Show All

Create tables from the results of a pass-through query (MDB)

Note  The information in this topic applies only to a Microsoft Access database (.mdb).

1. Create a pass-through query.

   How?

   1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
   2. In the New Query dialog box, click Design View, and then click OK.
   3. Without adding tables or queries, click Close in the Show Table dialog box.
   4. On the Query menu, point to SQL Specific, and then click Pass-Through.
   5. On the toolbar, click Properties to display the query property sheet.
   6. In the query property sheet, set the ODBCConnectStr property to specify information on the database to which you want to connect. You can type the connection information, or click Build , and then enter information about the server you're connecting to.

      When you are prompted to save the password in the connection string, select Yes if you want the password and logon stored in the connection string information.

   7. If the query isn't the type that returns records, set the ReturnsRecords property to No.
   8. In the SQL Pass-Through Query window, type your pass-through query.

      For details on the syntax for your query, see the documentation for the SQL database server to which you're sending the query.

   9. To run the query, click Run on the toolbar. (For a pass-through query that returns records, you can click View on the toolbar, instead.)

      If necessary, Microsoft Access prompts you for information about your SQL server database.

   Notes

   • If you don't specify a connection string in the ODBCConnectStr property, or if you delete an existing string, Access uses the default string "ODBC;". With this setting, Access prompts you for connection information each time you run the query.
   • Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen (-) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on.
2. Create a make-table query based on the pass-through query.

   How?

   1. Create a query, selecting the tables or queries that contain the records you want to put in the new table.

      How?

      1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
      2. In the New Query dialog box, click Design View, and then click OK.
      3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
      4. Double-click the name of each object you want to add to the query, and then click Close.
      5. Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
      6. To view the query's results, click View on the toolbar.
   2. In query Design view, click the arrow next to Query Type on the toolbar, and then click Make Table. The Make Table dialog box appears.
   3. In the Table Name box, enter the name of the table you want to create or replace.
   4. Do one of the following:

      Click Current Database.

      Click Another Database, and then either type the path of the database where you want to put the new table or click Browse to locate the database.

   5. Click OK.
   6. Drag from the field list to the query design grid the fields you want in the new table.
   7. In the Criteria cell for the fields that you've dragged to the grid, type the criteria.
   8. To preview the new table before you create it, click View on the toolbar. To return to query Design view and make changes or run the query, click View on the toolbar again.
   9. To create the new table, click Run on the toolbar.

   Note  The data in the new table you create does not inherit the field properties or the primary key setting from the original table.

3. In the make-table query, include all fields from the pass-through query by dragging the asterisk (*) to the design grid.
4. Run the make-table query.

   How?

   Do one of the following:

   Run a select or crosstab query

   When you open a select or crosstab query, Microsoft Access runs (executes) the query for you and shows the results in Datasheet view.

   1. In the Database window, click Queries under Objects.
   2. Click the query you want to open.
   3. Click Open on the Database window toolbar.

   Run an action query

   Unlike select and crosstab queries, you can't view the results of an action query by opening it in Datasheet view. However, in Datasheet view you can preview the data that will be affected when you run the action query.

   Caution   It's a good idea to make a copy of the data you are changing or moving in an action query, in case you need to restore the data to its original state after running the action query.

   1. Open the action query in Design view.
   2. To preview the records that will be affected in Datasheet view, click View on the toolbar and check the records. For each action query, you will see the following:

      For this query	The datasheet displays
      Update	The fields to be updated.
      Delete	The records to be deleted.
      Make-table	The fields to be included in the new table.
      Append	The records to be added to another table.

   3. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.
   4. Click Run on the toolbar to run the query.

   Note  To stop a query after you start it, press CTRL+BREAK.

When you run the make-table query, you get one table for each result. The first table created has the name defined in the query; subsequent tables have that name concatenated with a sequential number starting at 1.