Avatar of mrferrari
Flag for United States of America

asked on 

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
Set rs = Nothing
Set cn = Nothing

Open in new window

Microsoft OfficeMicrosoft ExcelSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon