After migrating your Access back-end to a server database, you will eventually hear about pass-through queries. They are one of the most powerful tools in a migrated Access application — and one of the most misunderstood.

What Is a Pass-Through Query?

A normal Access query is written in Access SQL (Jet SQL). When you run it against linked tables, Access translates it and sends it to the server. Sometimes the translation is good. Sometimes Access decides it cannot translate the query and pulls entire tables across the network to process locally. That is when things get slow.

A pass-through query bypasses this translation entirely. You write the query in the server’s native SQL dialect (T-SQL for SQL Server, standard SQL for MySQL/PostgreSQL/MariaDB), and Access sends it directly to the server without modification. The server executes it and sends back the results.

Think of it this way: a normal query is like asking a translator to relay your message. A pass-through query is like speaking to the server in its own language.

How to Create One

In the Access query designer:

  1. Create a new query.
  2. Close the “Show Table” dialog without adding any tables.
  3. Go to Query menu > SQL Specific > Pass-Through (or in the ribbon: Design > Pass-Through).
  4. Write your SQL in the server’s dialect.
  5. Open the property sheet and set the ODBC Connect String to your server connection string.

You can also set the “Returns Records” property. Set it to Yes for SELECT queries, No for INSERT/UPDATE/DELETE or stored procedure calls that do not return a result set.

When Pass-Through Queries Help

Complex Queries That Access Translates Poorly

Access’s query engine does its best to translate your queries to SQL the server can execute. But it has limits. Subqueries, complex joins, UNION queries, and anything using server-specific functions often force Access to pull data locally. A pass-through query ensures the entire query runs on the server.

Example: A query that joins five tables with subqueries and aggregate functions might take 45 seconds through a linked table query (because Access is pulling raw data and processing it locally) but 2 seconds as a pass-through (because the server handles everything).

Calling Stored Procedures

Stored procedures are one of the biggest advantages of a server database. They run complex logic on the server, close to the data, and return just the results. You cannot call a stored procedure from a regular Access query. You can from a pass-through query.

EXEC sp_GenerateMonthlyReport @Year = 2026, @Month = 6

Running Server-Specific SQL

Each database has features that Access SQL does not support: common table expressions (CTEs), window functions (ROW_NUMBER, RANK), MERGE statements, temporary tables, and more. Pass-through queries let you use all of them.

WITH MonthlySales AS (
    SELECT CustomerID, SUM(Amount) AS Total,
           ROW_NUMBER() OVER (ORDER BY SUM(Amount) DESC) AS Rank
    FROM Orders
    WHERE OrderDate >= '2026-01-01'
    GROUP BY CustomerID
)
SELECT * FROM MonthlySales WHERE Rank <= 10

There is no way to write this in Access SQL.

Bulk Operations

Need to update 50,000 rows based on criteria? A pass-through UPDATE runs entirely on the server. An equivalent operation through linked tables would pull data across the network, process it locally, and send updates back one by one. The pass-through version might take seconds. The linked table version might take an hour.

Action Queries Against the Server

INSERT, UPDATE, DELETE, and DDL statements (CREATE TABLE, ALTER TABLE) can all be sent as pass-through queries. This is useful for maintenance operations, data cleanup, or any bulk operation.

When Pass-Through Queries Hurt

When You Need Editable Results

Pass-through query results are always read-only in Access. You cannot bind a form to a pass-through query and let users edit records. If users need to edit data, you need either a regular linked table query (which is updatable) or a different approach — like using a pass-through for display and VBA with direct ODBC calls for updates.

When You Need Parameters from Forms

Pass-through queries do not support Access parameter syntax (the [Forms]![FormName]![ControlName] references). The SQL is sent to the server as-is, and the server has no idea what your Access forms contain.

Workaround: Build the SQL string in VBA, substituting form values into the query before executing it.

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qryPassThrough")
qdf.SQL = "SELECT * FROM Customers WHERE State = '" & Me.txtState & "'"

Be careful with SQL injection here. If the parameter comes from user input, validate and sanitize it.

When the Query Is Simple

If Access can translate a simple SELECT query correctly — and for basic queries with filters, joins, and sorting, it usually can — there is no benefit to writing it as a pass-through. You lose the ability to edit results and gain nothing in return.

Pass-Through Queries in VBA

You can create and modify pass-through queries on the fly in VBA. This is the most flexible approach:

Sub RunServerQuery(strSQL As String)
    Dim qdf As QueryDef

    On Error Resume Next
    CurrentDb.QueryDefs.Delete "qryTemp"
    On Error GoTo 0

    Set qdf = CurrentDb.CreateQueryDef("qryTemp")
    qdf.Connect = "ODBC;DRIVER={SQL Server};" & _
                  "SERVER=MyServer;DATABASE=MyDB;Trusted_Connection=Yes;"
    qdf.SQL = strSQL
    qdf.ReturnsRecords = True

    DoCmd.OpenQuery "qryTemp"
End Sub

This pattern lets you build queries dynamically, include form parameters safely, and reuse the same QueryDef name.

Best Practices

  1. Use pass-through for reporting queries. Reports are read-only anyway, so the “not editable” limitation does not matter. Complex report queries almost always run faster as pass-throughs.

  2. Use linked tables for data-entry forms. Users need to edit records. Linked tables provide updatable recordsets that work naturally with Access forms.

  3. Use pass-through for bulk operations. Any INSERT, UPDATE, or DELETE affecting more than a few hundred rows should be a pass-through.

  4. Store your connection string in one place. Hard-coding connection strings in every pass-through query makes maintenance a nightmare. Store it in a table or a VBA constant and apply it programmatically.

  5. Test your SQL in the server’s management tool first. Write and debug your query in SQL Server Management Studio, MySQL Workbench, or pgAdmin. Once it works, paste it into the pass-through query. Debugging SQL syntax errors inside Access is painful.

The Bottom Line

Pass-through queries are the bridge between Access’s familiar front-end and the full power of a server database. Use them for complex queries, stored procedures, bulk operations, and anything where Access’s query translation falls short. Use linked tables for simple queries and data-entry forms. Knowing when to use each one is what separates a migrated application that performs well from one that feels slower than the original.