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

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,187
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.

Code:
[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.

Code:
    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
 
Last edited:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
712
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.

Code:
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
 
Last edited:

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,171
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,608
Office Version
2013
Platform
Windows
It's possible that qryForwardRecon should also not have non-ADO stuff in it ( a common example is Nz() )
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,217
Messages
5,413,134
Members
403,464
Latest member
TheRepairGuy

This Week's Hot Topics

Top