Access VBA DoCmd.RunSQL Method and Error 2342

This tutorial explains how to use Access VBA DoCmd.RunSQL Method command and cause of Error 2342 (due to using non-action query such as Select statement)

Access VBA DoCmd.RunSQL Method and Error 2342

DoCmd.RunSQL Method runs specified SQL statement. The SQL statement can only be action query (such as create table, modifying data), but not Select statement.

Syntax of DoCmd.RunSQL Method

DoCmd.RunSQL(SQLStatement, UseTransaction)

SQLStatement	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.
UseTransaction	Use True (–1) to include this query in a transaction. Use False (0) if you don’t want to use a transaction. If you leave this argument blank, the default (True) is assumed.

Example of DoCmd.RunSQL Method

The below example updates Employee ID from 001 to 010

Public Sub updateSQL()
    DoCmd.SetWarnings False
    strSQL = "UPDATE [Schedule_Table] SET [Empl ID] = '010' WHERE [Empl ID]='001'"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

DoCmd.SetWarnings False Method is used to disable the warning message.

Cause of Error 2342 using DoCmd.RunSQL Method

The below Select statement will cause Run-time error ‘2342’, because DoCmd.RunSQL Method only supports action query.

Public Sub selectSQL()
    strSQL = "Select * FROM [Schedule_Table] WHERE [Empl ID]='001'"
    DoCmd.RunSQL strSQL
End Sub

The error message “Run-time error ‘2342’: A RunSQL action requires an argument consisting of SQL statement” is very confusing, it fails to point out the error is caused by using non-action query.

Solution of Error 2342 to select query

The below code creates a Query called “tempQry” using DoCmd.DeleteObject Method and then open it using DoCmd.OpenQuery Method.

Just in case “tempQry” already exists in database, the code highlighted in green deletes tempQry if it exists.

Public Sub selectSQL()
    Dim qdf As QueryDef
    strSQL = "Select * FROM [Schedule_Table] WHERE [Empl ID]='001'"
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "tempQry"
    On Error GoTo 0
    Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
    DoCmd.OpenQuery ("tempQry")
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff194626.aspx?f=255&MSPPError=-2147217396

 

Access VBA DoCmd.RunSQL Method and Error 2342