Ms Access Pass Through Query to ODBC or SQL Server Connect

Microsoft Access Pass Through Query is the method that allows you to execute SQL statement directly using external database such as SQL Server. Before we get started with Pass Through Query, first you need to set up the DSN from ODBC data source:
1. Go to Start > Control Panel.
2. In the Search Box, input Administrative Tools. Click to open it.
3. In Administrative Tools dialog box, open the ODBC data source.
4. In ODBC data source dialog box, go to System DSN tab.

Ms Access Pass Through Query-1

Ms Access Pass Through Query-1

5. Click Add. In the new dialog box, choose the appropriate driver then click Finish.

Ms Access Pass Through Query-2

Ms Access Pass Through Query-2

6. A new dialog box will appear. Provide the appropriate information for the Data Source.

NOTE: If you already has the System DSN, you can select it or configure the DSN first.
The setup for ODBC data source is done. Now, follow the Pass-Through Query instructions:
1. Create a query by go to Create tab > Query Design, not Query Wizard.
2. When the dialog box for showing table is appeared, close it without adding any tables or queries.
3. Go to Design tab > Pass Through to activate it. The query window will be blank like query SQL view.

Ms Access Pass Through Query-3

Ms Access Pass Through Query-3

4. Click the Property Sheet. The properties box will appear to the right side.
5. In the Property Box, search for the row called ODBC Connect Str. Then click the builder (the icon with 3 dots).

Ms Access Pass Through Query-4

Ms Access Pass Through Query-4

6. In the Select Data Source dialog box, navigate to Machine Data Source tab. Select the ODBC Data Source that has been created earlier. Then, click OK.
7. Now, input your SQL statement and click run. If it done right, the tables and records will appear.

Read Also:  MS Access Case Statement Syntax in Multiple Conditions

NOTE: You can create the DSN via the New button in Machine Data Source tab. The steps is almost same, only without using Control Panel search and early dialog box to determine creating User Data Source or System Data Source. Make sure to use the Pass-Through Query not for linking or importing other Access Database, or it will cause errors. The Pass-Through Query is for SQL or Oracle database.



Access Database Tags: #access 2016 pass through query #access pass through query execute stored procedure #ms access 2013 pass through query #ms access pass through query odbc connect string #ms access pass through query to sql server #ms access pass through query vba #ms access pass through query with parameters

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top