Fix Microsoft Access Pass-Through Query Connection Prompts for SQL Server
Question details
The user needs to use Microsoft Access as a front end for a SQL Server database without repeatedly entering connection details for pass-through queries.

- Product
- Microsoft Access and Microsoft SQL Server
- Device & OS
- not provided
- Scenario
- Migrating an Access application's data to SQL Server while retaining Access as the front end and using pass-through queries.
- Observed behavior
- Pass-through queries repeatedly request connection details because the connection string does not contain sufficient information.
Confirm the SQL Server name or instance, target database, authentication method, and installed SQL Server ODBC driver. If the connection uses an ODBC data source name (DSN), verify that it is available to the installed Access version and points to the correct server and database.
Complete the ODBC connection string in the Access pass-through query
Use this solution when a pass-through query repeatedly asks for connection information because its saved connection string is incomplete.
An Access pass-through query sends SQL directly to SQL Server using its own ODBC connection information. Do not assume that a working linked-table connection automatically supplies the connection details for every pass-through query.
In the Access Navigation Pane, right-click the affected pass-through query and select Design View. If Access displays SQL View for the pass-through query, keep that view open.
On the Query Design tab, click Property Sheet, or press F4. Display the query properties and locate ODBC Connect Str.
Set ODBC Connect Str to include the installed driver, server or instance, database, and authentication information. For Windows authentication, an example is: ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=your-server;DATABASE=your-database;Trusted_Connection=Yes;Encrypt=Yes;TrustServerCertificate=No;. Replace the example values with your environment's settings and use the exact name of an installed driver.
If you use Windows authentication, confirm that your Windows account has access to the target database. If you use SQL Server authentication or a DSN, use the ODBC Connect Str property's builder button, where available, to select the configured data source and enter the required connection details. Avoid embedding database passwords in a shared Access file.
Save the query, then click Run on the Query Design tab. Confirm that it connects to the intended database without asking again for server or database details. Repeat this check for other affected pass-through queries; if prompts remain, inspect the selected DSN, authentication settings, and SQL Server permissions.

Use WPS Office to Review and Share Exported SQL Server Reports
WPS Office is a free, lightweight alternative for working with spreadsheet reports exported from Access or SQL Server. Its familiar interface and Microsoft Office format compatibility make it easy to move reporting documents into WPS, but it does not replace the Access database front end or fix ODBC pass-through connections.

Frequently Asked Questions
Where do I change the connection string for an Access pass-through query?
Right-click the query in the Access Navigation Pane and select Design View. Open Property Sheet from the Query Design tab or press F4, then edit the query's ODBC Connect Str property.
Why does a pass-through query ask for connection details when linked tables already work?
A pass-through query has its own ODBC Connect Str property. A working linked-table connection does not guarantee that this property is complete. Check the affected query's server, database, driver or DSN, and authentication settings.
Will adding the server and database always remove login prompts?
It resolves prompts caused by missing server and database details, but login prompts can still occur if authentication information is missing or invalid. Check the authentication method in ODBC Connect Str and confirm that the connecting account has permission to access the database.
Can WPS Office replace Access as the SQL Server front end?
WPS Office is not a replacement for an Access application with database forms, linked tables, and pass-through queries. For reporting tasks, export query results from Access to an Excel-compatible file, then open that file in WPS Spreadsheets.




