VBA: Store ODBC query result in code, rather than displaying it

Question

I have the following macro, which accepts a string argument code which is a SQL query, and executes it against my Teradata server and displays the results.

Dim dest As Range
Set dest = ActiveCell

Dim timestamp As String
timestamp = Format(Now, "yyyyMMdd_h:mm:ss_AM/PM")

Dim queryName As String
queryName = "Query_" & timestamp

ActiveWorkbook.Queries.Add Name:=queryName, formula:= _
    "let" & Chr(13) & "" & Chr(10) & " Source = Odbc.Query(""dsn=my-server-name"", " _
    & Chr(34) & code & Chr(34) & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) _
    & "" & Chr(10) & " Source"

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" _
    & queryName & ";Extended Properties=""""" _
    , Destination:=Range(dest.Address)).QueryTable

    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & queryName & "]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = False
    .Refresh BackgroundQuery:=False
End With

For one report I'm trying to automate, the first step is to query a particular timestamp from the database and make sure it shows today's date before proceeding with the rest of the report. I want to have a macro do that, but have it just return the timestamp value rather than displaying it-- that way I can write it into an If statement to conditionally run the next steps of the report.

Is there a way it can execute the query and store the returned timestamp in a variable rather than displaying it anywhere? Otherwise, I guess I'll just display it somewhere, interrogate the value and then delete it because I don't need it displayed.

Answer 1

ADODB Solution

To get data from a query into a variable I would use an ADODB connection and recordset. Recordsets have a method called GetRows which returns the data from the query results.

Below is a function that can be used to query for your data. This takes in your ConnectionString and your SQL Code and returns your data in the form of a two-dimensional array.

For ease of explaining, I made this function late binding. To make Early binding set a reference to the Microsoft ActiveX Data Objects x.x Library.

Private Function QueryDatabase(ByVal ConnectionString As String, ByVal Sql As String) As Variant
    
    On Error GoTo Catch
    
  
    'OPEN CONNECTION TO DATABASE
    Dim Connection As Object
    Set Connection = CreateObject("ADODB.Connection")
    Connection.ConnectionString = ConnectionString
    Connection.Open
    On Error GoTo CloseConnection 'MAKE SURE TO CLOSE CONNECTION ON ERRORS
    
    'OPEN DATA AND GET RECORDSET
    Dim Rs As Object
    Set Rs = CreateObject("ADODB.Recordset")
    With Rs
        .ActiveConnection = Connection
        .Source = Sql
        .LockType = 1 'adLockReadOnly - MAKES CONNECTION READ ONLY
        .CursorType = 0 'adOpenForwardOnly - FREQUENCY OF CHECKING DATABASE - SET TO ONCE
        .Open
        On Error GoTo CloseRecordset
    End With
    
    'RETURN QUERY RESULTS BACK INTO A TWO DIM ARRAY (DOES NOT INCLUDE HEADERS)
    QueryDatabase = Rs.GetRows
    
CloseRecordset:
    Rs.Close
    
CloseConnection:
    Connection.Close
    
    'CHECK IF ERROR OCCURED
    If Err.Number <> 0 Then
        GoTo Catch
    End If
    
    Exit Function
Catch:
    'HANDLE ERRORS HERE...
    
End Function

---

Timestamp Validation

For validating your timestamp I would abstract that into its own formula returning a boolean value. Not exactly sure how your data is structured, or how you want to achieve this but below is a blueprint of what you could attempt to do.

Private Function TimestampValid() As Boolean
    
    Dim QueryName As String
    QueryName = "Query_" & timestamp
    
    Dim Connection As String
    Connection = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryName & ";Extended Properties="""""
    
    Dim Sql As String
    Sql = "SELECT * FROM [" & QueryName & "]"
    
    'DATA IS A TWO DIM ARRAY (MINUS THE HEADERS)
    Dim Data As Variant
    Data = QueryDatabase(Connection, Sql)
    
    'DO YOUR VALIDATION HERE...
    '(NOT SURE WHAT YOU ARE RETURNING SO I CAN'T DO IT FOR YOU)...
    
End Function