Solved: Writing VBA code to run SQL query with ADODB connection and getting Run-time error 3709

I'm a little new to running queries via Excel and am getting the above error but can't figure out why. I'm trying to run an Access query via Excel VBA, the query runs ok in Access.

Thanks so much.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rngTarget As Range
Dim intColIndex As Integer
Dim j As Integer
    
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBName & ";"
 
Set rs = New ADODB.Recordset
Set rngTarget = Worksheets("InputData").Cells(2, 1)
    
For j = LBound(RegVars) To UBound(RegVars)
        
    If Not IsNull(RegVars(j)) Then
            
        rs.Open "Select [Date], Mid from tbl_IndexNumber, " _
            & "(Select Top 1 ID from tbl_ID " _
            & "where StartDate < #11/22/2007# and Description LIKE 'XXX*' " _
            & "order by StartDate desc) as T3 " _
            & "where tbl_Number.ID = T3.ID " _
            & "and [Date] < #" & StartImport & "# and [Date] > #4/14/2008# " _
            & "order by [Date];"
        
    End If
        
    TgtRange.Offset(0, j).CopyFromRecordset rs ' the recordset data
        
Next j
 
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Open in new window