#16
テーブルやクエリのレコードを操作する基本的なプログラムを、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
|
