Excel VBA: Simple DB access by ODBC without ADO

How to use ODBC from VBA without ADO object? 

The answer is the following.


Create Access File:

Create the following sample Access table.


Table name: ProductMaster
Column names: ID, Product, Amount

And, Save as a SampleDB.accdb in appropriate folder.


Create DSN:

Create a DSN by the following.

Click Search button on taskbar.

Type "odbc", and select appropriate ODBC Data Source. (If your Access is 64bit, select 64bit Data Source)

Click Add... 

(If other user needs to use this DSN, select "System DSN" tab, and then click it.)

Select "Microsoft Access Driver (*.mdb, *.accdb), and click Finish.

Type "SampleDB" into Data Source Name,

and then click Select button.

Select SampleDB.accdb and click OK.

Click OK.

Click OK.


Create Table:

Open an Excel book, and then execute the following sub procedure.

Private Sub uCreateTableWithQuery()
    Dim uWS As Worksheet
    Dim uList As ListObject
    
    Set uWS = ActiveSheet
    Set uList = uWS.ListObjects.Add( _
        SourceType:=xlSrcQuery, _
        Source:="ODBC;DSN=SampleDB", Destination:=Range("A3"))
    
    uList.DisplayName = "ExcelProductMaster"
End Sub

It will make the following table that is connected to the DSN via QueryTable.


Reflesh Table:

Execute the follwing sub procedure.

Public Sub uRefleshQueryTable()
    Dim uList As ListObject
    Dim uSQL As String
    
    Set uList = ActiveSheet.ListObjects("ExcelProductMaster")
    
    uSQL = "SELECT * FROM ProductMaster"
    Debug.Print uSQL 'for Debug
    
    With uList.QueryTable
        .CommandText = uSQL
        .AdjustColumnWidth = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

It will reflesh the table.


You can customize the SQL string. For example.

uSQL = "SELECT * FROM ProductMaster WHERE Product='XBOX'"

will get the following result.


for your information.


コメント

アクセス数の多い投稿

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

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

Teamsで日本語入力すると左上に変換ウィンドウが出る

Amazon Prime Videoで4K UHD映画を検索する方法

NEC Aterm WX5400HP をセットアップ

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

Microsoft Flight Simulator (2020)のPS4コントローラー設定

次期主力Wi-Fiルーター NEC Aterm WX5400HP

iPhone 12 Proと iPhone 8 Plusのサイズを比較

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