Excel VBAからODBCを使ってデータを簡単に取得する
VBAから ADO を使わずにODBC経由でデータベースにアクセスする方法です。
サンプルデータベース
次のようなアクセスのデータベースがあるとします。
テーブル名は「商品マスター」
カラム名は、ID, 商品名, 金額
保存してあるファイルは C:\Test\SampleDB.accdb
DSNの用意
このファイルにアクセスするための DSN を作成します。（作成方法は後述）
VBAの実行
Excel でシートを開き、次のプロシージャーを実行します。これはテーブルを作成するプロシージャーなので、最初に一回実行するだけです。
Dim uWS As Worksheet
Dim uList As ListObject
Set uWS = ActiveSheet
Set uList = uWS.ListObjects.Add( _
SourceType:=xlSrcQuery, _
Source:="ODBC;DSN=NK Test", Destination:=Range("A3"))
uList.DisplayName = "ExcelProductMaster"
End Sub
DSN=にDSN名を指定します。この例では、NK Test。
Destination はテーブルの左上を配置するセルです。この例ではA3セル。
DisplayName はテーブル名を指定します。この例ではExcelProductMaster。
実行すると次のようにテーブルが作成されます。テーブルはQueryTableに接続され、QueryTableはDSNを利用可能です。
次のプロシージャーを実行します。
Dim uList As ListObject
Dim uSQL As String
Set uList = ActiveSheet.ListObjects("ExcelProductMaster")
uSQL = "SELECT * FROM 商品マスター"
With uList.QueryTable
.CommandText = uSQL
.AdjustColumnWidth = False '列幅を調整しない
.Refresh BackgroundQuery:=False
End With
End Sub
ListObjectにはテーブル名を指定します。この例ではExcelProductMaster。
uSQLにSQL文を適宜指定します。
実行すると、Accessデータベースの内容が読み込まれます。
uRefleshQueryTable を実行するたびに、Access からデータが読み込まれます。
次のように更新ボタンを用意して uRefleshQueryTable を呼び出すようにすれば、簡単にデータのリフレッシュが可能になります。
クエリの破棄
作ったテーブルを破棄したい場合にはテーブル範囲のセルを選択して、「すべてクリア」を実行します。関連付けられたクエリを削除するか聞かれるので「はい」を選択すれば、クエリごと削除できます。
かなりSQLやテーブルをいじった場合には、列並びや更新がおかしくなり、破棄が必要になるときがあります。
最初の uCreateTableWithQuery を実行すれば簡単にテーブルを再作成できるので、うまく行かなければ削除しましょう。
（先日もサブクエリを使ったSQLを色々試すうちにORDER BYが効かなくなりしばし悩みましたが、テーブルを削除したら直りました。うまくいかない時、データベースで同じSQLを直接実行すると問題なくソートされていました）
ただし、当然ですが書式等は設定し直しになります。
毎回テーブルを作り直すサンプルも用意しました。
クエリテーブルを使うメリット
クエリテーブルを使うとSQL文を作成するだけで、上記のように非常に短いコードでデータを読み込むことができます。
SQL文に列を追加すれば、自動的にテーブルも拡張されます。
テーブル列の並びを変えてもある程度は大丈夫です。次の例では金額と商品名の並びを変えて更新しています。きちんと後から追加した Nintendo64 が読み込まれています。
また、Excel の内部コードでテーブルに書き込むため、おそらく VBA から一行一行セルにデータを書き込むよりも速いのではないかと思います。
選択条件を指定する
条件指定を加えてみます。
B1セルに「商品名」という名前を付けてあります。
次のようにコードを変更し、「商品名」セルがブランクでなければ where 句を追加します。
Public Sub uRefleshQueryTable2()
Dim uList As ListObject
Dim uSQL As String
Dim uName As String
Set uList = ActiveSheet.ListObjects("ExcelProductMaster")
uSQL = "SELECT * FROM 商品マスター"
uName = Range("商品名")
If uName <> "" Then
uSQL = uSQL & " WHERE 商品名 = '" & uName & "'"
End If
Debug.Print uSQL 'ODBCエラー調査用
With uList.QueryTable
.CommandText = uSQL
.AdjustColumnWidth = False '列幅調整しない
.Refresh BackgroundQuery:=False
End With
End Sub
PS5を入力して実行した結果です。
このように、シートに条件を用意すればユーザーが簡単に絞り込みを行えるようになります。
もちろん、テーブルのフィルターも使えるので、実際にはもっと複雑な条件の指定の仕方が多いと思います。
ユーザーDSNの追加方法
参考までに Access のファイルに対するユーザー DSN の追加方法を書いておきます。
ユーザー DSN はユーザー毎の DSN です。誰がログインしても使えるようにしたいのであれば、システムDSNを使用します。その場合は管理者権限が必要です。
- コントロールパネルを開き「管理ツール」を開き、ODBC Data Sources(32-bit)を開きます。（アプリが 64bit なら ODBC データソース 64bit を選択します）
- 「追加」ボタンを押します。
- Microsoft Access Driver (*.mdb, *.accdb) を選択して「完了」を押します。
- データソースに後から判別できる分かりやすい名前を入れ、「選択」ボタンを押します。
- Access のデータファイルを選択して、「OK」ボタンを押し、元のウィンドウで再度「OK」を押します。
- データソースが追加されているはずです。
 |
 |
 |
DBのコンピューター名を変えたらクエリが動かなくなる
DBのコンピューター名を変更したところ既存クエリが動作しなくなりました。こういう事を考えても、テーブルは毎回作成し直した方が良さそうです。
コメント
コメントを投稿