You can use the OpenQuery action to open a select (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 (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.) 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.), Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), or Print Preview (print preview: A view of a document as it will appear when you print it.). This action runs an 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.). You can also select a data entry mode for the query.
Note This action is only available in the Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) environment (.mdb or .accdb). See the OpenView, OpenStoredProcedure, or OpenFunction actions if you are using the Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) environment (.adp).
The OpenQuery action has the following arguments.
The name of the query to open. The Query Name box in the Action Arguments section of the Macro Builder pane shows all queries in the current database. This is a required argument.
If you run a macro containing the OpenQuery action in a library database (library database: A collection of procedures and database objects that you can call from any application. In order to use the items in the library, you must first establish a reference from the current database to the library database.), Microsoft Office Access 2007 first looks for the query with this name in the library database, and then in the current database.
||The view in which the query will open. Click Datasheet, Design, Print Preview, PivotTable, or PivotChart in the View box. The default is Datasheet.
||The data entry mode for the query. This applies only to queries opened in Datasheet view. Click Add (the user can add new records (record: A collection of data about a person, a place, an event, or some other item. Records are the logical equivalents of rows in a table.) but can't edit existing records), Edit (the user can edit existing records and add new records), or Read Only (the user can only view records). The default is Edit.
If you use Datasheet for the View argument, Access displays the result set (result set: The set of records that results from running a query or applying a filter.) if the query is a select, crosstab, union (union query: A query that uses the UNION operator to combine the results of two or more select queries.), or pass-through (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of the data being processed by the Access database engine.) query whose ReturnsRecords property is set to Yes; and it runs the query if it is an action, data-definition (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.), or pass-through query whose ReturnsRecords property is set to No.
The OpenQuery action is similar to double-clicking the query in the Navigation Pane, or right-clicking the query in the Navigation Pane and selecting a view. With this action you can select additional options.
Switching to Design view while the query is open removes the Data Mode argument setting for the query. This setting isn't in effect even if the user returns to Datasheet view.
- If you don't want to display the system messages that normally appear when an action query is run (indicating it is an action query and showing how many records will be affected), you can use the SetWarnings action to suppress the display of these messages.
To run the OpenQuery action in a Visual Basic for Applications (VBA) module, use the OpenQuery method of the DoCmd object.