VBA Running SQL Query That Hits TeraData Database

[Dathan Camacho]

I'm sorry about the formatting below. I need some help, but I'm at work, and IT has it set up so I can't use any of the add-ins that make code more presentable.

If you'll pardon the formatting, here is my question:

The code below successfully goes out to Teradata and returns data directly into an Excel spreadsheet for SQL QueryA.

I'm trying to add a 2nd SQL query, QueryB, within the same macro.

QueryA is working fine, but for I get an error message "Object variable or With block variable not set" at the line that says cmdSQLData.CommandText = QueryB

Can anyone help me diagnose the problem? Thank you!

Sub Scorecard()
'BEFORE RUNNING THIS QUERY, CLICK TOOLS AND THEN CLICK REFERENCES.
'MAKE SURE THE 2 REFERENCES BELOW ARE SELECTED:
        
        'Microsoft ActiveX Data Objects 2.8 Library
        'Microsoft ActiveX Data Objects Recordset 2.8 Library
'=====================================================================================================
'DECLARE VARIABLES FOR WHERE CLAUSE OF SQL QUERY
        Dim Output1 As Worksheet
        Set Output1 = Worksheets("Output1")
        Dim Inputs As Worksheet
        Set Inputs = Worksheets("Inputs")
        Dim StoreNumbers As String
        Dim DeptNumbers As String
        Dim ItemNumbers As String
        
        Const FIRST_DATA_ROW As Integer = 2  'NOTE: THIS IS USED TO DEFINE 1ST DATA ROW OF THE INPUT SHEET, NOT THE DESTINATION SHEET
        
        Dim StoreLastRow As Long
        Dim DeptLastRow As Long
        Dim ItemLastRow As Long
'DEFINE VALUES FOR WHERE CLAUSE OF SQL QUERY
        With Inputs
            StoreLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            DeptLastRow = .Cells(Rows.Count, "B").End(xlUp).Row
            ItemLastRow = .Cells(Rows.Count, "C").End(xlUp).Row
        
            StoreNumbers = "'" & Join(Application.Transpose(Sheets("Inputs").Range("A" & FIRST_DATA_ROW & ":A" & StoreLastRow).Value), "','") & "'"
            DeptNumbers = "'" & Join(Application.Transpose(Sheets("Inputs").Range("B" & FIRST_DATA_ROW & ":B" & StoreLastRow).Value), "','") & "'"
        End With

'=====================================================================================================
'DECLARE VARIABLES FOR CONNECTION (HOW THE QUERY CONNECTS TO TERADATA)
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
'DECLARE VARIABLES FOR RECORDSET (THE RESULTS OF THE SQL QUERY)
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
'DECLARE VARIABLES FOR COMMAND (I THINK THIS MAKES TERADATA RUN THE QUERY AFTER A CONNECTION IS ESTABLISHED)
    Dim cmdSQLData As ADODB.Command
    Set cmdSQLData = New ADODB.Command
    
'=====================================================================================================
'CONNECT TO TERADATA
    cn.Open "Data Source=WML; Database=WMLAD; Persist Security Info=True; User ID=******; Password=******; Session Mode=ANSI;"
'PREPARE TO SEND A QUERY TO TERADATA (THIS IS A GUESS. I'M NOT 100% SURE WHAT THIS DOES EXACTLY)
    Set cmdSQLData.ActiveConnection = cn
'=====================================================================================================
'DEFINE THE 1ST QUERY
'=====================================================================================================
    QueryA = "select s.store_nbr,a.business_date,sum(a.repl_store_qty)as store_qty,sum(a.instk_repl_store_qty) As instk_qty from us_wm_vm.daily_item a,us_wm_vm.item  b, us_wm_vm.store_item s where s.old_nbr = b.old_nbr and s.store_nbr in(" & StoreNumbers & ") and a.item_nbr = b.item_nbr and a.business_date between '2010-11-01' and '2010-11-15' and a.repl_store_qty > 0 and b.dept_nbr in (" & DeptNumbers & ") and b.vnpk_cspk_code = 'B' group by 1,2"
  
'TELL VBA WHICH QUERY TO SEND TO TERADATA 1ST
    cmdSQLData.CommandText = QueryA 'TELL VBA TO LOAD THE QUERY INTO TERADATA
    cmdSQLData.CommandType = adCmdText
