Acc: You may encounter slow performance or hangs when designing/executing queries that include pass-through queries

Symptoms

In Microsoft Access you may encounter slow performance using pass-through queries as source tables within other queries.

Cause

Microsoft Access does not store the definition for a pass-through query. Therefore, the pass-through query may be executed at various times to retrieve information related to its definition. When the pass-through query takes considerable time to execute, Access appears to hang as the pass-through query is being executed.

Resolution

Depending on where you notice the issue in Access, there are a number of methods available to prevent this issue.

General Changes:

1. Improve the pass-through query performance to reduce the time it takes to execute the query.

2. Instead of using a pass-through query, create a view in SQL Server and link that view to Access as a linked table.

Specific Scenarios:

Designing a query:

1. Make design changes in Access SQL View instead of design view (In Query Design View, you can change the view to SQL View by first opening the query in Design View. Then go to the Results section on the Design tab on the ribbon.)

2. The issue affects saved queries. So you can open your existing query in Access SQL View, copy the SQL syntax, create a new query, paste the SQL Syntax in the SQL View and then change the query to design view. You will be able to design the query without the issue until you save it.

Opening forms or executing queries:

1. Change the recordset type property to Snapshot. (In design view, expose the property sheet from the design tab of the ribbon under the Show/Hide group)

Viewing reports:

1. Change the report to use the pass-through query as its recordsource