VBAでAccessデータベースを活用しよう～接続編～

エクセルでデータ管理をしてるとデータが重くなったり、
複数の人が同時に編集できないという煩わしさがあったので、
データベースを活用してデータ管理をやりやすくしてみました。

VBAでデータベースを扱いたい人は是非見ていってください。

★準備編

VBAでデータベースを活用するためにいくつか準備することがあります。

①データベースファイルの準備

今回はデータベースとしてAccessを使います。

「.accdb」ファイル形式のデータを用意してください。

Accessを持ってない方はネットでサンプルファイルがいくつか用意されているので、デスクトップ等に保存してください。

②参照設定の準備

ファイルが用意できたらエクセルを開き、[Alt] + [F11]でVBAを開きましょう。

次に[ツール]→[参照設定]を選択し、[Microsoft ActiveX Data Objects 6.1 Library]と[Microsoft ADO Ext.6.0 for DDL and Security]をチェックし、[OK]をクリック。

 

ここまでで準備完了です。

★接続チェック

上記準備ができたらいよいよコーディングです。

エクセルとデータベースの接続を確認するため、以下のコードを入力してください。

4行目の[*******]の部分は保存した[accdb]ファイルの絶対パスを入力します。

絶対パスの取得方法は以下のサイトが参考になります。

 
amaotolog.com

コードが入力できたら[実行]をしましょう。

はじめにメッセージボックスで[1]が表示され、次に[0]が表示されれば成功です。

Public Sub 接続確認

Dim DBFile As String
Dim strDB As String
Dim CN As ADODB.Connection

DBFile = "******"
strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile

Set CN = New ADODB.Connection
CN.ConnectionString = strDB
CN.Open

MsgBox CN.State

CN.Close

MsgBox CN.State

Set CN = Nothing

End Sub

★コード解説

Dim DBFile As String

VBAがデータベースファイルにアクセスする場所をDBFileとして宣言しています。

Dim strDB As String

VBAとデータベースは元々別のプログラムなので、両者を仲介させることが必要です。

それがProviderと言われるもので、このProviderを設定するためにここでstrDBとして宣言しています。

Dim CN As ADODB.Connection

データベースへの接続にはConnectionオブジェクトを活用します。

CNとしてここで宣言しています。

CN.Open

データベースとの接続を実行します。

MsgBox CN.State

VBAとデータベースとの接続状態を示します。

接続中なら[1]が表示され、接続されていない場合は[0]が表示されます。

Openの後なので[1]が表示されます。

CN.Close

VBAとデータベースの接続を解除します。

そのあとのMsgBox CN.Stateは[0]になります。

Set CN = Nothing

作成したインスタンスを開放します。

以上でデータベースとの接続チェックは完了です。

次回以降でデータベースファイルの確認、更新、入力等を扱っていきます。

お疲れ様でした。