Create a pass-through query

You can use Access as a front-end application and then link to tables in a back-end server, such as SQL Server. By default, most queries run locally in the Access ACE database engine. In some cases, Access can run a query remotely on SQL Server, depending on the SQL syntax and other factors. For more information, see "JET Database Engine Version 3.0: ODBC Connectivity" in Rediscovered JET and ODBC white papers.

Often though, you want to explicitly run a query on a database server. You do this to help improve performance: database servers have greater processing power than a desktop computer and you can return a smaller subset of data over the network connection. To run a query on a database server, use a pass-through query which is a Transact SQL (TSQL) statement that connects to SQL Server by using an ODBC connection string. For example, you can pass a SELECT statement to return data to a datasheet, form, report or list control, run the equivalent of action queries (Data Manipulation Language (DML) statements), call stored procedures, and perform administrative tasks (Data Definition Language (DDL) statements).

Note    The results of a pass-through query are read-only. To directly edit data in a datasheet or form, create a linked table to an indexed view. For more information, see Create Indexed Views.

1. Connect to a server database.

   For more information about linking to SQL Server, see Import or link to data in an SQL Server database and Link to or import data from an Azure SQL Server Database.

2. Select Create > Query Design.

3. If necessary, close the Add Tables dialog box (Show Table in Access.

4. Select Design >Pass-Through.

   Access hides the query design grid and displays SQL View.

5. If the query property sheet does not appear, press F4 to display it.

6. On the property sheet, click the ODBC Connect Str property box, and then do one of the following:

   Linked Table Manager (Access for Microsoft 365)DSN fileVBA

   To obtain a connection string:

   1. In the navigation pane, right click a table linked to the desired data source and select Linked Table Manager.

   2. In the Linked Table Manager dialog box, select the check box of the data source, and then select Edit.

   3. In the Edit Link dialog box, copy the text in the Connection string box.

   1. Select Build .

   2. Follow the prompts to create a DSN file that contains the connection string information. For detailed steps, see Import or link to data in an SQL Server database and Link to or import data from an Azure SQL Server Database.

   After you create a DSN file connection, the ODBC connection string is internally stored in your Access database. Here’s a trick to obtain a copy of that string:

   1. Locate a linked table name in the Navigation Pane using the ODBC connection that you want.

   2. Press Ctrl+G. to open the VBA Immediate window.

   3. Enter: the following code:

      ?CurrentDb.TableDefs("<table name>").Connect

      But substitute for <table name> the linked table name from step a.

   4. Copy the string returned into the ODBC Connect Str property.

7. Enter your query in SQL View.

   Note: There are important differences between Access SQL and SQL Server TSQL, especially with syntax, function names, and function arguments. For more information, see Comparing Access SQL with SQL Server TSQL.

   Tip    Build the TSQL query in SQL Server Management Studio first, and then copy and paste the SQL into SQL View. This ensures the query has correct syntax. If you don’t have convenient access to an SQL Server Database, consider installing Microsoft SQL Server Express edition on your desktop which supports up to 10 GB and is a free and easier way to run through and check your migration.

8. Consider setting additional related properties:

   • Returns Records     Some SQL statements return records, such as SELECT, and some do not, such as UPDATE. If the query returns records, set this property to Yes; if the query doesn't return records, set this property to No.

     Note    Pass-through queries can return multiple result sets, but in a datasheet, form, report, or recordset variable, only the first result set is used. To retrieve multiple result sets, use a make table query as the following example shows:

     SELECT <pass-through query name>.*
     INTO <local table name>
     FROM < pass-through query name >

     You must use the asterisk (*) because each result set may have different columns. Each result set is saved in a separate table and numbers are appended to the local table names accordingly. For example, if three result sets are returned, the table names would be: <local table name>, <local table name>1, <local table name>2.

   • Log Messages    Specify whether to return messages from the SQL database in an Access messages table. The table name has the format, username – nn, where username is the sign in name for the current user, and nn is a number starting at 00.

   • ODBC Timeout    Specify the number of seconds to wait before a time-out error occurs when a query is run. The default value is 60 seconds. There may be delays due to network traffic or heavy use of the database server.

   • Max Records    Specify the maximum number of records to return. You may have limited system resources, or you want to test your results with just a small amount of data.

9. When you finish entering the query, select Design > Run. Your query is sent to the database server for processing.