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.
manual pass through

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!

Why does the image have 000402 in field name table prefix but SQL statements in your narrative do not? And the VBA constructed string does not have it anywhere. — June7
– June7, Commented Jan 11, 2021 at 10:12
thats an misleading error I overlooked. Both tables with and without the numbers exist and are have the same structure. I will edit it. Thanks! — timo_vdw
– timo_vdw, Commented Jan 11, 2021 at 10:24

Gustav · Accepted Answer · 2021-01-11 10:43:55Z

3

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"

answered Jan 11, 2021 at 10:43

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

timo_vdw Over a year ago

both did not work but omitting the AS qryTest works. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related