教えて!ExcelVBA!

ExcelVBAの基礎知識・書き方について紹介します。

【ExcelVBA 外部アプリケーション連携】(Access連携)Accessテーブル(レコード)に接続する及び切断するにはどうすればいいの?教えて!

f:id:m_kbou:20200512090631p:plain

ExcelVBAよりAccessデータベースに含まれるテーブルデータを操作するには、Accessデータベース接続後にテーブルに接続する必要があります。また、接続にてデータ操作が完了した後は、Accessデータベースとテーブルの切断作業も必要となります。今回は接続と切断方法について説明します。

 

 

構文

記述方法は以下の通りとなります。

(1)テーブルへの接続方法

①データベースへの接続

Set[参照データベース]= OpenDatabase(”[データベースの登録パス]”)

Set[参照テーブル]=[参照データベース].OpenRecordset(”[SQL文]”)

[説明]:

Accessテーブルとの接続には「Set[参照テーブル]=[参照データベース].OpenRecordset(”[SQL文]”)」と記述します。[SQL文]には対象テーブルと抽出方法を記述します。[SQL文]は「”」(ダブルクォーテーション)で囲みます。また、[参照データベース]には上記①で予め接続してあるAccessデータベースを指定します。Accessデータベースの接続方法についてはこちらを参照して下さい。内容を纏めると、「[SQL文]で記述されている指定方法でテーブルよりデータを抽出し、結果を[参照テーブル]にセットして下さい。」との意味になります。

[記述例]:

Set r = db.OpenRecordset("select * from T_従業員")

(2)テーブルからの切断方法

[参照テーブル].Close
Set[参照テーブル]= Nothing

②データベースの切断

[参照データベース].Close
Set[参照データベース]= Nothing

[説明]:

テーブルへ接続をした場合には必ず切断処理が必要となります。よって、接続と切断は一連の処理と理解して下さい。接続されたAccessテーブルを切断するには「[参照テーブル].Close」及び「Set[参照テーブル]= Nothing」と記述します。[参照テーブル]には上記の(1)接続方法で説明した変数名をそのまま使用します。内容を纏めると、「[参照テーブル]にセットされている参照テーブルを切断して下さい。」との意味になります。また、テーブルの切断後には上記②のデータベース切断の処理(記述)が必要となります。データベース切断についてはこちらを参照して下さい。

[記述例]:

r.Close
Set r = Nothing

 

使い方

使い方について具体的に説明していきます。

[プログラミング例]:

Sub サンプル()
    '↓処理①
    Dim db As Database
    Dim r As Recordset
    '↓処理②
    Set db = OpenDatabase("C:\テスト\従業員.accdb")
    '↓処理③
    Set r = db.OpenRecordset("select * from T_従業員")
    '↓処理④
    MsgBox "テーブルに接続しました。"
    '↓処理⑤
    r.Close
    Set r = Nothing
    '↓処理⑥
    db.Close
    Set db = Nothing
End Sub

処理の流れは以下の通りとなります。

[処理①]:変数の定義
Dimによる変数定義となります。

[処理②]:データベースへの接続
データベースをOPENし[処理①]で定義した変数:dbにデータベースの参照を代入します。今回はC:\テストに従業員.accdbデータベースが登録されている事とします。(※データベースへの接続についてはこちらを参照して下さい。)

[処理③]:テーブルへの接続
テーブルをOPENし[処理①]で定義した変数:rに抽出した参照データを代入します。今回は「T_従業員」テーブルを使用しますが、このテーブルは従業員.accdbの中に含まれています。また、指定したSQL文は「T_従業員テーブルの全項目を使用します。」との意味になります。

[処理④]:メッセージ
[処理③]のテーブル接続が正常に終了した場合、”テーブルに接続しました。”をMsgBoxで値を表示します。

[処理⑤]:テーブルの切断
テーブルの切断を行います。

[処理⑥]:データベースの切断
最後にデータベースの切断を行います。(※データベースの切断についてはこちらを参照して下さい。)

f:id:m_kbou:20210219105141p:plain

※上記のプログラミング例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方についてはこちらを参考にして下さい。また、Accessデータベースとの接続には事前設定が必要となります。事前準備はこちらを参考にして下さい。

[実行例]:

(実行前)

C:\テストにAccessデータベース(従業員.accdb)が登録されています。

f:id:m_kbou:20200512090657p:plain

従業員.accdbデータベースの中には、T_従業員テーブルが登録されています。

f:id:m_kbou:20200512090713p:plain

(実行後)

テーブルに接続できたため、”テーブルに接続しました。”のメッセージが表示されます。

f:id:m_kbou:20200512090727p:plain

従業員.accdbにT_従業員テーブルが登録されていない場合は、以下の様な警告メッセージが表示され、接続ができなかった事を確認できます。

f:id:m_kbou:20200512090742p:plain

[サンプル]:

上記で説明したファイルをダウンロードできます。ご自由にお使い下さい。

drive.google.com

Accessテーブルに接続する及び切断する方法についての説明は以上です。

 

おわりに

今回はAccessテーブルに接続及び切断する方法について説明しました。上記でも説明しましたが、Accessテーブルへ接続する場合にはデータベース接続後にテーブル接続が必要となりますので注意して下さい。また、Excel⇔Accessの連携には事前の準備作業が必要となります。こちらは予め実施しておくようにお願いします。