#16 DAO/ADO基本コード対比表

テーブルやクエリのレコードを操作する基本的なプログラムを、DAOの場合とADOの場合で対比したものです。実際には、ケースに応じてさまざまな引数等のバリエーションがありますので、適宜応用して書き換えてください。

操作内容 DAOの場合 ADOの場合
読み込み
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("住所テーブル")
With rst
  Do Until .EOF
    Debug.Print !ID, !氏名, !住所, !郵便番号
    .MoveNext
  Loop
  .Close
End With
Dim cnn As Connection
Dim rst As Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
  .Open "住所テーブル", cnn
  Do Until .EOF
    Debug.Print !ID, !氏名, !住所, !郵便番号
    .MoveNext
  Loop
  .Close
End With
追加
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("住所テーブル")
With rst
  .AddNew
    !氏名 = "東京 太郎"
    !住所 = "東京都千代田区XXXXXXX"
    !郵便番号 = "XXX-XXXX"
  .Update
  .Close
End With
Dim cnn As Connection
Dim rst As Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
  .Open "住所テーブル", cnn, , adLockOptimistic
  .AddNew
    !氏名 = "東京 太郎"
    !住所 = "東京都千代田区XXXXXXX"
    !郵便番号 = "XXX-XXXX"
  .Update
  .Close
End With
更新
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("住所テーブル")
With rst
  Do Until .EOF
    .Edit
      !更新日時 = Now()
    .Update
    .MoveNext
  Loop
  .Close
End With
Dim cnn As Connection
Dim rst As Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
  .Open "住所テーブル", cnn, , adLockOptimistic
  Do Until .EOF
    !更新日時 = Now()
    'Move系メソッドを実行しない場合はUpdate要
    .MoveNext
  Loop
  .Close
End With
削除
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("住所テーブル")
With rst
  Do Until .EOF
    .Delete
    .MoveNext
  Loop
  .Close
End With
Dim cnn As Connection
Dim rst As Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
  .Open "住所テーブル", cnn, , adLockOptimistic
  Do Until .EOF
    .Delete
    .MoveNext
  Loop
  .Close
End With
検索
Dim dbs As Database
Dim rst As Recordset
Dim strCriteria As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("住所テーブル", _
                             dbOpenDynaset)

With rst
  strCriteria = "住所 Like '東京*'"
  .FindFirst strCriteria
  Do Until .NoMatch
    Debug.Print !ID, !氏名, !住所, !郵便番号
    .FindNext strCriteria
  Loop
  .Close
End With
Dim cnn As Connection
Dim rst As Recordset
Dim strCriteria As String
Dim varBookmark As Variant

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
  .Open "住所テーブル", cnn, adOpenKeyset
  strCriteria = "住所 Like '東京*'"
  .Find strCriteria
  Do Until .EOF
    Debug.Print !ID, !氏名, !住所, !郵便番号
    varBookmark = .Bookmark
    .Find strCriteria, 1, , varBookmark
  Loop
  .Close
End With

| Index | Prev | Next |

 

Copyright © T'sWare All rights reserved