Microsoft Access Linked Table To Sql Server Express via ODBC 17 or ODBC 18 Driver

ODBC Driver 17 and 18 introduce additional security and feature behaviors compared to ODBC 11, and those can change how fast an Access front end feels, especially over LAN connections and with many small round‑trips.

From the provided information, the following points are relevant:

1. Use current SQL Server ODBC drivers for new development
   Documentation recommends avoiding older, built‑in drivers (like SQL Server Native Client / older WDAC drivers) for new development and using the newer, individually shipped ODBC drivers instead. These newer drivers (17/18) add support for features such as Always Encrypted, Azure AD authentication, UTF‑8 collations, and improved TLS handling, which can change connection behavior and defaults.
2. Connection string differences and encryption
   For ODBC Driver 18, the quick‑start examples show that Encrypt is expected and typically on for modern connections:

Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=myDB;Trusted_Connection=yes;Encrypt=optional;

or

Driver={ODBC Driver 18 for SQL Server};Server=myserver.database.windows.net;Database=myDB;UID=myUser;PWD=<password>;Encrypt=yes;

In the question, the Driver 18 string is:

ODBC;Driver={ODBC Driver 18 for SQL Server};Server=192.168.00.000\csdb;Database=XXX;Database=CIS;Trusted_Connection=yes;Encrypt=no;TrustServerCertificate=yes;

Key observations:

• Database appears twice (Database=XXX;Database=CIS;). Only one value will actually be used; the last one typically wins. This can cause confusion about which database is really being hit and may affect query plans or schema resolution if not intended.
• With Driver 18, encryption and certificate validation can introduce extra negotiation on each connection if not configured consistently. Even with Encrypt=no;TrustServerCertificate=yes;, the driver’s default security behavior differs from older drivers, and any mismatch with server TLS configuration or group policy can add overhead.

3. Driver 17/18 are optimized for newer SQL Server features, not necessarily for Access‑style chattiness
   The newer drivers are designed to support features like Always Encrypted and Azure AD authentication. For example, Always Encrypted is enabled via:

Driver={ODBC Driver 18 for SQL Server};Server={myServer};Encrypt=yes;Trusted_Connection=yes;ColumnEncryption=Enabled;

When Always Encrypted or related metadata features are in play, the driver must perform extra metadata lookups and key operations. Even if Always Encrypted is not explicitly enabled, the newer driver’s metadata handling and TLS behavior can add latency on connections that open and close frequently, which is common in Access front ends with many bound forms and queries.

4. Recommended direction
   Given the context:

• Use the latest ODBC driver (17 or 18) rather than 11 for long‑term support and compatibility, as recommended in the migration and linking guidance for Access:
  • “Consider installing the latest version of the SQL Server OLE DB and ODBC drivers instead of using the native SQL Server drivers that ship with Windows. Not only are the newer drivers faster, but they support new features in Azure SQL that the previous drivers don't.”
• Ensure the connection string is clean and minimal for on‑premises SQL Server Express with Windows authentication, for example (pattern based on the quick‑start examples):

    ODBC;Driver={ODBC Driver 18 for SQL Server};Server=192.168.00.000\csdb;Database=XXX;Trusted_Connection=yes;Encrypt=optional;
  

  or with Driver 17:

    ODBC;Driver={ODBC Driver 17 for SQL Server};Server=192.168.00.000\csdb;Database=XXX;Trusted_Connection=yes;
  

• Avoid duplicate keywords (only one Database=), and use a consistent Encrypt setting that matches the SQL Server’s TLS configuration and network policies to prevent repeated negotiation overhead.
• If Always Encrypted or Azure AD features are not used, keep ColumnEncryption and Azure AD–related options out of the connection string to avoid unnecessary work.

5. Access‑specific considerations
   The Access migration and linking guidance emphasizes:

• Installing the latest ODBC driver on every client machine that uses the Access front end.
• Using Linked Table Manager to refresh/relink tables after changing drivers so that all linked tables use the same DSN/driver configuration.

If some linked tables still point to the old driver or to a DSN with different options, Access may be opening multiple connections with different behaviors, which can manifest as latency.

In summary, ODBC 17/18 are the recommended drivers and can be faster overall, but for an Access front end the effective performance depends heavily on:

• A clean, consistent connection string (no duplicate Database entries).
• Matching encryption/TLS settings between driver and server.
• Ensuring all linked tables use the same updated driver and DSN configuration.

Adjusting these aspects is the supported way to align the newer drivers’ behavior with the low‑latency experience previously seen with ODBC 11.

References:

• Connect Access to SQL Server
• Migrate an Access database to SQL Server
• ODBC Programmer's Reference
• Using Always Encrypted with the ODBC Driver for SQL Server
• ODBC driver installation check
• Summary of ODBC driver versions

I apologize for the delayed response. What I discovered was not anything I was looking for in the beginning and it boiled down to a few things. The first item was my ODBC DSN Less connection string was configured using the IP address of the machine hosting the Sql server. I found using the Fully Qualified Domain Name (FQDN) and port number was far more efficient. At the same time I discovered the authentication from the host to the server was authenticating with a legacy Microsoft authentication protocol called NT LAN Manager (NTLM) and not Kerberos.

This was due to not having a Service Principle Name (SPN) registered in the Active Directory; therefore, I had to create a domain service account for my Sql Server Services and get away from the default local service account.

In short, I'm certain those were my main reasons for latency. I've seen a noticeable difference in speed of connection.