Excel VBAからODBCを使ってデータを簡単に取得する

VBAから ADO を使わずにODBC経由でデータベースにアクセスする方法です。


サンプルデータベース

次のようなアクセスのデータベースがあるとします。

テーブル名は「商品マスター」
カラム名は、ID, 商品名, 金額
保存してあるファイルは C:\Test\SampleDB.accdb


DSNの用意

このファイルにアクセスするための DSN を作成します。(作成方法は後述)


VBAの実行

Excel でシートを開き、次のプロシージャーを実行します。これはテーブルを作成するプロシージャーなので、最初に一回実行するだけです。

Private Sub uCreateTableWithQuery()
    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を利用可能です。


次のプロシージャーを実行します。

Public Sub uRefleshQueryTable()
    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を直接実行すると問題なくソートされていました)

ただし、当然ですが書式等は設定し直しになります。


毎回テーブルを作り直すサンプルも用意しました。

VBA: ODBCを使って毎回テーブルを作成するサンプル


クエリテーブルを使うメリット

クエリテーブルを使うと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を使用します。その場合は管理者権限が必要です。

  1. コントロールパネルを開き「管理ツール」を開き、ODBC Data Sources(32-bit)を開きます。(アプリが 64bit なら ODBC データソース 64bit を選択します)



  2. 「追加」ボタンを押します。

     
  3. Microsoft Access Driver (*.mdb, *.accdb) を選択して「完了」を押します。

  4.  
  5. データソースに後から判別できる分かりやすい名前を入れ、「選択」ボタンを押します。
  6.  
  7. Access のデータファイルを選択して、「OK」ボタンを押し、元のウィンドウで再度「OK」を押します。
  8.  
  9. データソースが追加されているはずです。


DBのコンピューター名を変えたらクエリが動かなくなる

DBのコンピューター名を変更したところ既存クエリが動作しなくなりました。こういう事を考えても、テーブルは毎回作成し直した方が良さそうです。


PowerQueryを使ってCSVファイルを読み込む

ODBCとは関係ありませんが、CSVファイルを読み込む方法も書いてみました。


コメント

アクセス数の多い投稿

セキュリティ対策ソフトのノートンが詐欺ソフトまがいになってしまってショック

ZIPファイルを開こうとすると、展開を完了できません、と言われる

Excel 2019 クエリが原因で日本語入力の一文字目が勝手に確定する

突然滅茶苦茶遅くなったPCがWindows Updateのキャッシュクリアで復活

オカムラ家具のOAチェアー、コンテッサを分解清掃

Windows セキュリティーのビックリマークが消えない

ChatGPTが日本語からVBAのコードを生成できてたまげる

Excelのテーブルに行や列を挿入する際のエラー

Power Automate Desktopでブラウザでダウンロードしたファイルを処理する