SQL query works in MS Access but doesnt when called from excel VBA

[vds1]

Hi Gurus -

Thank you for looking into my problem.

Below query works when i execute in MS Access. Its a standard query which i plan to call in Excel using ADO.

[B]select * from qryForwardRecon;[/B]

When the above query is called using ADO in Excel VBA , i get zero records. Not sure what could be the issue. Please see the code i am using.

    DB1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\db.mdb;Persist security info=false;"
    
    '// Get Data from queries
    Sheets("RECON_FORWARD").Select
    Cells.Clear
    StrSQL = "select * from qryForwardRecon;"
    
    Cn.Open DB1
    Rs.Open StrSQL, Cn, adOpenKeyset, adLockBatchOptimistic
    
    '// Get field Names
    For i = 0 To Rs.Fields.Count - 1
        Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next
        
    '//Get Data
    Range("A2").CopyFromRecordset Rs

Reference used : Microsoft Active X Data Object 2.1 Library

 

[welshgasman]

Do you have On Error Resume Next at all?
As when I try your code it falls over first with no variables defined (I use option explcicit)

Then with the connection.?

From https://stackoverflow.com/questions...-ms-access-table-from-ms-excel-2010-using-vba

Note the reference to 64bit?

This works, I tested it, but I am on 32bit windows.

Public Sub foo()
    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\Paul\Documents\Bibbys.mdb"
    strSql = "SELECT Count(*) FROM qryDates;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.Fields(0) & " rows in MyTable"
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

 

[james_lankford]

Option Explicit


Sub doit()
    
On Error Resume Next
    
    Dim Cn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim DB1 As String
    Dim StrSQL As String
    Dim i As Integer
    
    ' https://www.connectionstrings.com/access-2013/
    DB1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\db.mdb;Persist Security Info=False;"
    
    '// Get Data from queries
    Sheets("RECON_FORWARD").Select
    Cells.Clear
        
    StrSQL = "select * from qryForwardRecon;"
        
    Set Cn = New ADODB.Connection
    
    Cn.Open DB1
    If Err.Number <> 0 Then
        Debug.Print Err.Number & " : " & Err.Description
        Err.Clear
        GoTo exit_sub
    End If
    
    Set Rs = New ADODB.Recordset
    
    Rs.Open StrSQL, Cn, adOpenKeyset, adLockBatchOptimistic
    If Err.Number <> 0 Then
        Debug.Print Err.Number & " : " & Err.Description
        Err.Clear
        GoTo exit_sub
    End If
    
    '// Get field Names
    For i = 0 To Rs.Fields.Count - 1
        Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next
        
    '//Get Data
    Range("A2").CopyFromRecordset Rs
    
exit_sub:
    If Not Rs Is Nothing Then
        If (Rs.State And adStateOpen) = adStateOpen Then
            Rs.Close
        End If
        Set Rs = Nothing
    End If
        
    If Not Cn Is Nothing Then
        If (Cn.State And adStateOpen) = adStateOpen Then
            Cn.Close
        End If
        Set Cn = Nothing
    End If
    
End Sub

 

[xenou]

It's possible that qryForwardRecon should also not have non-ADO stuff in it ( a common example is Nz() )