Change table name in linked table in Access

Question

I'm trying to change the name of a table in Access. I've gone to the link manager and gone through that process. It will change to the Server I put it, but it never changes the Table name (Highlighted in yellow).

Answer 1

It seems your goal is to change the linked TableDef's SourceTableName, but I doubt that is possible. Attempting to do it triggers error #3268:

Cannot set this property once the object is part of a collection.

So I think you will have to create a new linked TableDef with the Connect property from the old link and your new SourceTableName value and Append that to the TableDefs collection.

Const cstrOldName As String = "dbo_tblFoo2"
Dim db As DAO.Database
Dim tdfOld As DAO.TableDef
Dim tdfNew As DAO.TableDef
Set db = CurrentDb
Set tdfOld = db.TableDefs(cstrOldName)
tdfOld.Name = cstrOldName & "_old" ' rename the old link

Set tdfNew = db.CreateTableDef
With tdfNew
    .Name = cstrOldName
    .Connect = tdfOld.Connect
    .SourceTableName = "dbo.Dual"
End With
db.TableDefs.Append tdfNew