RunSQL Action

Description

You can use the RunSQL action to run a Microsoft Access action query by using the corresponding SQL statement. You can also run a data-definition query.

Setting

The RunSQL action has the following arguments.

Action argument	Description

SQL Statement	The SQL statement for the action query or data-definition query you want to run. The maximum length of this statement is 256 characters. This is a required argument.
Use Transaction	Select Yes to include this query in a transaction. Select No if you don't want to use a transaction. The default is Yes. Previous versions of Microsoft Access have always included the query in a transaction when you ran this action. If you select No for this argument, the query may run faster.

Remarks

You can use action queries to append, delete, and update records and to save a query's result set as a new table. You can use data-definition queries to create, alter, and delete tables, and to create and delete indexes. With the RunSQL action, you can perform these operations directly from a macro without having to use stored queries.

If you need to type an SQL statement longer than 256 characters, use the RunSQL method of the DoCmd object in Visual Basic instead. You can type SQL statements of up to 32,768 characters in Visual Basic.

Microsoft Access queries are actually SQL statements that are created when you design a query by using the design grid in the Query window. The following table shows the Microsoft Access action queries and data-definition queries and their corresponding SQL statements.

Query type	SQL statement

Action
Append	INSERT INTO
Delete	DELETE
Make-table	SELECT...INTO
Update	UPDATE
Data-definition (SQL-specific)
Create a table	CREATE TABLE
Alter a table	ALTER TABLE

(continued)

Delete a table	DROP TABLE
Create an index	CREATE INDEX
Delete an index	DROP INDEX

You can also use an IN clause with these statements to modify data in another database.

Note To run a select query or crosstab query from a macro, use the View argument of the OpenQuery action to open an existing select query or crosstab query in Datasheet view. You can also run existing action queries and SQL-specific queries in the same way.

Tip To see the SQL equivalent of a Microsoft Access query, click SQL View on the View menu in the Query window. You can use the listed SQL statements as models to create queries to run with the RunSQL action. Duplicating an SQL statement in the SQL Statement argument for the RunSQL action has the same effect as running this Microsoft Access query in the Query window.