It is possible to query Oracle table data from VBA using QueryTables. The specification for the connection string may vary from RDBMS to RDBMS, but otherwise, The implementation is straightforward. For example, assume we’re interested in querying and Oracle table SAMPLE_TABLE present in SAMPLE_SCHEMA in SAMPLE_DB and authenticated by password1 using DSN SAMPLE_SCHEMA, and that we need to return all records from SAMPLE_TABLE to worksheet From_DB starting in cell A1:

'================================================
' Query and retrieve Oracle table data via ODBC |
'================================================
Public Sub CaptureTableODBC( _
                        ByVal Tablename As String, _
                        ByVal Schema As String, _
                        ByVal Database As String, _
                        ByVal Password As String)


' Variable declarations =>
Dim SQLstr As String
Dim connStr As String
Dim sht As Worksheet: Set sht = ThisWorkbook.Worksheets("From_DB")

' Clear all content in `From_DB` =>
sht.UsedRange.Clear

' Create query string =>
Tablename = Application.WorksheetFunction.Trim(Tablename)
SQLstr = "SELECT * FROM " & CStr(Tablename)

' Compile connection string =>
connStr = _
    "ODBC;DSN=" & Schema & ";UID=" & Schema & ";PWD=" & Password & ";Database=" & Database

With sht.QueryTables.Add( _
                Connection:=connStr, _
                Destination:=sht.Range("A1"), _
                SQL:=SQLstr)
    .Refresh
End With

' Terminate connection =>
connStr.Close

End Sub

The QueryTables method is straightforward, and works well if the macro will not be utilized by anyone other than the creator. But if the intention is to distribute the subprocedure for use by other analysts, this approach is less than optimal.

When querying databases from Excel via ODBC, it is necessary to have a DSN with which to associate a given Data Source. In the example above, the DSN was identical to the name of the Schema of interest, but there is no way to ensure all potential future users will follow the same DSN naming convention. This is the source of many of the compatibility issues facing developers wishing to distribute VBA applications that interact with an external data source using ODBC. Fortunately, there’s an alternative, more flexible API that can be utilized.

Querying External Data Sources with ActiveX Data Objects (ADO)

Microsoft’s ActiveX Data Objects (ADO) comprises a set of Component Object Model (COM) objects for accessing data sources. In terms of portability, querying external data sources with ADO is superior to ODBC since ADO permits DSN-less data source connections, meaning as long as you can successfully authenticate to a given external data source, it’s possible to retrieve datasets from Excel/VBA without requiring a DSN.

The following query accesses the same sample table as above, but using ADO instead of ODBC. The first function, CreateConnectionStr, generates the connection string required to connect to an Oracle database (check out this site for other RDBMS connection strings):

'================================================
' Compile connection string for ADO usage       |
'================================================
Public Function CreateConnectionStr( _
                            ByVal Database As String, _
                            ByVal Schema As String, _
                            ByVal Password As String)

Const Provider As String = "OraOLEDB.Oracle"
CreateConnectionStr = "Provider=" & CStr(Provider) & ";" & _
                      "Data Source=" & CStr(Database) & ";" & _
                      "User Id=" & CStr(Schema) & ";" & _
                      "Password=" & CStr(Password) & ";"

End Function


'================================================
' Query and retrieve Oracle table data via ADO  |
'================================================
Public Sub CaptureTableADO(ByVal Tablename As String)

' Variable declarations =>
Dim SQLstr As String
Dim connStr As String
Dim i As Long
Dim sht As Worksheet: Set outsht = ThisWorkbook.Worksheets("From_DB")
Dim conn As Object: Set conn = CreateObject("ADODB.Connection")
Dim recordSet As Object: Set recordSet = CreateObject("ADODB.Recordset")
Const Provider As String = "OraOLEDB.Oracle"


' Clear all content in `From_DB` =>
sht.UsedRange.Clear

' Create query string =>
Tablename = Application.WorksheetFunction.Trim(Tablename)
SQLstr = "SELECT * FROM " & CStr(Tablename)

' Call CreateConnectionStr =>
Set connStr = CreateConnectionStr("SAMPLE_DB", "SAMPLE_SCHEMA", "password1")

' Open connection =>
conn.Open connStr
recordSet.Open SQLstr, conn


' Put headers in First Row =>
For i = 1 To recordSet.Fields.Count
    sht.Cells(1, i).Value = recordSet.Fields(i - 1).Name
Next

' Write dataset to `From_DB` =>
sht.Range("A2").CopyFromRecordset recordSet


' Terminate connections =>
conn.Close
Set conn = Nothing
Set recordSet = Nothing

End Sub

Notice that it’s necessary to manually write the dataset’s headers to the first row of From_DB. This is a small price to pay for the kind of flexibility a DSN-less connection offers.

Since CaptureTableODBC and CaptureTableADO are subprocedures requiring arguments to be passed in, it is not possible to call either of these subprocedures directly. An argument-less subprocedure needs to call CaptureTableODBC or CaptureTableADO, passing the necessary arguments to the subprocedures. For example:

'=========================
'Calling CaptureTableADO |
'=========================

Public Sub Caller()

Dim Tablename As String: Tablename = "SAMPLE_TABLE"

Call CaptureTableADO(Tablename)

End Sub

Interacting with external datasets and sources is a common task among analysts of all kinds. Creating portable, scalable and robust tools to interact with those data sources can be greatly simplified by removing the DSN requirement altogether.