Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

View products that this article applies to.

Symptoms

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

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2685090
Revision : 6
Created on : 8/20/2020
Published on : 8/21/2020
Exists online : False
Views : 139