ODBC Drivers: Recommendations for Working with MS Access

ODBC Drivers: Recommendations for Working with MS Access

When connecting to external ODBC data sources in Microsoft Access, it’s important to understand the performance differences between pass-through queries and linked tables.

Pass-Through Queries

Pass-through queries execute on the server, returning only processed data to Access. This approach offers:
  1. Maximized Performance: Processing happens on the server, reducing load on Access and network traffic.
  2. Efficient Query Handling: No unnecessary local processing or query transformations in Access.

Linked Tables

Linked tables involve working with external data directly in Access. However, they have some drawbacks:
  1. Excessive Data Loading: Access may load more data than needed and perform additional queries, slowing down performance.
  2. Local Processing: Queries might be processed locally, causing inefficiencies.

Recommendations

1. Prefer Pass-Through Queries

For maximum performance, use pass-through queries to execute queries directly on the server.


2. Optimize Linked Tables

If linked tables are necessary, limit data loading by linking to filtered views or using SQL filters to reduce the dataset and improve performance.

By choosing the right method and optimizing data handling, you can improve performance when working with external ODBC data sources in Access.

    • Related Articles

    • Using ODBC Drivers with Power BI Online

      Power BI Online supports working with ODBC drivers, but to ensure proper functionality—especially for scheduled data refresh—you need to configure the setup correctly. Make sure the Power BI Data Gateway is installed on the same computer where the ...

    • Error 193 While Creating ODBC Connection in ODBC Administration

      If you encounter Error 193 while creating an ODBC connection, it's important to note that our ODBC drivers currently do not support Windows ARM. We are actively working on expanding compatibility and will provide updates on the possibility of ...

    • Tableau cannot get quest tables and colums with ODBC Drivers

      Tableau cannot get quest tables and colums with ODBC Drivers In Tablеau, after selecting an ODBC source, to obtain a list of tables available to the driver and work with them, use the following button: Next, load the list of tables by clicking on the ...

    • How to Configure Devart ODBC Drivers for Optimal Performance

      To ensure optimal performance when using Devart ODBC drivers, it's recommended to fine-tune several key parameters: Connection Timeout — Set an appropriate timeout to avoid long waits on unresponsive connections. Data Fetching Size — Adjust the ...

    • What SQL Syntax Can Be Used with Devart ODBC Drivers?

      Devart ODBC Drivers internally rely on the SQLite engine to execute SQL queries. This means that all SQL statements executed through the driver must conform to the SQLite SQL dialect. The SQLite engine supports a wide range of SQL features including: ...