Naming of linked SQL Server tables is inconsistent
Updated: Feb 18
When you (re)link SQL Server tables using code, the naming of the linked tables no longer matches the previous behaviour.
The first parameter of the CreateTableDef method, [Name] for the local table name, is ignored. Instead the resulting table in Access is named after the third parameter SourceTableName, whereby the schema name (in most cases "dbo") is omitted. However, some people have reported that the omission of the schema prefix does not always happen. The logic behind this inconsistency of the bug is still unclear.
Depending on the specific circumstances, the changed behaviour can cause consequential errors like 3215 "dbo.TableName is not a valid name".
The first report was made on 2023-01-09 for Access 365 version 2212 build 15928.20198 in this Answers discussion. Many other users have confirmed the problem for version 2212. The recently updated click2run versions of Access 2016/2019/2021 are also affected, because they are all version 2212.
Version 2301 (Build 16026.20146) released in the Current Channel on 26th January, has a fix for this bug. Several affected people have confirmed that the problem is resolved in this version.
Microsoft has as well published an article about the problem.
Until you get the fix, you can try to use one of these temporary remedies:
Workaround 1: Reverting to Office/Access version 2211 helps.
Workaround 2: (Re)linking works again in many cases when the schema prefix "dbo." is removed in the code.
Workaround 3: According to this forum answer (and the Access team) you can rename the created TableDef to the desired local table name before you Append it. This also helps in many cases.
Workaround 4: by Philipp Stiefel
If you get error no 3001 "Invalid argument" when using CreateTableDef, then try this approach instead (of course adapted with your variables):
Set td = db.CreateTableDef() td.Name = stLocalTableName td.Connect = stConnect td.Attributes = dbAttachSavePWD td.SourceTableName = stRemoteTableName db.TableDefs.Append td