Am trying to create tables in MS Access that are linked to SQL Server 2022. My current version of Access requires a 64bit DSN to establish these persistent links (I have a 365 subscription so it is up-to-date). I installed the latest ODBC driver from Microsoft (ODBC Driver 18 for SQL Server).
I used the WinAdmin ODBC 32bit tool and created a 32bit System DSN which connected just fine. I used the WinAdmin ODBC 64bit tool and to create a 64bit System DSN which fails to connect. The configuration values for both DSNs are identical (used a compare tool to guarantee identical configurations).
Using MS Access VBA code, I can connect to the SQL Server using ADO components (Provider=MSOLEDBSQL) but cannot connect using DAO components (Provider=SQLODBC; which is expected since MS Access is looking for a 64bit ODBC driver which is currently not functional).
I tried repairing the ODBC driver installation > no luck; I uninstalled and reinstalled > no luck.
I am aware of the new options/behaviors of the version 18 ODBC driver and have tried multiple combinations of "Encryption/Certificate" values.
Here are the configuration options which work for 32bit, but not for 64bit:
Microsoft ODBC Driver for SQL Server Version 18.03.0001
Data Source Name: TSQL_Training_64
Data Source Description: Training Database
Server: DBS00
Use Integrated Security: Yes
Database: TRN01
Language: (Default)
Data Encryption: Optional
Trust Server Certificate: Yes
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Here are the results from the failed connection test:
Microsoft ODBC Driver for SQL Server Version 18.03.0001
Running connectivity tests...
Attempting connection [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.
[Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to DBS00. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
TESTS FAILED!
This error message is not very helpful (to me anyway): except for the DSN Name, the configuration values are identical for both 32bit (which succeeds) and 64bit (which fails).