CODEWITHSUNDEEP
ms-accessvbams-access-2010ms-access-2007
InitLipton
InitLipton

Reputation: 2453

Change table name in linked table in Access

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).

enter image description here

Upvotes: 2

Views: 4937

Answers (1)

HansUp
HansUp

Reputation: 97131

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

Upvotes: 6

Related Questions