データベース管理ソフトウェア、Accessを実務で使うときに「知っているとちょっと便利なコツ」について紹介するこのコラム。今回は、AccessVBAを使う方なら一度は考えるDAOとADOの違いと使い分けについて、記事公開時点（2021年8月）の最新の情報を踏まえて解説します。あなたのAccess力アップに役立ちますように。
前回のコラムを見逃した方はこちらからご覧ください。
第3話 マクロとVBAの違い
DAOとADOってなんのこと？
AccessVBAを使ってデータベースを操作しようとすると、DAOやADOという単語を耳にしませんか？ これは一体、何なのでしょうか？
ざっくり言うと、どちらもデータベースへ接続・操作する手段のことです。Accessでは、どちらも利用することができます。
今回は、この2つについて学んでいきましょう。
どう違うの？
この2つは略称で、正式名称は以下のとおりです。
・DAO … Data Access Objects
・ADO … ActiveX Data Object
DAOはAccessで使用されるデータベースに特化した手段です。それに対してADOは、Accessをはじめ各社の主要なデータベースでも幅広く使える接続手段です。
DAOに関しては、Access開発当初からずっと使われてきた技術だったものの、Access2000よりADOが使えるようになったことで、一時期はADOが推奨される風潮があったようです。
しかし、Access2007よりDAOが新しくなったことから、現在では「古い手段」というイメージは払拭されています。
どちらを使うべき？
この記事が公開される2021年8月現在では、Accessにおいて、DAO/ADOどちらを使っても大きな差はありません。
DAOは元々Accessに特化した技術なのでADOよりも高速と言われていますが、パソコンのスペックもどんどん上がっているので、体感できるほどの差にはなりにくいと思われます。
Accessのみで完結するシステムならばDAO、Access以外でデータベースを使う・または今後その可能性がある場合はADO、という判断をしてもよいのではないでしょうか。
テンプレート化して使うのがおすすめ
データベースを操作するには、SQLというデータベースに問い合わせを行うための言語を使います。
VBAからデータベースを操作する際、SQLは目的によって内容を変えなくてはなりませんが、そのSQLを「処理する部分」は一定です。DAO/ADOそれぞれ、処理部分のみ切り分けたテンプレートを用意しておけば、SQLのみの変更でかんたんに利用することができます。
ただし、SQLでの問い合わせには大きく分けて「データベースから任意のレコードを抽出する」働きと「データベースの内容を変更する」働きがあるので、VBAで「処理する部分」はDAO/ADOそれぞれに、2種類ずつ必要になります。
特に「任意のレコードを抽出する」場合、レコードセットというオブジェクトが登場します。これは、データベースのレコードが集まった表形式のデータ構造で、Accessでテーブルやクエリをデータシートビューで開いた時と同じイメージのものをVBA上で保持、扱うことができます。
後述のコードで、任意のレコードの集合を「レコードセット」という形で取得し、それを展開する記述がありますので、注目してみてください。
それではここからはDAO/ADOのテンプレートとして使える、SQLを「処理する部分」のAccessVBAをご紹介します。任意のテーブルが存在しているaccdbファイルを開いた状態からAlt+F11キーでVBE（Visual Basic Editor）を開きます。
「挿入」→「標準モジュール」を作成して、そこへコードをコピー&ペーストしてお使いください。
DAOのテンプレート
それではまず、DAOを利用する方法から紹介します。
DAOは2021年8月現在ではAccessの規定のデータベース接続方法と考えられるため、標準で「ツール」→「参照設定」の「Microsoft Office XX.X Access database engine Object Library」にチェックが付いています。この設定でDAO.DatabaseやDAO.Recordsetなどのオブジェクトが利用できます。
以下はレコードの抽出で使用するテンプレートです。引数のSQLによって抽出されたレコードを2次元配列へ格納して返します。
On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示
Dim db As DAO.Database 'DAOデータベース用オブジェクトの宣言
Set db = CurrentDb '現在開いているデータベースへ接続
'レコードセットのオープン
Dim rs As DAO.Recordset 'DAOレコードセットオブジェクト変数宣言
Set rs = db.OpenRecordset(sql) 'カーソルタイプを指定してレコードセットを開く
'空だったら最終処理へ
If rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合
getRsInArray_DAO = Array() '空の配列を返す
GoTo Finally '最終処理へジャンプ
End If
'レコード数を取得
Dim recordCount As Long
rs.MoveLast
recordCount = rs.recordCount
rs.MoveFirst
'配列に展開
Dim ary() As Variant
ReDim ary(recordCount - 1, rs.Fields.Count - 1) '配列の要素数を定義
Dim rsNum As Long '縦要素（レコードセット）の繰り返し用変数
Dim fldNum As Long '横要素（フィールド）の繰り返し用変数
Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
ary(rsNum, fldNum) = rs(fldNum) '配列に格納
Next fldNum
rsNum = rsNum + 1 '縦要素をカウントアップする
rs.MoveNext '次のレコードに移動する
getRsInArray_DAO = ary '配列を返す
GoTo Finally '正常に終了したら最終処理へジャンプ
Err_Handler: '例外処理
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージが入る
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
getRsInArray_DAO = Array() '空の配列を返す
Finally: '最終処理
rs.Close 'レコードを閉じる
Set rs = Nothing
End If
If Not db Is Nothing Then '接続解除
db.Close
Set db = Nothing
End If
以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行し、結果をTrue/Falseで返します。トランザクションが実装されているので、途中でエラーが発生した場合は処理されません。
On Error GoTo ErrorHandler 'エラーが起きたら「ErrorHandler」にジャンプする指示
Dim ws As DAO.Workspace 'トランザクション用オブジェクト
Set ws = DBEngine(0)
Dim db As DAO.Database 'DAOデータベース用オブジェクトの宣言
Set db = CurrentDb '現在開いているデータベースへ接続
ws.BeginTrans 'トランザクション開始
'実行
Dim sql As Variant
For Each sql In sqlList 'SQL文リストをループ
db.Execute sql '1行ずつ実行
Next sql
ws.CommitTrans '確定
tryExecute_DAO = True '成功だった場合、関数の結果にTrueを入れる
GoTo Finally '正常に終了したら最終処理へジャンプ
ErrorHandler: '例外処理（エラーが起きたらここへジャンプ）
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージ格納
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
Finally: '最終処理
db.Close
Set db = Nothing
End If
If Not ws Is Nothing Then 'トランザクション用のオブジェクトを破棄
ws.Close
Set ws = Nothing
End If
ADOのテンプレート
続いてADOを利用する方法です。
DAOの場合、「ツール」→「参照設定」の「Microsoft Office XX.X Access database engine Object Library」を利用しましたが、ADOで同様にするには、「Microsoft ActiveX Data Objects X.X Library」を利用します。パソコン環境によってバージョンが複数存在する可能性がありますが、数字の大きなものがよいでしょう。
ただし、このライブラリはデフォルトでチェックがオフになっているので、VBAを組み込んだAccessファイルを別のパソコンで使うときに、参照設定でチェックを入れ直さないとエラーになってしまいます。
ライブラリを使ったほうが開発時は便利なのですが、運用時にうっかりエラーを引き起こす可能性もあるので、今回は参照設定の「Microsoft ActiveX Data Objects X.X Library」にチェックを入れずにADOを利用するコードをご紹介します。
以下はレコードの抽出で使用するテンプレートです。引数のSQLによって抽出されたレコードを2次元配列へ格納して返します。
Const adStateOpen = 1 'レコードセットが開いている場合の設定値
Const adOpenKeyset = 1 'レコードセットカーソルタイプの設定値
On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示
Dim cn As Object 'ADOコネクション用オブジェクトの宣言
Set cn = CurrentProject.Connection '現在開いているデータベースへ接続
'レコードセットのオープン
Dim rs As Object 'レコードセット用変数宣言
Set rs = CreateObject("ADODB.RecordSet") 'ADOレコードセットオブジェクトを作成
rs.Open sql, cn, adOpenKeyset 'カーソルタイプを指定してレコードセットを開く
'空だったら最終処理へ
If rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合
getRsInArray_ADO = Array() '空の配列を返す
GoTo Finally '最終処理へジャンプ
End If
'配列に展開
Dim ary() As Variant
ReDim ary(rs.recordCount - 1, rs.Fields.Count - 1) '配列の要素数を定義
Dim rsNum As Long '縦要素（レコードセット）の繰り返し用変数
Dim fldNum As Long '横要素（フィールド）の繰り返し用変数
Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
ary(rsNum, fldNum) = rs(fldNum) '配列に格納
Next fldNum
rsNum = rsNum + 1 '縦要素をカウントアップする
rs.MoveNext '次のレコードに移動する
getRsInArray_ADO = ary '配列を返す
GoTo Finally '正常に終了したら最終処理へジャンプ
Err_Handler: '例外処理
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージが入る
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
getRsInArray_ADO = Array() '空の配列を返す
Finally: '最終処理
If rs.State = adStateOpen Then rs.Close 'レコードセットが開いていたら閉じる
Set rs = Nothing
End If
If Not cn Is Nothing Then
cn.Close '接続解除
Set cn = Nothing
End If
以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行します。トランザクションが実装されているので、途中でエラーが発生した場合は処理されません。
On Error GoTo ErrorHandler 'エラーが起きたら「ErrorHandler」にジャンプする指示
Dim cn As Object 'ADOコネクション用オブジェクトの宣言
Set cn = CurrentProject.Connection '現在開いているデータベースへ接続
cn.BeginTrans 'トランザクション開始
'実行
Dim sql As Variant
For Each sql In sqlList 'SQL文リストをループ
cn.Execute sql '1行ずつ実行
Next sql
cn.CommitTrans '確定
tryExecute_ADO = True '成功だった場合、関数の結果にTrueを入れる
GoTo Finally '正常に終了したら最終処理へジャンプ
ErrorHandler: '例外処理（エラーが起きたらここへジャンプ）
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージ格納
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
Finally: '最終処理
cn.Close '接続解除
Set cn = Nothing
End If
テンプレートの利用（レコードの抽出）
SQLのSELECT構文を作成し、引数としてテンプレートへ渡して結果のレコードセットを二次配列の形で受け取るコードがこちらです。
SELECT構文の基本形は以下のようなものです。
SELECT フィールド1, フィールド2 FROM テーブル WHERE 条件;
'SQL作成
Dim sql As String
sql = "任意のSELECT構文"
'レコードセットを配列で取得
Dim ary() As Variant
ary = getRsInArray_DAO(sql) '←DAOを利用する場合
'ary = getRsInArray_ADO(sql) '←ADOを利用する場合
'空の配列だったら終了
If isEmptyArray(ary) Then Exit Sub
'中身の確認
Dim rsCount As Long
rsCount = UBound(ary, 1) '第1要素（レコード）数
Dim fldCount As Long
fldCount = UBound(ary, 2) '第2要素（フィールド）数
Dim x As Long, y As Long
For x = 0 To rsCount
Debug.Print ary(x, y), ; 'イミディエイトウィンドウに出力
Next y
Debug.Print "" 'イミディエイトウィンドウの改行
取得部分は、DAO/ADOどちらか一方をお使いください。
配列を受け取った後、中身を確認する前に配列が空かどうかの判定も必要なので、以下の関数も任意のモジュールに追記してください。
On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示
If UBound(tgtAry) >= 0 Then '配列に要素が存在したら
Exit Function '終了（Falseを返す）
End If
Err_Handler: 'エラーもしくは要素が存在しない場合
isEmptyArray = True 'Trueを返す
End Function
コードを実行すると、SELECT構文で抽出した配列の中身がVBEのイミディエイトウィンドウ（表示されていない場合は「表示」→「イミディエイトウィンドウ」）で確認できます。
取得した配列は、そのままコンボボックスやリストボックスのソースにセットすることができるほか、レコードの値を非連結で扱えるのでさまざまな実装に役立ちます。
テンプレートの利用（レコードの挿入・更新・削除）
SQLのINSERT/UPDATE/DELETE構文をコレクションに格納し、引数としてテンプレートへ渡して結果をTrue/Falseの形で受け取るコードがこちらです。
それぞれの構文の基本形は以下のようなものです。
・INSERT INTO テーブル(フィールド1, フィールド2) VALUES(値1, 値2);
・UPDATE テーブル SET フィールド1=値1, フィールド2=値2 WHERE 条件;
・DELETE FROM テーブル WHERE 条件;
複数のSQL文に対応するためコレクションに格納していますが、格納されるSQL文が1つでも動作します。数が多い場合はDoやForなどのループを使ってSQLを作成&コレクションへ格納していくとスマートです。
'コレクションの作成
Dim sqlList As Collection
Set sqlList = New Collection
'SQL作成
Dim sql As String 'SQL文用変数の宣言
sql = "任意のINSERT/UPDATE/DELETE構文" '1つ目のSQL文
sqlList.Add sql 'コレクションへ追加
sql = "任意のINSERT/UPDATE/DELETE構文" '2つ目のSQL文
sqlList.Add sql 'コレクションへ追加
'実行
If tryExecute_DAO(sqlList) Then '処理が成功した場合 '←DAOを利用する場合
'If tryExecute_ADO(sqlList) Then '処理が成功した場合 '←ADOを利用する場合
MsgBox "正常に終了しました", vbOKCancel + vbInformation, "終了" '終了メッセージ
End If
実行部分は、DAO/ADOどちらか一方をお使いください。
実行すると、問題が起きた場合は処理されずにエラー内容が、問題なく処理が終了した場合は終了メッセージが表示されます。
いかがだったでしょうか？次回はVBAでSQL文を扱う際のコツやポイントについてです。1行で済むような短い文ならば理解しやすいのですが、可読性のために改行したい、変数を使いたい、などの実用的なSQL組み込みのためのヒントをご紹介しますので、ぜひご期待ください。
第1話「イベント」を使いこなそう
第2話 レイアウトで躓きやすいポイント
第3話 マクロとVBAの違い
