Show All

Hide All

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 Database window.) 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.

Setting

The RunSQL action has the following arguments.

Remarks

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

Tip