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.