AcceessのADOでの繋ぎ方

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

環境:Access2000

参照方法

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

更新

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

追加

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はシチュエーションで考えると吉（ヘルプなり確認したまへ）。

構文

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

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