DoCmd.RunSQL is resulting in Run-time error 2342 or Run-time error 3129-CodePudding

In a nutshell:

I'm well along in developing a database but I really need to run a SELECT query from VBA because I will eventually need to run it with variables naming one of the tables.

Here is my code:

SqlStr = "SELECT tblFall2021.fldUserID, tblFall2021.fldDate, tblFall2021.fldTime, tblFall2021.fldUserName, " & _ 
" tblVolunteers.ID, tblVolunteers.fldPhone, tblVolunteers.fldEmail, tblVolunteers.[fldChurch/Parish], " & _
" tblVolunteers.fldGroup, tblVolunteers.[fldCouncil/Court] " & _ 
" FROM tblFall2021 INNER JOIN tblVolunteers ON tblFall2021.[fldUserID] = tblVolunteers.[ID] " & _ 
" WHERE (((tblFall2021.fldDate)='9/22/2021'));" 

' MsgBox SqlStr 
DoCmd.RunSQL SqlStr

Running it as is gets me Run-time error '2342': A RunSQL action requires an argument consisting of an SQL statement.

I tried doubling the " at the front and backends and VBA didn't like that at all; turned it all red, I assume because it is a bad string.

So I tripled the "" and ran it and got:

Run-time error '3129': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The formation of the string shown above exactly matches Microsoft's examples. But everywhere I look for help tells me DoCmd.RunSQL will only run action queries, not select queries, though Microsoft says otherwise:

A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql

and I was previously informed on this very site that DoCmd.RunSQL is ONLY for SELECT queries and won't do action queries.

(I also tried using Chr(34) at the front and back instead of extra quotes, which also yields Run-time error '3129.')

Is there something wrong with the string above? I copied it directly from the SQL view of a query that works and formatted it to match Microsoft's advice.

So I'm entirely out of ideas.

Thanks in advance.

CodePudding user response：

Your SQL string looks correct, though see here to make sure: How to debug dynamic SQL in VBA

But: DoCmd.RunSQL is really only for action queries.

While Microsofts doc has been known to be incorrect at times, here it is not: SELECT...INTO is indeed an action query, it creates a new table.

SELECT ... FROM ... only returns data. What do you want to do with the resulting data?

To process it with VBA, use DB.OpenRecordset.
To show the data to the user, you need a saved query (you can create that on the fly, if you must) that you can open with DoCmd.OpenQuery.

CodePudding user response：

Consider stored, named queries for both action or resultset queries. In MS Access, saved queries are more efficient than SQL statements constructed in code (VBA, Python, Java, etc.) since the Access query engine runs statistics on saved queries and will save best execution plan.

You can even dynamically adjust the SQL of saved, named queries using QueryDefs as demonstrated below which may be your intended goal as stated with

need to run it with variables naming one of the tables

Therefore, consider creating a named query object in Access with below SQL. Notice the use of table aliases to cut down on verbiage and the fix of your date logic as Access does not directly equate a date/time type to date string.

SQL

SELECT t.fldUserID
     , t.fldDate
     , t.fldTime
     , t.fldUserName
     , v.ID
     , v.fldPhone
     , v.fldEmail
     , v.[fldChurch/Parish]
     , v.fldGroup
     , v.[fldCouncil/Court] 
FROM tblFall2021 t
INNER JOIN tblVolunteers v
    ON t.[fldUserID] = v.[ID] 
WHERE t.fldDate = CDate('9/22/2021');

Now, if you need to swap out tblFall2021 for a different table, you only need to do so once due to use of table aliases and can do so with VBA.

VBA

Dim newTable As String
Dim qDef As QueryDef

newTable = "tblSpring2022"

' RETRIEVE SPECIFIC QUERYDEF
Set qdef = CurrentDb.QueryDefs("mySavedNamedQuery")

' ADJUST SQL PROPERTY
qdef.SQL = Replace(qdef.SQL, "tblFall2021", newTable)

' RELEASE QUERY TO SAVE CHANGES
Set qdef = Nothing

Then, use the adjusted query anywhere a query object can be used:

' OPEN ADJUSTED QUERY OBJECT
DoCmd.OpenQuery "mySavedNamedQuery"

' ASSIGN OPEN FORM OR REPORT TO ADJUSTED QUERY
Forms!myForm.Form.RecordSource = "mySavedNamedQuery"
Reports!myReport.Report.RecordSource = "mySavedNamedQuery"

' OPEN A RECORDSET OF QUERY, EVEN USE ABOVE QUERYDEF (BEFORE RELEASE)
Set rst = CurrentDb.OpenRecordset("mySavedNamedQuery")
Set rst = qdef.OpenRecordset()