VBA ADOによるデータベース接続について詳細解説！　[VBA関連解説#06]

エクセルVBAのADO機能を利用した、アクセスDBとの連携について解説していきます。

この記事では、データベースの接続/切断について解説します。

VBA関連解説

VBA関連解説

ADOについて

ADOは、ActiveX Data Objectsの略で、Microsoftが提供するデータベースにアクセスするためのツールです。
SQL文を使用し、レコードの追加・削除・検索・抽出・並べ替え等々の基本的な操作をができます。

環境設定

アクセス設定

テスト用にアクセスDBを用意し、テーブルをひとつ作成していきます。

アクセスを開き、空のデータベースを選択して、デスクトップにTEST.accdbを作成します。

　　データベース用：TEST.accdb

テスト用のテーブルを作成していきます。
[作成]-[テーブルデザイン]を選択します。

テーブルがデザインビューで表示されたら、各フィールドのフィールド名とデータ型を設定します。

主キーを設定します。
今回は受注IDを主キーとするので、受注ID行を選択し、[テーブルデザイン]-[主キー]をクリックします。

行セレクタに鍵マークが表示され、主キーの設定は完了です。

構造設定が終わったら、[テーブルデザイン]-[表示]-[データシートビュー]を選択して、データシートビューを開きます。
テーブルの表示方法は、構造設定をするデザインビューと、データの表示や操作をするデータシートビューがあります。

[名前を付けて保存]ダイアログが表示されるので、テーブル名をT_受注として保存して下さい。

テスト用にレコードを登録します。

アクセスDBの準備ができました。

エクセル設定

ADOを使用するためには、VBEの環境設定が必要となります。

まずは、テスト用のエクセルブックを用意します。

　　プログラム用：TEST.xlsm

テストブックのVBEを開き設定していきます。

VBEの[ツール]–[参照設定]をクリックします。
[Microsoft ActiveX Data Objects x.x Library]にチェックを入れ、[OK]を押します。
現在のバージョンは 2.8 ですが、最新のものがあればそちらを選択してください。

エクセルブックの準備ができました。

データベース接続/切断

接続

エクセルのテストブックに標準モジュール：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コネクションオブジェクトをPublicで宣言します。

@2.変数の格納
AccessDBのファイル名とデータ位置を変数に取り込みます。

@3.データベース接続
adoCnオブジェクトを作成します。

adoCnオブジェクトのOpenメソッドを使用し、データベースと接続します。
パラメータのProviderはAccessの固有値なので、Microsoft.ACE.OLEDB.12.0とします。Date Sourceは変数を使用し、DBのフルパスを設定します。

クライアント側でカーソルを移動できるように設定するため、CursorLocation = 3 に設定します。
＊詳細は省きますが、CursorLocation = 3(adUseClient)を設定すると、レコードセットのRecordCountを取得できるので、設定しておきましょう。

切断

データベース切断のサブプロシージャを標準モジュール：TESTに作成します。

Sub 切断()

'''''''''' DB切断 ''''''''''

adoCn.Close
Set adoCn = Nothing

''''''''''''''''''''''''''''''''''''''''
End Sub

@1.データベース切断
adoCnオブジェクトのCloseメソッドを使用し、データベース接続を切断します。
adoCnオブジェクトを破棄して終了です。

データベース接続し、なんらかの処理プロセスが終わった後には、データベース切断のコードを入れましょう。

VBA実行

実行用のプロシージャを作成します。

Sub 実行()

Call 接続

Call 切断

End Sub

データベース接続/切断のVBAを実行してみましょう。
接続して切断するだけなので、何も起こりませんが、この実行プロシージャの接続/切断の間に処理プロセスを作成していきます。
　

次の記事では、データベースの操作について解説していきます。
www.minizaiko.com

小さな在庫管理

在庫管理導入からシステム作成まで詳細解説！