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.