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

- 12月 19, 2021

業務アプリケーションのデータベースから ODBC を使ってデータを取得できるようになったので、Excel VBAでの ODBC 利用方法を書いてみたいと思います。

ADOを使う方法

Google 先生に利用方法を聞くと ADO を使う方法を解説しているページを紹介してくれます。例えば次のようなページです。

ADO(ActiveX Data Objects)の使い方の要点エクセルの神髄

私も最初はこのようなサイトを参考にして ADODB オブジェクトを使ってデータベースからデータを取得していました。

しかし、データベースで使っている Unicode の文字列（簡体字）が化けてしまうという問題が生じ、DSN の設定を変えたり色々試しましたが解決せず諦めました。

なお、データベースはマイナーなIntersystems のCacheです。

クエリを使う方法

Excel のクエリを使ったところ、文字化けしなかったので、現在はこちらの方法を使っています。

データベース

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

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

DSNの用意

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

VBAのコード

次のコードを用意します。（ご自分の環境で実行する場合は適切なDSN名を指定して、SQL文も適切なものを指定します。Destination にはテーブルの左上の位置を指定します）

Option Explicit

'テーブル作成　データシートに最初のテーブルを作るときだけ使用する
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 = "TBL_" & uWS.Name
End Sub


'クエリーテーブル更新
Public Sub gRefleshQueryTable()
    Dim uList As ListObject
    Dim uSQL As String
    
    Set uList = ActiveSheet.ListObjects("TBL_" & ActiveSheet.Name)
    
    uSQL = "SELECT * FROM 商品マスター"
    
    With uList.QueryTable
        .CommandText = uSQL
        .AdjustColumnWidth = False '列幅調整しない
        
        Debug.Print uSQL 'ODBCエラー調査用
        .Refresh BackgroundQuery:=False
    End With
End Sub

注意：　この例ではテーブル名は「TBL_シート名」になります。重複に注意しましょう。

実行

データを読み込みたいシートを開き、uCreateTableWithQuery プロシージャーを実行します。これは最初に一回実行するだけです。

するとアクティブシートに次のようにテーブルが追加されます。

次に、 gRefleshQueryTable プロシージャーを実行します。

すると、上のAccessデータベースの内容が読み込まれます。

gRefleshQueryTable を実行するたびに、Access からデータが読み込まれます。

次のように更新ボタンを用意して gRefleshQueryTable を呼び出すようにすれば、簡単にデータのリフレッシュが可能になります。

クエリの破棄

作ったテーブルを破棄したい場合にはテーブル範囲のセルを選択して、「すべてクリア」を実行します。関連付けられたクエリを削除するか聞かれるので「はい」を選択すれば、クエリごと削除できます。

かなりSQLやテーブルをいじった場合には、列並びや更新がおかしくなり、破棄が必要になるときがあります。

uCreateTableWithQuery を実行すれば簡単にテーブルを再作成できるので、うまく行かなければ削除しましょう。

（先日もサブクエリを使ったSQLを色々試すうちにORDER BYが効かなくなりしばし悩みましたが、テーブルを削除したら直りました。データベースで同じSQLを直接実行すると問題なくソートされていました）

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

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

クエリテーブルを使うとSQL文を作成するだけで、上記のように非常に短いコードでデータを読み込むことができます。

SQLに列を追加すれば、自動的にテーブルも拡張されます。

テーブル列の並びを変えてもある程度は大丈夫です。次の例では金額と商品名の並びを変えて更新しています。きちんと後から追加した Nintendo64 が読み込まれています。

また、Excel の内部コードでテーブルに書き込むため、おそらく VBA から一行一行セルにデータを書き込むよりも速いのではないかと思います。

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

テーブルが必要です。

テーブルの置き場所を考えなくてはいけないのが面倒です。

応用

条件指定を加えてみます。

B1セルに"_商品名"という名前を付けてあります。

次のようにコードを変更し、_商品名セルがブランクでなければ where 句を追加します。

'クエリーテーブル更新

Public Sub gRefleshQueryTable()

Dim uList As ListObject

Dim uSQL As String

Dim uName As String

Set uList = ActiveSheet.ListObjects("TBL_" & ActiveSheet.Name)

uName = Range("_商品名")

uSQL = "SELECT * FROM 商品マスター"

If uName <> "" Then

uSQL = uSQL & " WHERE 商品名 = '" & uName & "'"

End If

With uList.QueryTable

.CommandText = uSQL

.AdjustColumnWidth = False '列幅調整しない

Debug.Print uSQL 'ODBCエラー調査用

.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」を押します。

データソースが追加されているはずです。

このブログを検索

趣味はたぶん旅行です