'PREVENT QUERY FROM TIMING OUT
    cmdSQLData.CommandTimeout = 0
'TELL TERADATA TO ASSIGN THE QUERY RESULTS TO A RECORDSET
    Set rs = cmdSQLData.Execute()
    
'=====================================================================================================
'DEFINES WHAT TO DO WITH THE RESULTS OF THE 1ST QUERY
'=====================================================================================================
    
'DEFINE THE FIRST DESTINATION ROW IN THE DESTINATION SPREADSHEET
    Row = 5
'SET POINTER TO FIRST RECORD OF RECORDSET (THE RESULTS OF THE SQL QUERY)
    rs.MoveFirst
'THE RECORDSET STRUCTURE CAN BE THOUGHT OF AS A MATRIX p CONTAINING ROWS AND FIELDS THAT BEGIN WITH ARRAY (0,0).
'-----------------------------------------------------------------------------------------------------
'IF THERE ARE RESULTS IN THE RECORDSET, START A LOOP THAT INSERTS THEM IN A SPREADSHEET. KEEP LOOPING UNTIL REACHING THE END OF THE RECORDSET.
        Do While (rs.EOF = False And rs.BOF = False)
'DEFINE HOW MANY ROWS TO RETURN AT ONCE FROM THE RESULTS MATRIX p
        p = rs.GetRows(1)
    
'A DESTINATION COLUMN MUST BE DEFINED FOR EACH COLUMN RETURNED BY THE SQL QUERY (EACH VARIABLE IN THE SELECT STATEMENT)
'THIS CODE INSERTS RESULTS INTO THE DESTINATION COLUMNS BY ROW
        Output1.Range("B" & Row).Value = p(0, 0) '1ST DESTINATION COLUMN IN DESTINATION SPREADSHEET
        Output1.Range("C" & Row).Value = p(1, 0) '2ND DESTINATION COLUMN IN DESTINATION SPREADSHEET
        Output1.Range("D" & Row).Value = p(2, 0) '3RD DESTINATION COLUMN IN DESTINATION SPREADSHEET
        Output1.Range("E" & Row).Value = p(3, 0) '4TH DESTINATION COLUMN IN DESTINATION SPREADSHEET
'MOVE TO NEXT ROW OF DESTINATION SPREADSHEET
        Row = Row + 1
'LOOP AND CONTINUE RETURNING ROWS TILL END OF RECORDSET
        Loop
'-----------------------------------------------------------------------------------------------------
'CLEAR THE RECORDSET OF THE RESULTS FROM THE 1ST QUERY
    Set rs = Nothing
'CLEAR THE TERADATA INSTRUCTIONS
    Set cmdSQLData = Nothing
'=====================================================================================================
'DEFINE THE 2ND QUERY TO SEND
'=====================================================================================================
    QueryB = "SELECT L1.STORE_NBR, L1.BIN_DATE,SUM(L1.BIN_ONHAND_UNITS)AS BIN_ONHAND_UNITS,SUM(L1.BIN_ONHAND_WHPK)AS BIN_ONHAND_WHPK FROM (SELECT BIOH.STORE_NBR,BIOH.ACCTG_DEPT_NBR,BIOH.ITEM_NBR,I.UPC_NBR,I.WHPK_QTY,I.WHPK_SELL_AMT,CAST(BIOH.SNAPSHOT_TS AS DATE) AS BIN_DATE,MAX(BIOH.SNAPSHOT_TS)  AS BIN_TIMESTAMP,SUM(BIOH.BIN_ONHAND_QTY) AS BIN_ONHAND_UNITS,BIN_ONHAND_UNITS / I.WHPK_QTY AS BIN_ONHAND_WHPK,BIN_ONHAND_WHPK * I.WHPK_SELL_AMT AS BIN_ONHAND_COST FROM US_WM_BIN_VM.BIN_ITEM_ON_HAND BIOH, US_WM_VM.ITEM I WHERE BIN_DATE BETWEEN DATE - 181 AND DATE - 1 AND BIOH.ITEM_NBR = I.OLD_NBR AND I.OBSOLETE_DATE IS NULL AND I.vnpk_cspk_code = 'B' AND I.DEPT_NBR  IN (" & DeptNumbers & ") AND BIOH.STORE_NBR IN (" & StoreNumbers & ") GROUP BY 1,2,3,4,5,6,7) L1 GROUP BY 1,2"
