October 2015 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Microsoft Access beginners are often confused about the difference between the RunSQL action in a Macro and the 'DoCmd.RunSQL' method in Visual Basic for Applications (VBA). Regardless of where you use it—Macro or VBA—you must provide the SQL statement of an Action Query or a Data Definition Query. These are the only types of queries supported.

If you’re unsure which queries fall under these categories, refer to the following list:

Action Query Type.

**Action Query Types**
Query Type	Statement
Append	INSERT INTO
Delete	DELETE
Make-Table	SELECT ... INTO
Update	UPDATE

Data Definition Queries.

**Data-Definition Query Types**
Query Type	Statement
Create a table	CREATE TABLE
Alter a table	ALTER TABLE
Delete a table	DROP TABLE
Create an Index	CREATE INDEX
Delete an Index	DROP INDEX

Using the SQL Statement of any other Query type in the RUNSQL Action will end up in errors. In Macro, the length of an SQL statement can be a maximum of 256 characters or fewer.

The 'DoCmd.RunSql' method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as a parameter to this command. But, in VBA, you can load the SQL Statement of a predefined query into a String Variable and use it as the parameter to the DoCmd.RUNSQL command.

Example-1:

Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

The RunSQL action in a Macro allows you to modify or delete information across multiple records in one step. Before executing an Action Query, Microsoft Access displays a warning message appropriate to the action (such as update or delete) and waits for the user’s response to either proceed or cancel.

Once you have thoroughly tested and perfected the Action Query, you can instruct Microsoft Access to temporarily suppress warning messages during the execution of the RunSQL action. After the query completes, you should re-enable the warning messages so that Access can continue to alert you about unexpected errors or issues as they occur.

The SetWarnings action in a Macro (or the DoCmd.SetWarnings method in VBA) is used to control system warning messages. This is particularly useful when data processing for a report involves one or more Action Query steps within a Macro.

In the Macro design example shown below, the first action is SetWarnings, with its parameter set to No, which temporarily turns off warning messages while the RunSQL action executes in the next step. The third action is another SetWarnings, this time with its parameter set to Yes, which re-enables system warnings. This ensures that Microsoft Access can once again handle and report any unexpected errors as they occur.

Right-click to open Large Image in New Tab/Window.

Example-2:

Create a Table with the Data-definition SQL

Warning: Double-check that you are not using any existing table name for this sample run.

Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open it in any of the following Views: