How to Create SQL Pass-Through Queries in MS Access Using CData

Microsoft Access is a popular database management system that enables users to store, manage, and analyze structured data efficiently. While Access provides built-in query capabilities, complex queries on large datasets and external databases can be slow. Using SQL pass-through queries with CData allows direct SQL execution on external databases, improving performance and flexibility.

CData Connectors augment Microsoft Access with advanced schema retrieval, including the sys_tablecolumns system table, which provides column names, data types, and metadata from external databases. This helps optimizing queries and improve data handling.

In this guide, we'll cover creating SQL pass-through queries in MS Access using CData's ODBC Connector. We'll use the CData ODBC Driver for Salesforce for demonstration, but the methods and principles apply to all 270+ CData connectors.

Let's begin!

Prerequisites

1. CData ODBC Driver: We'll use the Salesforce version, but this tutorial works with all CData drivers. Download here.
2. Microsoft Access: Ensure you have Microsoft 365 installed on your system, which includes Access. If you don't have it, you can download a trial of Microsoft 365 from here.

Getting Started

Overview

Here's an overview of the steps:

• Configure: Set up the DSN for the installed CData ODBC Driver on your system.
• Query: Create and execute a pass-through SQL query in Access Query Design using the sys_tablecolumns system table to retrieve column details.
• Save: Save the created query for future reuse and efficient data retrieval.

STEP 1: Configure the DSN for CData ODBC Salesforce Driver

1. Launch ODBC Data Source Administrator to configure the DSN (Data Source Name) for the CData ODBC Driver for Salesforce:
• On Windows: Search for "ODBC Data Source Administrator" in the Start menu, open the application, and select "CData ODBC Driver for Salesforce."
• On Mac: Open "Applications," go to "Utilities," open "ODBC Manager," and select "CData ODBC Driver for Salesforce."
• On Linux: Use the command line to launch ODBC Data Source Administrator or use UnixODBC if installed, then select "CData ODBC Driver for Salesforce."

2. Click on the Add button to create a new DSN.
3. Select CData ODBC Driver for Salesforce from the list and click Finish.
4. In the DSN configuration window, select the Advance tab and enter the required authentication details:
   • Authentication: Choose a method — Login (username, password, and security token), OAuth (no credentials required), or SSO (configured via SSOProperties and TokenUrl).
   • Server URL: Enter the Salesforce login instance URL.
   • Other settings: Configure additional options based on your Salesforce environment.
5. Click Test Connection to verify the connection.
6. If the test is successful, click OK to save the DSN.

STEP 2: Create and Save a Pass-through Query in Access Query Design

1. Launch MS Access, then go to the Create tab and select Query Design.
2. In the Query Design window, click on the Pass-Through button.
3. When the SQL statement input field appears, enter the following SQL statement:

SELECT * FROM sys_tablecolumns WHERE TableName='TableName';

4. This query will return all columns from the system table that match the specified table name.
5. Click Run on the top-left of the Query Design window.
6. When the "Select Data Source" dialog appears, go to the Computer Data Sources tab and select the data source configured with your installed CData driver (e.g., CData Salesforce Source), then click OK.

7. The execution result of the SQL statement will be displayed.

8. Save the query by clicking the Save button on the top-left or by going to File > Save. Enter a name for your query and click OK.

STEP 3: Save and Reuse the Query

After creating the pass-through query, you can reuse it for future data retrieval tasks. Follow these steps:

1. Your saved query will appear under the All Access Objects pane on the left. Right-click your query (eg. Passthrough-Query ) and select Design View.

2. Once in Query Design mode, click on Property Sheet.
3. In the Property Sheet (displayed on the right side), find and click ODBC Connection String.
4. Click the ... button (three dots) next to the ODBC Connection String field.
5. When the "Select Data Source" dialog appears again, select the same data source as in step 6 and click OK.
6. After a few seconds, the "Connection String Builder" dialog appears, asking if you want to save the password in the connection string. Click Yes to include it or No to exclude it.

7. To execute the query, double-click the saved query name in the All Access Objects pane on the left. The results will be displayed.

You can now reuse this pass-through query anytime to execute arbitrary SQL statements directly against an external database.

Enhance MS Access with CData ODBC Drivers

Unlock the full potential of Microsoft Access with CData's ODBC, JDBC, and API drivers, enabling seamless SQL pass-through queries across 270+ data sources, including SaaS platforms, ERP suites, big data, RDBMS, and NoSQL. Execute SQL directly on external databases, eliminate bottlenecks, and optimize data access with industry-leading connectivity.

Get Started with a Free Trial

Try the CData ODBC drivers for free and optimize your queries today. Download a free trial.