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.
5. Click Add. In the new dialog box, choose the appropriate driver then click Finish.
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.
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).
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.
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.