「Excelだけではデータ量や安定性に限界を感じる」「データベースと連携して効率化したい」——そんな声を現場でよく耳にします。そこで注目したいのが、VBAとADO（ActiveX Data Objects）を活用したAccessとの連携です。
本記事では、Excel VBAからAccessデータベースへ接続・切断するための基本的なADOの使い方を、実務で役立つコード例とともにわかりやすく解説します。初心者の方でもすぐに試せるよう、初期設定から接続処理、切断処理までを丁寧に紹介します。
「Excelの限界を突破したい」「業務の効率化を図りたい」と考えている方は、ぜひ最後までご覧ください。
VBA技術解説
ADO（ActiveX Data Objects）とは？
ADOは、Microsoftが提供するデータベースアクセス用のインターフェースで、Excel VBAなどからAccessなどの外部データベースに接続し、データを操作するために使われます。SQLという命令文を使って、検索・追加・更新・削除・並べ替えなど、基本的な処理を簡単に実行できます。
Excelでは扱いづらい大量データや複雑な管理も、ADOを使えば効率的に処理できます。VBAと組み合わせることで、Excelの操作性を保ちつつ、柔軟で安定したデータ管理が実現できます。初心者でも基本を押さえれば、接続や切断はシンプルなコードで実装でき、業務効率化やデータの一元管理に役立ちます。
テスト環境の設定
VBAからAccessデータベースを操作するには、まずテスト用の環境を整える必要があります。ここでは、Access側のデータベース作成と、Excel側のADO設定手順を紹介します。
Access 設定
まず、テスト用のAccessデータベースを作成し、テーブルをひとつ用意します。
１．Accessを起動し、「空のデータベース」を選択
デスクトップに TEST.accdb を作成します。
データベース用：TEST.accdb
２．テーブル作成
[作成] → [テーブルデザイン]を選択します。
３．フィールドの設定
デザインビューで、各フィールドの「フィールド名」と「データ型」を入力します
４．主キーの設定
「受注ID」フィールドを選択し、[テーブルデザイン] → [主キー] をクリック。
行セレクタに鍵マークが表示されれば、主キーの設定は完了です。
５．データシートビューで確認
[テーブルデザイン] → [表示] → [データシートビュー] に切り替えて、テーブルの表示形式を確認します。
※テーブルの表示方法は、構造設定をするデザインビューと、データの表示や操作をするデータシートビューがあります。
６．テーブル名を保存
[名前を付けて保存] ダイアログが表示されるので、テーブル名を T_受注 に設定します。
７．テスト用レコードを登録
データシートビューで、テスト用にいくつかのレコードを入力しておきましょう。
これで、Access側の準備は完了です。
Excel 設定
次に、ExcelからADOを使ってAccessへ接続するための設定を行います。
１．テスト用のExcelブックの作成
ファイル名は TEST.xlsm（マクロ有効ブック）にします。
プログラム用：TEST.xlsm
２．VBE（Visual Basic Editor）を開く
テストブックのVBEを開き設定していきます。Excelで [Alt] + [F11] を押してVBEを起動します。
３．参照設定を行う
VBEのメニューから [ツール] → [参照設定] を選択し、Microsoft ActiveX Data Objects x.x Library にチェックを入れて [OK] をクリックします。
※現在のバージョンは「2.8」ですが、最新のバージョンがあればそちらを選択してください。
これでExcel側の準備も完了です！
データベース接続/切断
接続処理
Excelのテストブックに標準モジュール「TEST」を作成し、以下の接続用プロシージャを記述します。
Option Explicit Public adoCn As Object Sub 接続() Dim FileName As String FileName = "TEST.accdb" Dim データ位置 As String データ位置 = "C:\Users\*****\Desktop\" '''''''''' DB接続 '''''''''' Set adoCn = CreateObject("ADODB.Connection") adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & データ位置 & FileName & ";" adoCn.CursorLocation = 3 '''''''''''''''''''''''''''''''''''''''' End Sub
@1. 変数の宣言
モジュール先頭の宣言領域で、 ADOのConnectionオブジェクトをPublicで宣言します。これにより、他のプロシージャからも接続状態を参照できます。
@2.変数の格納
FileName と データ位置 に、Accessファイル名と保存場所のパスをそれぞれ格納します。この2つを組み合わせて、接続文字列の Data Source に指定します。
※FileName と データ位置 に分けて記述することで、ファイル名変更やパス変更に柔軟に対応できます。
@3.データベース接続
CreateObject("ADODB.Connection") でADO接続オブジェクト adoCnを生成します。このadoCnオブジェクトのOpen メソッドでAccessデータベースに接続します。
・Provider=Microsoft.ACE.OLEDB.12.0 は、Access用の接続プロバイダーです。
・Data Source には、Accessファイルのフルパスを指定します。
・CursorLocation = 3 は、クライアント側カーソル（adUseClient）を指定する設定です。
＊CursorLocation = 3 を設定することで、後続のレコードセット操作で RecordCount を取得できるようになりますので、設定しておきましょう。
切断処理
Excelのテストブックの標準モジュール「TEST」に、以下の切断用プロシージャを記述します。
Sub 切断() '''''''''' DB切断 '''''''''' adoCn.Close Set adoCn = Nothing '''''''''''''''''''''''''''''''''''''''' End Sub
@1.データベース切断
adoCnオブジェクトのCloseメソッドを使用し、データベース接続を切断します。
adoCnオブジェクトを破棄して終了です。
Accessデータベースとの接続が完了し、必要な処理が終わったら、必ず切断処理を行いましょう。リソースの解放やメモリリーク防止のためにも重要です。
さらに、安全に切断するには。切断前に If Not adoCn Is Nothing Then や If adoCn.State = 1 Then などで接続状態を確認しておくと、エラーを回避できます。
Sub 切断() '''''''''' DB切断 '''''''''' If Not adoCn Is Nothing Then If adoCn.State = 1 Then adoCn.Close Set adoCn = Nothing End If End If '''''''''''''''''''''''''''''''''''''''' End Sub
VBA実行
Accessデータベースとの接続・切断処理が整ったら、実行用のプロシージャを作成して、動作確認をしてみましょう。
Excelのテストブックの標準モジュール「TEST」に、以下のような実行用プロシージャを記述します。
Sub 実行() Call 接続 Call 切断 End Sub
@1.Call 接続
Accessデータベースに接続します。
@2.Call 切断
接続を切断してリソースを解放します。
現時点では接続してすぐに切断するだけなので、画面上では何も起こりません。
Accessとの接続がスムーズに行えることを確認できたら、次は「ADOによるデータベース操作」に進みましょう。
次の記事では、以下のような処理について詳しく解説しています。
・レコードの抽出（SELECT）
・データの登録（INSERT）
・データの更新（UPDATE）
・データの削除（DELETE）
Excel VBAからAccessデータベースを自在に操作するための基本を、実務コード付きで紹介しています。処理の流れや注意点も丁寧に解説していますので、初心者〜中級者の方にもおすすめです。
www.minizaiko.com
