troubleshooting Question

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

Avatar of mrferrari
mrferrariFlag for United States of America asked on
Microsoft OfficeMicrosoft ExcelSQL
4 Comments1 Solution2883 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
GrahamSkan
Retired
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros