【Access VBA】ADODBで別データベースに接続する方法
「Accessで別データベースの情報を使いたいけど、リンクテーブルだと動作が重くなる…」
こんな悩みはありませんか？
VBAのADODBライブラリを使えば、他のAccessデータベースのデータも簡単に取得・操作できます。
本記事では初心者でもわかるようにADODBで別データベースに接続する手順を解説します。
- ADODBで別のAccessデータベースに接続する方法
- Connection・Recordsetの基本
- VBAからSQLでデータ操作する方法
- エラー対策とトランザクションの基礎
VBAで使うADODBとは？
ADODBとは「ActiveX Data Objects Database」のことです。
VBAからデータベースに接続してレコードの取得・追加・削除を行うためのライブラリです。
ADODBを使うとVBAから直接SQLを実行したり別データベースの情報を操作できるため、
業務でのデータ連携や集計処理に便利です。
ADODBはVBAからさまざまなデータベースに接続して操作できるライブラリ
ADODBを使う前に必要な設定
AccessでADODBを使う場合は、事前に参照設定を行う必要があります。
Accessのメニューから 「Visual Basic」をクリックしてVBAエディタを開きます。
キーボードショートカットAlt + F11でも開けます。
VBAエディタのメニューから 「ツール」→「参照設定」 をクリックします。
「参照可能なライブラリファイル」の一覧から
「Microsoft ActiveX Data Objects 6.1 Library」にチェックを入れます。
チェックを入れたら 「OK」をクリックします。
この設定を行うとVBAでConnectionやRecordsetなどのADODBオブジェクトが使えるようになります。
ADODBの基本構文
まずはVBAでADODBを使って別データベースに接続する基本的な構文を確認しましょう。
Connectionの書き方
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\パス\Sample.accdb;"C:\パス\Sample.accdbには接続したいAccessデータベースのファイルパスを指定します。
データベースがパスワード付きの場合
Accessのデータベースにパスワードが設定されている場合は、
cn.Openの接続文字列にJet OLEDB:Database Password=パスワードを追加します。
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\パス\Sample.accdb;" & _
"Jet OLEDB:Database Password=yourpassword;"yourpasswordの部分に実際のパスワードを入力します。- パスワード付きDBも通常の接続方法とほぼ同じです。
ADODBを使ったVBAの実装例
SQLを直接実行する
ADODBのExecuteメソッドを使うとVBAからSQLを直接実行できます。
以下は、AccessのテーブルT顧客の全レコードを削除する例です。
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\パス\Sample.accdb;"
Dim sql As String
sql = "DELETE FROM T顧客"
cn.Execute sql
cn.Close: Set cn = NothingRecordsetを用いたデータ取得
ADODBのRecordsetを使うとSQLの結果を取得してVBAで操作できます。
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\パス\Sample.accdb;"
' SELECT文でデータ取得
rs.Open "SELECT * FROM T顧客", cn, adOpenStatic, adLockReadOnly
Do Until rs.EOF
Debug.Print rs!顧客名
rs.MoveNext
Loop
rs.Close: Set rs = Nothing
cn.Close: Set cn = NothingadOpenStaticとadLockReadOnlyは読み取り専用設定の例です。
ADODBを使う際の注意点
ADODBは使用後必ず閉じる
ADODBでデータベースに接続したら使用後は必ずRecordsetとConnectionを閉じて解放します。
閉じ忘れるとデータベースが開かれたままになり後続の処理に影響することがあります。
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing必要に応じてトランザクション処理を活用
DELETEやINSERTなどの操作を行う場合、途中でエラーが発生してもデータを元に戻せるようにトランザクションを使うと安全です。
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\パス\Sample.accdb;"
On Error GoTo ErrHandler
' トランザクション開始
cn.BeginTrans
cn.Execute "DELETE FROM T顧客 WHERE 顧客ID=100"
cn.Execute "INSERT INTO T顧客 (顧客ID, 顧客名) VALUES (101, '山田')"
' 問題なければコミット
cn.CommitTrans
ExitProc:
cn.Close: Set cn = Nothing
Exit Sub
ErrHandler:
' エラー発生時はロールバック
cn.RollbackTrans
MsgBox Err.Description
Resume ExitProc- BeginTrans → CommitTrans → RollbackTrans の流れが基本
- 複数操作をまとめて実行する場合に特に有効
👇トランザクション処理についての詳細はこちら
まとめ
今回はADODBで別データベースに接続する手順を解説しました。
ADODBを使えば、データベースを切り離して管理でき、複数人での利用も容易です。
VBAから直接SQLを実行できるため、データ操作も効率的に行えます。
コメント