Why does an ms-access sql Pass Through not work in VBA

Question

I am currently trying to write a select pass through query using VBA in Access 2016. If I use the manual option via the button Pass-Through and assign manually the dsn the following statement works.

SELECT top 1 dat_Kunden.Kunden_Status FROM dat_Kunden

The sql I want to pass through is changing so I want to create a VBA Function to execute it.

This is my current Function to execute a given sql statement

Function CreateSPT(strSQL As String)

Dim qdf As DAO.QueryDef, rs As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;SERVER=xxx;DATABASE=yyy;UID=zzz"    'in the code this is the real data
qdf.SQL = strSQL
qdf.ReturnsRecords = True

Set rs = qdf.OpenRecordset()
If Not (rs.BOF And rs.EOF) Then rs.MoveFirst
    Do Until rs.EOF
        Debug.Print rs.Fields(0)
        rs.MoveNext
    Loop


rs.Close
Set rs = Nothing
Set qdf = Nothing

End Function

This does work.

Sub test_sql()
SQL = "SELECT CONVERT( date, GETDATE() ) AS qryTest"
CreateSPT (SQL)
End Sub

This statement which works via the manual pass through does not work

Sub test_sql2()
SQL =  "SELECT top 1 dat_Kunden.Kunden_Status FROM dat_Kunden  AS qryTest"
CreateSPT (SQL)
End Sub

The Error code is Run-time error '3146': ODBC -- call failed at this line:

Set rs = qdf.OpenRecordset()

I hope you have an idea where my mistake is... Thanks to all of you, learned a lot from you!

Answer 1

If you provide an alias, use it:

SQL = "SELECT Top 1 qryTest.Kunden_Status FROM dat_Kunden AS qryTest"

or ignore it:

SQL = "SELECT Top 1 Kunden_Status FROM dat_Kunden AS qryTest"