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.

You have included three tables, but no relationship (/JOINS) between them. Why? Could possibly be a reason for your error. — PaulFrancis
– PaulFrancis, Commented Dec 11, 2014 at 12:05

HansUp · Accepted Answer · 2014-12-11 14:20:44Z

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.

You will need to use OpenRecordset to run the query and access the results.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related