みなさん、こんにちはKITAです。社内のデータベースにAccessを使っていらっしゃる方は多いと思います。ですが、アクセスにはエクセルのような計算機能がないので、エクセルでデータ処理してからアクセスにデータを入力している方は多いのではないでしょうか？エクセルのセルをコピーして、アクセスのフォームにペースト、というなかなかハードな作業をこなしていらっしゃるかたもいらっしゃいます。そこで、業務の自動化にもつながる、エクセルとアクセスの連携方法をご紹介いたします。これができるようになると、エクセルでデータの集計処理、そしてエクセルからアクセスに直接データを入力することができます。
ADOを使おう！
今回はADO(ActiveX Data object)を使います。Accessだけでなく、Oracle、MySQLなどの様々なデータベースに対して共通の手法で操作が出来る仕組みです。ExcelからADOを使用するためには、まず参照設定を行います。
必
事前準備としてVBEの「ツール」メニュー → 「参照設定」をクリックし、「参照設定」ダイアログボックスを表示し、「Microsoft ActiveX Data Objects X.X Library」にチェックを入れます（X.Xはバージョン）。
Excel上でAccessデータベースに接続してレコードを読み込む。
ExcelからAccessデータベースに接続して、レコードを読み込み、表示しましょう。
まず、Access上にテーブルを作成します。ID,品名、型番、単価のシンプルなレコードを作成します。テーブル名はt_itemとします。テーブルデザインや入力内容は以下の通りです。
アクセスのテーブル
次に、ExcelからAccessのテーブルをADOで読み込む為の設定を行います。Excelを起動し、VBE(Visual Basic Editor)を[Alt]+[F11]で起動し、ツール(T) → 参照設定(R) をクリック。
参照設定のウィンドウが開いたらMicrosoft ActiveX Data Objects 2.X Libraryにチェックを入れます。
最後に、テーブルをADOで読み込む為のVBAコードを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
Sub Access_read() Dim adoCON As New ADODB.Connection Dim adoRS As New ADODB.Recordset Dim strSQL As String Dim odbdDB As Variant Dim wSheetName As Variant Dim i As Integer 'カレントディレクトリのデータベースパスを取得 odbdDB = ActiveWorkbook.Path & "\test.accdb" 'データベース接続 adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & odbdDB & "" adoCON.Open 'DB接続用SQL strSQL = "SELECT t_item.* FROM t_item ORDER BY t_item.ID;" 'レコードセットを開く adoRS.Open strSQL, adoCON, adOpenDynamic 'アクティブシート名を取得 wSheetName = ActiveSheet.Name 'スタート行をセット i = 1 'テーブルの読み込み Do Until adoRS.EOF With Worksheets(wSheetName) .Cells(i, 1).Value = adoRS!ID .Cells(i, 2).Value = adoRS!品名 .Cells(i, 3).Value = adoRS!型番 .Cells(i, 4).Value = adoRS!単価 End With i = i + 1 adoRS.MoveNext Loop 'クローズ処理 adoRS.Close Set adoRS = Nothing adoCON.Close Set adoCON = Nothing End Sub |
VBAソースの説明
1.変数の設定
3－8行目でADOで使用する変数を宣言します。
- Excelブックと同じフォルダ内のAccessのtest.accdbのフルパスを取得する。
1１行目でActiveWorkbook.Pathを使いExcelのフォルダを取得します。
|
1 |
odbdDB = ActiveWorkbook.Path & "\test.accdb" |
2.データベース接続
14～16行目でAccessデータベースに接続します。
今回使用するアクセスのバージョンは2007以降なので、providerにはMicrosoft.ACE.OLEDB.12.0を指定して下さい。
|
1 2 3 4 5 |
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & odbdDB & "" adoCON.Open |
3.SQLを実行
19行目でDBに接続するSQLを実行します。アクセスのテーブル名を記述します。
|
1 |
strSQL = "SELECT t_item.* FROM t_item ORDER BY t_item.ID;" |
4.レコードセットを開く
22行目のOpenメソッドでレコードセットを開きます。
|
1 |
adoRS.Open strSQL, adoCON, adOpenDynamic |
5.アクティブシート名を取得
25行目で現在アクティブになっているワークシート名を取得します。
|
1 |
adoRS.Open strSQL, adoCON, adOpenDynamic |
6.スタート行をセット
28行目でエクセルシートに書き込みをはじめ行数をセットします。
|
1 |
i = 1 |
7.テーブル内のデータを全てExcelに出力
31～41行目でDo Until Loopを使い、レコードをExcelに繰り返して出力します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Do Until adoRS.EOF With Worksheets(wSheetName) .Cells(i, 1).Value = adoRS!ID .Cells(i, 2).Value = adoRS!品名 .Cells(i, 3).Value = adoRS!型番 .Cells(i, 4).Value = adoRS!単価 End With i = i + 1 adoRS.MoveNext Loop |
8.データベースをクローズ
44-47行でCloseメソッドを使いレコードセットとADOコネクションオブジェクトを閉じます。それぞれの変数にNothingをセットしてクリアします。
|
1 2 3 4 5 6 7 |
adoRS.Close Set adoRS = Nothing adoCON.Close Set adoCON = Nothing |
マクロの実行
Access_read()マクロを実行しましょう。
エクセルにアクセスのデータが転記されました。
エクセル＆アクセステンプレート差し上げます。
今回作成したエクセルとアクセスのサンプルを無料で差し上げます。
お問い合わせから、コメント欄に「ExcelとAccessを連携してみよう(1)テンプレ希望」と記入して送信ください。
まとめ
以上、今回はExcelからAccessにADOを使って接続する方法でした。次回は、エクセルからアクセスに書き込む方法をご紹介します。
エクセルマクロVBA作成に困ったら
VBAのコードがわからず検索してもなかなかいいコードが見つからない、そんんなお悩みはございませんか？開発委託・コンサルティング・ワンポイントサポートいたします。お気軽にどうぞ。
