As a database developper, I've created many Access databases over the last 15 years. In almost every database I created, I use vba to (re)link tables that are located in a SQL Server database. My code looks like this:
First I check if the table already exists. If so, I delete the table:
CurrentDb.TableDefs.Delete strLocalTableName
CurrentDb.TableDefs.Refresh
Next I build a connection string:
ODBC;DRIVER={SQL Server Native Client 11.0};SERVER=XXXXX;DATABASE=XXXXX;UID=dps;PWD=xxxxx
In the final step I try to link the table (again):
Set td = CurrentDb.CreateTableDef(strLocalTableName, dbAttachSavePWD, strRemoteTableName, strConnectionStringNative)
CurrentDb.TableDefs.Append td
All parameters are correct. I double checked them.
After an automatic update of Access on my main computer this code does not seem to work anymore. I get "Error 3001 - Invalid argument".
No matter which Access database I try (different customers), they all fail on this command. My version of Access:
Access 2021 MSO (Version 2212 Build 16.0.15928.20186) 64-bit
The strange thing is that the same code (even using the same Access file) works fine on another computer. The Access version on that computer however is:
Access 2019 MSO (Version 2211 Build 16.0.15831.20098) 64-bit
So it seems that it only occurs with the latest update of Access/Office. Any ideas how to get this working again?
