Linking tables programmatically

[Guest]

I am trying to write an update routine that can automatically add a link in my program files to a newly-created table in my data file.

I can get as far as "DoCmd.RunCommand acCmdLinkTables", but this alone opens a dialog that expects the user to browse to the data file location, then choose the table name. I already know both of these and want to automate this.

I started to continue with the code below, but got stuck on the last line. Am I close?

Dim DataFile as Object
TablePath = "<FullPathName>\<FileName>"
Set DataFile = DBEngine.Workspaces(0).OpenDatabase(TablePath, True)
DataFile.TableDefs("<TableName>").Connect = ";DATABASE=" & TablePath & ""

 

[Marshall Barton]

I am trying to write an update routine that can automatically add a link in my program files to a newly-created table in my data file.

I can get as far as "DoCmd.RunCommand acCmdLinkTables", but this alone opens a dialog that expects the user to browse to the data file location, then choose the table name. I already know both of these and want to automate this.

I started to continue with the code below, but got stuck on the last line. Am I close?

Dim DataFile as Object
TablePath = "<FullPathName>\<FileName>"
Set DataFile = DBEngine.Workspaces(0).OpenDatabase(TablePath, True)
DataFile.TableDefs("<TableName>").Connect = ";DATABASE=" & TablePath & ""

That code wiil update an existing linked table in the
DataFile. I think you want to add a new TableDef to the
program mdb.

If this code is running in the program file, then it could
be more like this air code:

Dim dbProg As DAO.Database
Dim tdf Ad DAO.TableDef
Set db = CurrentDb()
Set tdf = db.CreateTableDef("<TableName>")
tdf.Connect = ";DATABASE=""" & TablePath & """"
db.TableDefs.Append tdf
db.TableDefs.Refresh

 

[Guest]

That got me off to a good start, but it would not let me append because no fields were defined. I did a little more searching on the web and found .SourceTableName, which resulted in the following the fix that worked:

Dim tempTableDef As Object
Set tempTableDef = CurrentDb.CreateTableDef("<TableName>")
tempTableDef.Connect = ";DATABASE=" & TablePath & ""
tempTableDef.SourceTableName = "<TableName>"
CurrentDb.TableDefs.Append tempTableDef

 

[Marshall Barton]

Good to hear that you got it going.

Don't forget to refresh the TableDefs collection after the
append.