RunSQL Macro Action

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

Note   This action will not be allowed if the database is not trusted. For more information about enabling macros, see the links in the See Also section of this article.

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 255 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. If you select No for this argument, the query might 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. You can use the RunSQL action to perform these operations directly from a macro without having to use stored queries.

If you need to type an SQL statement longer than 255 characters, use the RunSQL method of the DoCmd object in a Visual Basic for Applications (VBA) module instead. You can type SQL statements of up to 32,768 characters in VBA.

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 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

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 an Access query, click SQL View on the View menu (on the Access status bar). 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 Access query in the query window.

Applies To: Access 2007, Access 2010 Developer, Access 2013 Developer, Access 2007 Developer, Access 2013, Access 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language