小さい会社の1人SEの開発記というか奮戦記

エクセルのVBAからアクセスの Northwind データーベースに接続し、SQL文を発行して条件の合うデータをエクセルのワークシートに取り込みます。

条件整理

Accessファイル	D:¥db¥NorthWind.accdb
インポート元	テーブル「Employees」
インポート先	Excelシート名「Sheet1」セルA5
処理	First Name が Nancy と合致するもの

ExcelからAccessに接続するには準備が必要です。事前に「ExcelからVBAでデーターベースを使う準備」を参照しておいて下さい。

VBAでのデータ取り込み手順

一連の手順をまとめると以下となります。

1. 変数の宣言
2. データベースに接続
3. 取得したデータをワークシートに挿入する
4. データベースの接続を解除する

変数の宣言

Dim strfn As String ‘アクセスファイル名
Dim adocn As Object ‘ADOコネクションオブジェクト
Dim adors As Object ‘ADOレコードセットオブジェクト
Dim strsq As String ‘SQL文

データベース接続

‘　　アクセスファイル名
strfn = “D:¥db¥NorthWind.accdb”

‘　　ADOコネクションオブジェクトを作成
Set adocn = CreateObject(“ADODB.Connection”) 

‘　　Accessファイルに接続(アクセスに接続する場合の決まり文句だと思って下さい)
adocn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strfn & “;”

‘　　ADOレコードセットオブジェクトを作成
Set adors = CreateObject(“ADODB.Recordset”)

データをワークシートに挿入

‘　　検索のためのSQL文、ダブルクオート(“)をSQL文に入れたい時は2つ連続して書きます。
strsq = “SELECT * FROM Employees WHERE [First Name] = “”Nancy”””

‘　　作成したSQL文でRecordSetを作成
adors.Open strsq, adocn 

‘　　セルA5にRecordSetのデータを挿入
Range(“A5”).CopyFromRecordset adors

データベース接続解除

‘　　レコードセットのクローズ
adors.Close

‘　　コネクションのクローズ
adocn.Close

‘　　オブジェクトの破棄
Set adors = Nothing
Set adocn = Nothing

まとめ

[ ボタン 1 ]をクリックするとセルA5にデータが挿入されます。

 

作成したVBAは以下の通りです。

Sub ボタン1_Click()

'   変数宣言
Dim strfn As String '　　アクセスファイル名
Dim adocn As Object '　　ADOコネクションオブジェクト
Dim adors As Object '　　ADOレコードセットオブジェクト
Dim strsq As String '　　SQL文

'   アクセスファイル名
strfn = "D:\db\NorthWind.accdb"
 
'　　データベース接続 
Set adocn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adocn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strfn & ";" 'Accessファイルに接続
Set adors = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
 
strsq = "SELECT * FROM Employees WHERE [First Name] = ""Nancy"""
adors.Open strsq, adocn 'SQLを実行して対象をRecordSetへ
Range("A5").CopyFromRecordset adors 'レコードセットのデータをセルA5に挿入

'　　データベース接続解除 
adors.Close 'レコードセットのクローズ
adocn.Close 'コネクションのクローズ
 
Set adors = Nothing  'オブジェクトの破棄
Set adocn = Nothing
     
End Sub

 

SQL文をそのままVBAに埋め込んでしまうと、汎用性に欠けてしまいます。

次回はワークシートに入力した値を検索条件にSQL文を発行するサンプルを作成します。

関連記事 →「ExcelからVBAでデーターベースを使う準備」
関連記事 →「VBAでAccessのテーブルの内容を全てExcelに挿入する」