VBAで参照設定をしないでADOを使ってAccessDBへ接続する

VBAでADOを使ってデータベースへ接続するとき、参照設定で「Microsoft ActiveX Data Objects *.* Library」にチェックをつける方法と、つけなくても動く方法の両方の使い方をまとめました。

参照設定のメリット・デメリット

ExcelからAccessDBを操作したり、AccessVBAで直接SQLを書いて操作したいというときにお世話になるADOですが、これを使うにはVBEにて

「ツール」→「参照設定」の、

「Microsoft ActiveX Data Objects *.* Library」にチェックを入れてから、というのが一般的です。

こちらのほうが定義が明確で不具合検出しやすい・入力支援機能が使えるなど開発時の効率が良いので、問題がなければこちらを採用したいところなのですが、違うPCから実行したとき、バージョンの違いなどでエラーが起きる場合があります。

利用するPCが少数で特定できる場合は、それぞれのPCで参照設定を再設定すれば良いのですが、ファイルを配布したりネットワークを介したりで、環境の違う多数のPCから利用する可能性がある場合、いちいち「エラー出た！」と呼ばれて(´Ａ｀)となる…。

そんな場合は、参照設定にチェックを入れないでADOを利用することが可能です。

参照設定でオブジェクトを指定せず実行時に生成するという方法で、若干遅くなると言われていますが、ここ数年業務で使用してみたところ中小規模程度のデータベースなら特に問題なく使えています。

参照設定にチェックを入れる場合

まずは「Microsoft ActiveX Data Objects *.* Library」にチェックした場合のコード。

Sub Sample()
  Dim adoCn As ADODB.Connection 'ADOコネクションオブジェクト
  Dim adoRs As ADODB.Recordset 'ADOレコードセットオブジェクト
  Dim strSQL As String 'SQL文
  
  'AccessVBAで現在のデータベースへ接続する場合
  'Set adoCn = CurrentProject.Connection
  
  '外部のAccessファイルを指定して接続する場合
  Set adoCn = New ADODB.Connection 'ADOコネクションのインスタンス作成
  adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=C:\SampleData.accdb;" 'Accessファイルを指定
             
  strSQL = "任意のSQL文"
  
  '追加・更新・削除の場合----------------------------------
  'adoCn.Execute strSQL 'SQLを実行
  '--------------------------追加・更新・削除の場合ここまで
  
  '読込の場合----------------------------------------------
  Set adoRs = New ADODB.Recordset 'ADOレコードセットのインスタンス作成
  adoRs.Open strSQL, adoCn 'レコード抽出
  Do Until adoRs.EOF '抽出したレコードが終了するまで処理を繰り返す
    Debug.Print adoRs!フィールド名 'フィールドを取り出す
    adoRs.MoveNext '次のレコードに移動する
  Loop
  adoRs.Close: Set adoRs = Nothing 'レコードセットの破棄
  '--------------------------------------読込の場合ここまで
  
  adoCn.Close: Set adoCn = Nothing 'コネクションの破棄
End Sub

2,3行目でコネクションとレコードセットの型を明確に宣言して、それぞれ必要なところでインスタンスを作成しています。このコードで参照設定に不備があると、宣言の部分でエラーになります。

外部のaccdbファイルを指定したい場合は上記のままで、AccessVBAで現在開いているDBを使う場合は10～12行をコメントアウトして、7行目のコメントアウトを外してください。

また、SQLで読込以外の処理（INSERTやUPDATEなど）をしたい場合は21～27行をコメントアウトして、17行目のコメントアウトを外してください。

トランザクションはこちらを参考にどうぞ。

• ExcelVBAとAccessの連携 第6回 トランザクション処理

参照設定にチェックを入れない場合

こちらは、参照設定で「Microsoft ActiveX Data Objects *.* Library」にチェックを入れなくても動くコード。

Sub Sample()
  Dim adoCn As Object 'ADOコネクションオブジェクト
  Dim adoRs As Object 'ADOレコードセットオブジェクト
  Dim strSQL As String 'SQL文
  
  'AccessVBAで現在のデータベースへ接続する場合
  'Set adoCn = CurrentProject.Connection
  
  '外部のAccessファイルを指定して接続する場合
  Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションのオブジェクトを作成
  adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=C:\SampleData.accdb;" 'Accessファイルを指定
             
  strSQL = "任意のSQL文"
  
  '追加・更新・削除の場合----------------------------------
  'adoCn.Execute strSQL 'SQLを実行
  '--------------------------追加・更新・削除の場合ここまで
  
  '読込の場合----------------------------------------------
  Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットのオブジェクトを作成
  adoRs.Open strSQL, adoCn 'レコード抽出
  Do Until adoRs.EOF '抽出したレコードが終了するまで処理を繰り返す
    Debug.Print adoRs!フィールド名 'フィールドを取り出す
    adoRs.MoveNext '次のレコードに移動する
  Loop
  adoRs.Close: Set adoRs = Nothing 'レコードセットの破棄
  '--------------------------------------読込の場合ここまで
  
  adoCn.Close: Set adoCn = Nothing 'コネクションの破棄
End Sub

2,3行目で明確な型宣言は行わず、必要なところでそれぞれオブジェクトを作成するため、参照設定を省略できる形です。ハイライト以外は上記のコードと一緒なので、解説も上をご参照ください。

多様な環境のPCから使う場面も結構あるので、こちらで組んだらとても楽でした。

Office2003, 2013, 2016, WinXP, 7, 10で使用経験がありますが、わたしの観測範囲では特に問題に遭遇したことはありません。（mdbファイルに接続する場合はProviderが Microsoft.Jet.OLEDB.4.0 になります。）

カーソルタイプとロックタイプの指定

上記サンプルコード22行目にあたる、レコードセットを作成してレコードを抽出する際、カーソルタイプとロックタイプというものが省略されています。

adoRs.Open strSQL, adoCn[, CursorType][, LockType]

• Access VBA 入門講座 Open – ADOによるレコードセットの作成

こちらのサイトで詳しく解説されていますが、定数0～3まで存在し、どちらも省略すると 0 が適用されます。目的によって定数を指定してください。

例えばCursorTypeを「adOpenKeyset」に指定してレコードセットを作成したい場合、

'↓参照設定していないと、この記述は使えない
adoRs.Open strSQL, adoCn, adOpenKeyset
'↓どちらでも使える
adoRs.Open strSQL, adoCn, 1 

「adOpenKeyset」が「1」であるということは、ライブラリが提供している情報なので、参照設定をしていないと「adOpenKeyset」という定数は使えません。その場合は直接数値を指定すると動きます。

'↓宣言セクションに定数を作っておく
Private Const adOpenKeyset = 1
'↓使うときはそれを指定
adoRs.Open strSQL, adoCn, adOpenKeyset

でも数値を書いちゃうと後で見たときに「なんの数値だこれ…」ってなっちゃうので、定数を作っておいたほうが分かりやすいですね。

n = adoRs.RecordCount

「adOpenKeyset」でレコードセットを作成すると、このように書いてレコードの数が取得できたりします。レコード数で配列を定義したいときなどに使えます。

参考

• Microsoft Visual Basic での ADO の使用
• VBA応用(ADOでデータを取得する。)
• データベース（Access 等）に接続（ADO） | ExcelWork.info

ありがとうございました！