エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム


connection

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAからAccessデータベースを操作する方法についてシリーズでお伝えしています。

前回は最初の記事ということでコチラ。

なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
エクセルと連携するデータベースの第一候補であるMicrosoft Access。今回は、なぜエクセルとAccessを連携するのが良いのか、またその際の注意点についてお伝えしたいと思います。

エクセルVBAとAccessを連携するメリットと注意点についてお伝えしました。

さて、今回からいよいよ実際にエクセルVBAからAccessデータベースに接続をしていきたいと思います。

接続をして切断するだけの超簡単プログラムです。

では行ってみましょう!

スポンサーリンク

Accessデータベース内にテーブルを準備

まずはAccessでこんなテーブルをを作ってみました。

Accessで作成したテーブル

都道府県ごとの面積や人口、区市町村数などをまとめたデータです。テーブル名は「データ」という名前です。

このテーブルを含むAccessデータベースファイルを「test.accdb」というファイル名で、今回のエクセルVBAを記述したファイルと同じフォルダに格納しました。

エクセルVBAからこのデータベースへの接続を試みていきます。

ADODB.Connectionオブジェクトでデータベースに接続する

Accessデータベースと接続をしてやり取りをする際には、ADODB.Connectionオブジェクトを使います。

ADODB…?どこかで聞いたような名前ですね…

ADOはActiveX Data Objectsの略で

ADOとは、Microsoftが提唱しているデータアクセス技術のことである。
引用:Weblio辞書-ADO

ADOはその目的によりさらにいくつかに区別されますが、そのうちデータの操作をする場合に使用するのがADODBとなります。

さらにさらにADODBはその目的に応じていくつかのオブジェクトを使い分けることになりますが、AccessをはじめとしたOracle、MySQLなどの各種データベースの接続をする際にはADODB.Connectionオブジェクトを使います。

このADODB.ConnectionオブジェクトがAccessデータベースとエクセルVBAの橋渡しの役割を果たします。

ちなみに、別記事でUTF-8のCSVを取り扱う際にはADODB.Streamオブジェクトを使うということをお伝えしていましたね。

文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法
エクセルVBAのLine Input命令でUTF-8のCSVファイルを取り込むと文字化けを起こします。今回は、ADODB.Streamを使ってUTF-8のCSVファイルを取り込む方法についてお伝えします。

ADODBライブラリを追加する

その前に、ADODB.Connectionを使うためには、ADODBライブラリを追加する必要があります。

Visual Basic Editorのメニューから「ツール」→「参照設定」を開いて、ライブラリの中から「Microsoft ActiveX Data Objects x.x Library」を見つけてチェックを入れます。執筆時のバージョンは「2.8」ですが、最新のものを選べばOKです。

VBEでADODBライブラリを追加

Accessデータベースに接続するVBAプログラム

では、実際にAccessデータベースに接続するVBAプログラムを書いてみましょう。

Sub ConnectDB()

Dim strFileName As String
strFileName = "test.accdb" 'データベースのファイル名

Dim adoCn As Object
Set adoCn = CreateObject("ADODB.Connection") 'ADODBコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

adoCn.Close 'コネクションのクローズ
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

以下で解説をしていきますね。

ADODB.Connectionオブジェクトの生成

まず、7行目でADODB.Connectionオブジェクトを生成して、adoCnという変数で取り扱えるようにセットをしています。

Set オブジェクト変数 = CreateObject(“ADODB.Connection”)

これは決まり文句ですので、Accessデータベースを取り扱うときにはコピペをしてプログラム冒頭入れておきましょう。

Accessデータベースへの接続をオープンする

次にAccessデータベースへの接続をオープンします。プログラムの8行目ですね。

ADODBコネクションオブジェクト.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Accessデータベースファイルのフルパス;”

これで指定したAccessデータベースへの接続がオープン状態になり、データベースの操作ができるようになります。

ProviderとData Sourceという二つのパラメータがありますが、Accessデータベースの場合はProviderは「Microsoft.ACE.OLEDB.12.0」で固定です。Data SourceにAccessデータベースのファイルをフルパスで指定してあればOKです。

今回は、エクセルVBAのファイルと同じフォルダにあるという前提ですので、ThisWorkbook.Pathを活用して指定をしています。

データベースへの接続をクローズしてオブジェクトを破棄

データベースの操作が終わったら、データベースへの接続をクローズします。プログラムの10行目です。

ADODBコネクションオブジェクト.Close

これで該当のADODB.Connectoinオブジェクトの接続がクローズします。

最後に11行目ですが、オブジェクトを破棄してあげています。

この二行もデータベースを利用した後はお決まりフレーズとして入れて頂ければOKです。

実行結果

…このプログラムを実行しても、なーんにも起きません。

実際にデータベースに接続できているのか…?と心配になりますが、できています。(たぶん)

次回、ちゃんとデータを取り出してみせますよ。

まとめ

何にも起きないつまらないプログラムを紹介してしまいました。こんなことは初めてです。

ですが、エクセルVBAでAccessデータベースに接続する際には必須の手続きになりますので、ぜひしっかり押さえておいて頂ければと思います。

以降、エクセルVBAでAccessデータベースを操作する手順としては

  1. ADODB.Connectionオブジェクトを生成
  2. ADODB.Connectionオブジェクトの接続をオープンして目的のAccessデータベースに接続
  3. Accessデータベースを操作
  4. ADODB.Connectionオブジェクトの接続をクローズ
  5. ADODB.Connectionオブジェクトを破棄

というものが基本になります。

少しややこしいですが、お決まりですので…マスターしちゃいましょう!

次回、接続したAccessデータベースからデータを取得していきたいと思います。

【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。

どうぞお楽しみに!

連載目次:エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。
  1. なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
  2. エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
  3. 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
  4. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
  5. 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
  6. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  7. エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
  8. 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
  9. エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
  10. エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
  11. エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
  12. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  13. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  14. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  15. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  16. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
  17. エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる

タイトルとURLをコピーしました