'TELL VBA WHICH QUERY TO SEND TO TERADATA 2ND
    cmdSQLData.CommandText = QueryB
'TELL VBA TO LOAD THE QUERY INTO TERADATA
    cmdSQLData.CommandType = adCmdText
'PREVENT QUERY FROM TIMING OUT
    cmdSQLData.CommandTimeout = 0
'TELL TERADATA TO ASSIGN THE QUERY RESULTS TO A RECORDSET
    Set rs = cmdSQLData.Execute()
'=====================================================================================================
'DEFINES WHAT TO DO WITH THE RESULTS OF THE 2ND QUERY
'=====================================================================================================
'RESET THE FIRST DESTINATION ROW IN THE DESTINATION SPREADSHEET
    Row = 5
'SET POINTER TO FIRST RECORD OF RECORDSET (THE RESULTS OF THE SQL QUERY)
    rs.MoveFirst
'THE RECORDSET STRUCTURE CAN BE THOUGHT OF AS A MATRIX p CONTAINING ROWS AND FIELDS THAT BEGIN WITH ARRAY (0,0).
'-----------------------------------------------------------------------------------------------------
'IF THERE ARE RESULTS IN THE RECORDSET, START A LOOP THAT INSERTS THEM IN A SPREADSHEET. KEEP LOOPING UNTIL REACHING THE END OF THE RECORDSET.
        Do While (rs.EOF = False And rs.BOF = False)
'DEFINE HOW MANY ROWS TO RETURN AT ONCE FROM THE RESULTS MATRIX p
        p = rs.GetRows(1)
'A DESTINATION COLUMN MUST BE DEFINED FOR EACH COLUMN RETURNED BY THE SQL QUERY (EACH VARIABLE IN THE SELECT STATEMENT)
'THIS CODE INSERTS RESULTS INTO THE DESTINATION COLUMNS BY ROW
        Output1.Range("G" & Row).Value = p(0, 0) '1ST DESTINATION COLUMN IN DESTINATION SPREADSHEET
        Output1.Range("H" & Row).Value = p(1, 0) '2ND DESTINATION COLUMN IN DESTINATION SPREADSHEET
        Output1.Range("I" & Row).Value = p(2, 0) '3RD DESTINATION COLUMN IN DESTINATION SPREADSHEET
        Output1.Range("J" & Row).Value = p(3, 0) '4TH DESTINATION COLUMN IN DESTINATION SPREADSHEET
'MOVE TO NEXT ROW OF DESTINATION SPREADSHEET
        Row = Row + 1
'LOOP AND CONTINUE RETURNING ROWS TILL END OF RECORDSET
        Loop
'-----------------------------------------------------------------------------------------------------
'CLEAR THE RECORDSET OF THE RESULTS FROM THE 2ND QUERY
    Set rs = Nothing
'CLEAR THE TERADATA INSTRUCTIONS
    Set cmdSQLData = Nothing
'CLOSE CONNECTION WITH TERADATA
    cn.Close
    Set cn = Nothing
End Sub

 

[James_B]

I'm afraid I have absolutely zero experience with this kind of thing myself. But you might try inserting

Set cmdSQLData = New ADODB.Command

before the line where you assign cmdSQLData to QueryB

 

[Dathan Camacho]

I'm afraid I have absolutely zero experience with this kind of thing myself. But you might try inserting

Set cmdSQLData = New ADODB.Command

before the line where you assign cmdSQLData to QueryB

James, that makes 2 of us!

I appreciate any insight, I'll try your suggestion.

If anyone has additional suggestions, please advise. Thank you!

 

[xenou]

I haven't looked closely but the above makes sense - you have destroyed your objects so they would need to be recreated:

'-----------------------------------------------------------------------------------------------------
'CLEAR THE RECORDSET OF THE RESULTS FROM THE 1ST QUERY
    Set rs = Nothing
'CLEAR THE TERADATA INSTRUCTIONS
    Set cmdSQLData = Nothing
'=====================================================================================================