You migrated your data to SQL Server specifically for better performance. Now some queries are slower. This seems like it should be impossible — SQL Server is objectively faster than Access at processing queries. But the problem is not query processing speed. It is how Access communicates with the server.
The Fundamental Issue
In a pure Access database, queries operate directly on a local file. There is no network latency, no translation layer, and no communication protocol overhead. A query that reads 10,000 rows from a local table is a fast disk operation.
After migration, the same query operates through ODBC linked tables. Every data request travels over the network to the server, gets processed, and the results travel back. The server processes the query faster than Access ever could — but the round trip adds latency.
For a single query, this latency is negligible. The problem is that Access often sends many separate queries where a server application would send one.
Problem 1: Access Sends Too Many Queries
When a form opens, Access may send dozens of separate queries to the server:
- One query for the form’s record source
- One query for each combo box and list box to populate its rows
- One query for each subform
- One DLookup call for each calculated control that uses DLookup
- Separate queries for each bound control’s validation rule
With a local Access database, each of these takes microseconds. Through ODBC, each one is a network round trip that takes 5-50 milliseconds. Twenty round trips at 20ms each adds 400ms of latency — noticeable as a perceptible delay when opening the form.
Fix: Reduce the number of individual queries: - Replace multiple DLookup calls with a single query that retrieves all needed values - Pre-load combo box and list box data once at application startup, not every time the form opens - Combine subform queries where possible - Use a single query with all needed fields rather than multiple queries to different tables
Problem 2: Access Downloads Entire Tables
When Access cannot translate a query into something the server can process remotely, it downloads the entire table and processes the query locally. This is called “non-remoted” or “locally processed” query.
Common triggers for local processing:
- VBA functions in queries. A query that uses
IIf(),Nz(), or custom VBA functions in a WHERE clause cannot be processed on the server. Access downloads all rows and filters them locally. - Mixed local and remote tables. A query that joins a local Access table with a linked server table forces Access to download the linked table and perform the join locally.
- Certain string operations. Access-specific string functions may not translate to the server’s SQL dialect.
How to detect this: In SQL Server, run SQL Server
Profiler while performing the slow operation in Access. If you see a
SELECT * FROM tablename with no WHERE clause, Access is
downloading the entire table.
Fix: - Replace VBA functions in queries with equivalent server functions - Move local tables to the server so joins happen remotely - Use pass-through queries for complex operations - Rewrite queries to use only functions the server supports
Problem 3: The N+1 Query Pattern
This is the most common performance killer. It happens with continuous forms and reports that display multiple records.
The pattern: 1. Access runs one query to get the list of records (this is fast) 2. For each record displayed, Access runs additional queries to look up related data
If the form shows 50 records and each record triggers 3 lookup queries, that is 150 extra queries — 150 network round trips — just to display one screen.
This pattern is often hidden in: - Calculated controls using DLookup:
=DLookup("CustomerName", "Customers", "CustomerID=" & [CustomerID])
- Combo box columns that display related values - Conditional formatting
rules that query other tables
Fix: - Join the related tables in the form’s record source query instead of using DLookup - Use a single query that includes all needed columns through JOINs - For combo boxes, use the column’s row source to include the display value in the main query
Problem 4: Missing Server-Side Indexes
Migration tools create primary key indexes on the server, but they may not create secondary indexes. Your Access database may have had indexes that were not transferred, or it may have been relying on full table scans that were fast enough locally but are too slow over ODBC.
How to detect: Check the server’s slow query log or
use the query plan analyzer: - SQL Server: Run the query in SSMS with
“Include Actual Execution Plan” enabled. Look for Table Scan operations.
- MySQL: Use EXPLAIN before your query - PostgreSQL: Use
EXPLAIN ANALYZE before your query
Fix: Create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses. The database’s query plan will tell you exactly which indexes are missing.
Problem 5: Record Locking Overhead
With Access linked tables, every time a user opens a record for editing, Access communicates with the server about locking. With optimistic locking, Access reads the record’s current values and stores them for comparison at save time. With pessimistic locking, Access requests a lock on the server.
This locking communication adds overhead that did not exist with local tables. The overhead is per-record, so it compounds with forms that display many editable records.
Fix: Use optimistic locking (the default). Avoid continuous forms in edit mode for large record sets. Use an unbound editing pattern where the user clicks an Edit button to open a single record for editing.
Problem 6: Pass-Through Queries Would Be Faster
Access’s query translation engine does its best to convert your Access SQL into the server’s SQL dialect and push processing to the server. But it is not always optimal. Access may make suboptimal choices about what to push remotely versus process locally.
A pass-through query sends raw SQL directly to the server with no translation. The server receives exactly the query you write and executes it using its full query optimizer.
When to use pass-through queries: - Complex queries with multiple joins, subqueries, or aggregations - Queries that use server-specific features (CTEs, window functions) - Any query that Access processes slowly through linked tables
How to create one: 1. In Access, create a new query 2. Close the table selection dialog without selecting any tables 3. Go to Design > Pass-Through in the ribbon 4. Set the ODBC Connect Str property to your connection string 5. Write the query in the server’s SQL dialect 6. Set Returns Records = Yes for SELECT queries
Limitation: Pass-through query results are read-only in Access. You cannot edit the results in a form. Use them for reports and data retrieval, not for editable forms.
The Optimization Process
- Identify the slow operations. Ask users which forms, reports, and processes are slower than before.
- Profile the queries. Use server-side monitoring (SQL Server Profiler, MySQL slow query log, PostgreSQL pg_stat_statements) to see what queries Access is actually sending.
- Look for the patterns. Are there too many queries? Full table downloads? Missing indexes?
- Fix the most impactful issues first. A form used 50 times a day by 15 users has more impact than a report run once a month.
- Test and measure. Time the operation before and after each fix to verify improvement.
Most post-migration performance issues are fixable. The server is fast — the bottleneck is almost always in how Access communicates with it, not in the server’s processing speed.