ADOでSQLでレコードセット作って一行ずつ処理する方法

2020年11月4日2022年5月3日

SBMJOB

Access VBAでテーブルを操作・参照するときはADO接続でSQL発行した方がロジックがわかりやすいと思われます。あと外部のサーバに接続して開発する場合もADOの方がすんなり理解しやすいのでADOおすすめします。さてタイトルの件ですが以下のサンプルコードで発行したSQLの内容でレコードセットを取得しそのレコードセットを一行ずつ処理するという内容になっています。

コード

Private Function sample()
 Dim myCn     As ADODB.Connection    'ADOコネクションオブジェクト
 Dim myRs     As ADODB.Recordset     'ADOレコードセットオブジェクト
 Dim strSQL   As String              'SQL文用文字列

  'エラートラップ
 On Error GoTo Err_Exit

 '関数の戻り値初期化
 ADO接続 = False
  '現在のデータベースへ接続
 Set myCn = CurrentProject.Connection
  'ADOレコードセットのインスタンス作成
 Set myRs = New ADODB.Recordset
  'SQL文作成
 strSQL = "SELECT * FROM 売上テーブル"
  'レコードセット取得
 myRs.CursorLocation = adUseClient
 myRs.Open strSQL, myCn, , adOpenDynamic
 With myRs
  '対象データがなければ終了
  　If .EOF Or .BOF Then
   　MsgBox "該当データがありません。処理を終了します。", vbOKOnly + vbInformation
   　GoTo End_Proc
  　Else
  　'一度レコード数を表示する
   　If .RecordCount <> 0 Then
    　MsgBox (.RecordCount & "件のレコードがセットされています")
   　End If
  　'先頭レコードに移動
 　　.MoveFirst
 　　While Not .EOF
  　　'MsgBox (.Fields("商品名"))
  　　If .Fields("商品名") = "うどん" Then
   　　MsgBox ("きたぁー！ずるずる")
  　　End If
  　　If .Fields("商品名") = "うどん" Then
  　　　.Fields("商品名") = "稲庭うどん"
 　 　End If
 　　.MoveNext
 　　Wend
 　End If
 End With
 MsgBox "処理を終了しました。", vbOKOnly + vbInformation
 End_Proc:
  'オブジェクトの開放
 Set myRs = Nothing: Close
 Set myCn = Nothing: Close
  '正常終了
 ADO接続 = True
 Exit Function
 Err_Exit:
  'エラーNOとエラーの詳細を表示
 MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical, "ADO接続()"
  'オブジェクトの開放
 Set myRs = Nothing: Close
 Set myCn = Nothing: Close
End Function

解説とポイント

myRsというレコードセットにSQLでレコードをテーブルから持ってきています。そしてそのmyRsを一行ずつ中身を見ていき商品名が「うどん」だったらメッセージを表示しています。この部分が一行ずつ処理している箇所となります。メッセージ表示ではなく他の計算処理や出力処理なのどに置き換えるとよいと思っています。

なお、このレコードセットなんですがロックタイプがadLockReadOnly以外の場合はレコードセットをUpdateしてやればテーブルを更新することが可能です。さらにレコードセットのフィールドを違う値で置き換えて次のレコードに移動したりといわゆるカーソルを移動させるとUpdateが自動でかかりテーブルが更新されます。

レコードセット宣言時のロックタイプデフォルト値はadLockReadOnlyなので上記のサンプルコードはレコードセットの中身は書き換える事ができますが（このレコードセットをソースとする帳票フォームの表示は変更することができます）、テーブル自体は更新することはできません。