Create a pass-through query

Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010.

With Access, you can utilise this as a front-end application and go on to link to tables in a back-end server, like SQL Server. Automatically, many queries operate locally in the Access ACE database engine. For some situations, Access can perform a query independently on SQL Server, based upon the SQL syntax and other factors. For further information, refer to “JET Database Engine Version 3.0: ODBC Connectivity” in Rediscovered JET and ODBC white papers.

Although more commonly, you want to clearly run a query on a database server. You conduct this with the intention to aid enhance performance: database servers possess deeper processing capabilities than a desktop computer and you can gather a smaller subset of data from the network connection. To perform a query on a database server, employ a pass-through query which is a Transact SQL (TSQL) statement that links to SQL Server by applying an ODBC connection string. For example, you can pass a SELECT statement to capture data to a datasheet, form, report or list control, operate the comparative form of action queries (Data Manipulation Language (DML) statements), call stored procedures, and conduct administrative tasks (Data Definition Language (DDL) statements).

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

  1. Connect to a server database. For further details 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. Choose Create > Query Design.
  3. Exit the Show Table dialogue box.
  4. Click Design >Pass-Through. Access conceals the query design grid and presents SQL View.
  5. If the query property sheet is hidden, press F4 to illustrate it.
  6. On the property sheet, select the ODBC Connect Str property box, and then perform one of the following actions:

Linked Table Manager (Access for Microsoft 365)

To obtain a connection string:

a. In the navigation pane, right-click a table associated with the preferred data source, and press Linked Table Manager.

b. In the Linked Table Manager dialogue box, choose the checkbox of the data source, and then pick Edit.

c. In the Edit Link dialogue box, duplicate the text in the Connection string box.

DSN file

a. Click Build Builder button .

b. Follow the prompts to make a DSN file that has the connection string information. For in-depth steps, see Import or link to data in an SQL Server database and Link to or import data from an Azure SQL Server Database.

VBA

a. When you produce a DSN file connection, the ODBC connection string is inwardly retained in your Access database. Here’s a trick to acquire a copy of that string:

b. Find a linked table name in the Navigation Pane applying your preferred ODBC connection.

c. Press Ctrl+G. to access the VBA Immediate window.

Type: the following code:

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

But replace the linked table name from step a with <table name>.

d. Replicate the string returned into the ODBC Connect Str property.

  1. Input your query in SQL View.

Note:   There are vital variations between Access SQL and SQL Server TSQL, particularly with syntax, function names, and function arguments. For more information, see Comparing Access SQL with SQL Server TSQL.

Tip: Develop the TSQL query in SQL Server Management Studio first, and then copy and paste the SQL into SQL View. This guarantees that the query has correct syntax. If you have no reliable access to an SQL Server Database, try installing Microsoft SQL Server Express edition on your desktop which supports up to 10 GB and is a free and easier way to perform and monitor your migration.

  1. Be open to formatting extra related properties:
    • Returns Records – Some SQL statements harvest records, like SELECT, and some do not, such as UPDATE. If the query collects records, define this property as Yes; if the query returns no records, set this property to No.

  1. Note  –  Pass-through queries can gather several result sets, but in a datasheet, form, report, or recordset variable, solely the first result set is applied. To regain numerousultiple result sets, use a make table query as the following example reflects:

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

It is crucial that you always use the asterisk (*) since every result set could contain various columns. Each result set is saved in a standalone table and numbers are imported to the local table names pertinently. 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  – Determine whether to return messages from the SQL database in an Access messages table. The table name includes the format, username – nn, where username represents the sign-in name for the existing user, and nn is a number beginning at 00.
  • ODBC Timeout  – Set the amount of seconds to wait before a time-out error happens once a query is run. The preset value is 60 seconds. There could be delays because of network traffic or heavy use of the database server.
  • Max Records – Define the maximum number of records to return. You might have restrained system resources or you seek to experiment your results with only a small amount of data.
  1. Once you’re done stating the query, choose Design > Run. Your query is delivered to the database server for processing.

See Also

Transact-SQL Reference

Querying with Transact-SQL

Leave a Reply

%d bloggers like this: