EXCEL VBA - SQL String not returning data from Access

Question

I am using Excel to produce a report based on data stored in Access. I ping one specific query to return a full data set based on the value of a cell. I have 3 other modules of the same type getting data from another query which work perfectly. The below gives me a head-ache as it does not return any record. When I go in access and create a dummy query using the same SQL string, Access is returning the records as expected. Anyone can help? Thanks,

    Sub fc_GetPLP()
    Dim Z_Connection As Object
    Dim Z_Recordset As Object
    Dim Z_OnlineOD As String
    Z_OnlineOD = Sheet2.Range("F6").Value

    Dim Z_Access_Path As String
    Dim Z_Access_File As String
    Z_Access_Path = Sheet2.Range("C2").Value
    Z_Access_File = Sheet2.Range("C3").Value

    Set Z_Connection = CreateObject("ADODB.Connection")
    Z_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Z_Access_Path & Z_Access_File

    Set Z_Recordset = CreateObject("ADODB.RECORDSET")
    Z_Recordset.activeconnection = Z_Connection

    Z_SQL = "SELECT Q_SPA_Pricing.* FROM Q_SPA_Pricing WHERE (Q_SPA_Pricing.Qf_FAR_OD='" & Z_OnlineOD & "')"
Debug.Print Z_SQL

    Z_Recordset.Open Z_SQL

Debug.Print Z_Recordset.EOF

    Sheet4.Columns("B:H").Clear
    Sheet4.Range("B2").CopyFromRecordset Z_Recordset

    Z_Recordset.Close
    Z_Connection.Close
    Set Z_Connection = Nothing
End Sub

For example, the SQL I just returned is:

SELECT Q_SPA_Pricing.* FROM Q_SPA_Pricing WHERE (Q_SPA_Pricing.Qf_FAR_OD='PAR-SIN')

Which is as expected, but only in Access. Excel returns an empty Recordset.

Thanks,

Fred

Yes. When I copied this SQL Statement into access I get the expected results. — Frederic Revol

Mukul Varshney Mukul Varshney · Accepted Answer · 2016-10-28T05:32:02

try below code

Sub fc_GetPLP()
    Dim Z_Connection As Object
    Dim Z_Recordset As Object
    Dim Z_OnlineOD As String
    Z_OnlineOD = Sheet2.Range("F6").Value

    Dim Z_Access_Path As String
    Dim Z_Access_File As String
    Z_Access_Path = Sheet2.Range("C2").Value
    Z_Access_File = Sheet2.Range("C3").Value

    Set Z_Connection = CreateObject("ADODB.Connection")
    Z_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Z_Access_Path & Z_Access_File

    Set Z_Recordset = CreateObject("ADODB.RECORDSET")
    Z_Recordset.activeconnection = Z_Connection

    Z_SQL = "SELECT Q_SPA_Pricing.* FROM Q_SPA_Pricing WHERE (Q_SPA_Pricing.Qf_FAR_OD='" & Z_OnlineOD & "')"
Debug.Print Z_SQL

    Z_Recordset.Open Z_SQL

'Debug.Print Z_Recordset.EOF
    If Z_Recordset.EOF = False Then
        Sheet4.Columns("B:H").Clear
        Sheet4.Range("B2").CopyFromRecordset Z_Recordset
    End If    

    Z_Recordset.Close
    Z_Connection.Close
    Set Z_Connection = Nothing
End Sub

EXCEL VBA - SQL String not returning data from Access

1 Answers