Can link be changed w/o opening the db?

[jDa]

Consider four databases: Controller.accdb, RPG.accdb, OldSource.accdb, and NewSource.accdb.

RPG.accdb has a link to Table_MyData in OldSource.accdb. This link needs to be redirected to Table_MyData in NewSource.accdb.

I need to write VBA code in Controller.accdb to accomplish this without having to manually open RPG.accdb.

Ideas?

 

[jDa]

Eureka! Lots of head scratching later...yes it can be done. Here is the code.

Public Const NewLinkName As String = "LegacyName"
Public Const OldLinkName As String = "LegacyName"
Public Const SrcTablName As String = "LegacyName"
Public Const strShortCon As String = ";DATABASE=F:\Experimental\LinkUpdater\NewSource.accdb"
Public Sub UpdateLinks()
Dim myRPGFile As Database
Dim myNewLink As TableDef

'Open a database that has a link to an outdated table; delete the link.
Set myRPGFile = OpenDatabase("F:\Experimental\LinkUpdater\RPG.accdb")
myRPGFile.TableDefs.Delete OldLinkName

'Create a TableDef for a link to a new table. Set properties. Append the TableDef.
Set myNewLink = myRPGFile.CreateTableDef(NewLinkName)
myNewLink.Connect = ";DATABASE=F:\Experimental\LinkUpdater\NewSource.accdb"
myNewLink.SourceTableName = SrcTablName
myRPGFile.TableDefs.Append myNewLink

myRPGFile.Close
Set myRPGFile = Nothing
End Sub

 

[Galaxiom]

You should be able to just edit the Connect Property of the existing TableDefs rather than recreating the TableDef.

Hopefully the new string can be derived from the old one.

 

[jDa]

Ah...much easier! And very cool...thanks!