Linking Table Code

[Guest]

Does anyone have code or can they point me the way to linking tables from
other MS Access DBs? I want my DB to start up with no links and then based
upon certain parameters link tables.

 

[Guest]

The Procedure below will link tables to a remote database. Call it with
strPath = the path of the remote database, including the database name, and
strTable being the table you want to link. Call for each table you want to
link.

Jim B

'*D* This procedure sets a link to a new table in a remote database
Sub SetNewTableLink(strPath As String, strTable As String)
On Error GoTo Err_SetNewTableLink

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim tdf As DAO.TableDef
Dim ws As Workspace

Set db = CurrentDb()

For Each tdf In db.TableDefs
If tdf.SourceTableName = strTable Then
tdf.RefreshLink
db.Close
Exit Sub
End If
Next tdf


Set tbl = db.CreateTableDef(strTable)

'Set the link to the remote table
tbl.Connect = ";DATABASE=" & strPath
tbl.SourceTableName = strTable
db.TableDefs.Append tbl

db.Close

Set tbl = Nothing
Set db = Nothing

Exit_SetNewTableLink:
Exit Sub

Err_SetNewTableLink:
MsgBox Err.Description
Resume Exit_SetNewTableLink

End Sub