AcceessのADOでの繋ぎ方

再びAccessでのお仕事が入ってきそうなのでリハビリ。まずは基本ネタ。

環境:Access2000

参照方法

view plainprint?

1. Dim cn As ADODB.Connection  
2. Dim Rst As ADODB.Recordset  
3. Dim i  As Integer  
4. Set cn = CurrentProject.Connection  
5. Set Rst = New ADODB.Recordset  
6. Rst.Open "SELECT * FROM TBS_TEST WHERE SEQNo < 3 ", cn, adOpenForwardOnly, adLockReadOnly  
7. Do Until Rst.EOF  
8.     MsgBox (Rst("SEQNo"))  
9.     Rst.MoveNext  
10. Loop  
11. Rst.Close: Set Rst = Nothing  
12. cn.Close: Set cn = Nothing  

Dim cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim i  As Integer
Set cn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
Rst.Open "SELECT * FROM TBS_TEST WHERE SEQNo < 3 ", cn, adOpenForwardOnly, adLockReadOnly
Do Until Rst.EOF
    MsgBox (Rst("SEQNo"))
    Rst.MoveNext
Loop
Rst.Close: Set Rst = Nothing
cn.Close: Set cn = Nothing

更新

view plainprint?

1. Rst.Open "SELECT * FROM TBS_TEST WHERE SEQNo <= 2", cn, adOpenDynamic, adLockPessimistic  
2. Do Until Rst.EOF  
3.     Rst("Data").Value = Rst("SEQNo").Value + 1  
4.     Rst.Update  
5.     Rst.MoveNext  
6. Loop  
7. Rst.Close  

Rst.Open "SELECT * FROM TBS_TEST WHERE SEQNo <= 2", cn, adOpenDynamic, adLockPessimistic
Do Until Rst.EOF
    Rst("Data").Value = Rst("SEQNo").Value + 1
    Rst.Update
    Rst.MoveNext
Loop
Rst.Close

追加

view plainprint?

1. Rst.Open "SELECT * FROM TBS_TEST WHERE SEQNo <= 2", cn, adOpenDynamic, adLockPessimistic  
2. Rst.AddNew  
3. Rst("SEQNo") = 10  
4. Rst("Data").Value = "新規"  
5. Rst.Update  
6. Rst.Close  

Rst.Open "SELECT * FROM TBS_TEST WHERE SEQNo <= 2", cn, adOpenDynamic, adLockPessimistic
Rst.AddNew
Rst("SEQNo") = 10
Rst("Data").Value = "新規"
Rst.Update
Rst.Close

CursorTypeやLockTypeはシチュエーションで考えると吉（ヘルプなり確認したまへ）。

構文

view plainprint?

1. Recordset.Open Source,ActiveConnection,CursorType,LockType,Option  

Recordset.Open Source,ActiveConnection,CursorType,LockType,Option

memo→http://www.accessclub.jp/ado/08.html