You can use the RunSQL action to run a Microsoft Office Access 2007 action query (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.) by using the corresponding SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.). You can also run a data-definition query (data-definition query: An SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.).
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.
The RunSQL action has the following arguments.
You can use action queries to append, delete, and update records and to save a query's result set (result set: The set of records that results from running a query or applying a filter.) 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 (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) in the Query window (Query window: A window in which you work with queries in Design view, Datasheet view, SQL view, or Print Preview.). The following table shows the Access action queries and data-definition queries and their corresponding SQL statements.
You can also use an IN clause with these statements to modify data in another database.
Note To run a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) or crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.) from a macro, use the View argument of the OpenQuery action to open an existing select query or crosstab query in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). You can also run existing action queries and SQL-specific queries (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.) in the same way.
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.