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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
715
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,612
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,507
Messages
5,414,963
Members
403,558
Latest member
Hardeni

This Week's Hot Topics

Top