ms access - SELECT query does not work when converted to VBA - invalid SQL statement

Question

I have been struggling with SQL statement when converted to VBA. Basically, I wish to add something to it before executing (which shouldn't be a problem). However, when I try to run it before changes, VBA does not recognize it as a valid SQL statement. Despite trying to add/remove brackets and any other special character, it still does not work. Please note, that it works perfectly when run as a query. Please see the string below:

SQLstr = "SELECT SourceData.[Fiscal Year], SourceData.[Fiscal Quarter ID], " _
& "SourceData.[Transaction Date], SourceData.[Sales Order Number], SourceData.[Activated?], " _
& "SourceData.[Product ID], SourceData.[Bookings Quantity], SourceData.[Term Length], " _
& "SourceData.[Estimated Expiring Quarter], SourceData.[End Customer Company Name], " _
& "SourceData.[Sold To Company Name] " _
& "FROM SourceData, finalCust, finalPart " _
& "WHERE (((SourceData.[End Customer Company Name]) Like finalCust.[FinalList]) " _
& "And ((SourceData.[Sold To Company Name]) Like finalPart.[FinalList]))"

The code is 'pure' SQL into VBA, without any amendments but I don't want to mislead.

Here's an error message:

Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL statement.

Which I would consider as unreadable SQL statement for VBA.

Answer 1

That error message is misleading. The real problem is that DoCmd.RunSQL is intended for "action" queries: UPDATE; INSERT; DELETE; etc.

It will not accept a plain SELECT query. For example, this simple SELECT query gives me that same "A RunSQL action requires an argument consisting of an SQL statement" message from DoCmd.RunSQL:

Dim SQLstr As String
SQLstr = "SELECT * FROM tblFoo;"
DoCmd.RunSQL SQLstr

However, DoCmd.RunSQL executes this valid UPDATE statement without error:

SQLstr = "UPDATE tblFoo SET long_text='bar' WHERE id=1;"
DoCmd.RunSQL SQLstr

You need a different method to use your SELECT query. And the choice of method depends on what you want to do with the results returned by the query.