Our database-of-record uses 38 tables that are in five
different back ends. When I move an updated front end from the sandbox
directory to the production directory relinking the tables to the correct back
end using the link manager was always a pain. I figured that there had to be a
way to automate the process. Once I realized that I can use CurrentProject.Connection
to determine which directory the front end was opened from it was just a matter
of If-Then-Else logic.
I wrote the code as a public function and use an AutoExec
macro to call it when the front end is opened.
Here is the code:
Public
Function RelinkBackEnds()
'This function checks to see
which directory the front end was opened from and
'redirects the table links to
the proper back ends. Any front end opened from the
'\kheelstudent\ directory is a
production front end and needs to be linked to the
'production back end in the
\kheelstudent\ directory. Any front end opened from the
'\Kheel\ directory is a sandbox
front end and needs to be linked to the
'production back end in the
\Kheel\ sandbox. This code handles the redirecting without
'the user having to use the
Linked Table Manager.
Dim Dbs As Database
Dim tblDef As TableDef
Dim tblDefs As TableDefs
Dim strNewPath As String
Dim strBE As String
Set
Dbs = CurrentDb
'Get the table definitions from the
database.
Set tblDefs = Dbs.TableDefs
'Cycle through all the table definitions.
For Each tblDef In tblDefs
'Test the path to see if the database front
end is in the \kheelstudent\ or \Kheel\ directory.
If CurrentProject.Connection Like
"*kheelstudent*" Then
'If the database is in the
\kheelstudent\ directory set the new path, strNewPath,
'to the back end directory under
\kheelstudent\.
strNewPath =
";DATABASE=\\ilr.cornell.edu\ilr
shared\Catherwood\kheelstudent\KIDB\BackEnds\"
'Check to make sure that there is a
table name in the table definition.
If tblDef.SourceTableName <>
"" Then
'Debug.Print tblDef.Connect
'For each path test for the presence
of "sb" in the string. The sandbox back ends have the
'suffix "sb" and are
located in the \Kheel\ directory. The production back ends lack the
'suffix "sb" and are
located in the \kheelstudent\ directory.
If tblDef.Connect Like
"*sb*" Then
'If the path string contains
"sb" set the variable "strBE" to the
'corresponding production back
end.
If tblDef.Connect Like
"*KIDBsb_be.accdb" Then
strBE =
"KIDB_be.accdb"
ElseIf tblDef.Connect Like
"*KDMTsb_be.accdb" Then
strBE =
"KDMT_be.accdb"
ElseIf tblDef.Connect Like
"*FolderListssb_be.accdb" Then
strBE =
"FolderLists_be.accdb"
ElseIf tblDef.Connect Like
"*Keywordsb_be.accdb" Then
strBE =
"Keyword_be.accdb"
ElseIf tblDef.Connect Like
"*KULPsb_be.accdb" Then
strBE =
"KULP_be.accdb"
End If
'Debug.Print strBE
'Construct the link path with
the new path and the production back end.
tblDef.Connect = NewPath &
strBE
'Debug.Print tblDef.Connect
'Refresh the link.
tblDef.RefreshLink
Else:
'If the path string does not
contain "sb" then the front end is already linked to the
'production back end.
'Do nothing.
End If
'Reset the new path variable to a
zero-length string.
strNewPath = ""
End If
Else:
'If the database is in the \Kheel\
directory set the new path, strNewPath,
'to the back end directory under
\Kheel\.
strNewPath =
";DATABASE=\\ilr.cornell.edu\ilr
shared\Catherwood\Kheel\Miles\Database\BackEnds\"
'Check to make sure that there is a
table name in the table definition.
If tblDef.SourceTableName <>
"" Then
'Debug.Print tblDef.Connect
'For each path test for the
presence of "sb" in the string. The sandbox back ends have the
'suffix "sb" and are located
in the \Kheel\ directory. The production back ends lack the
'suffix "sb" and are
located in the \kheelstudent\ directory.
If tblDef.Connect Like
"*sb*" Then
'If the path string contains
"sb" then the front end is already linked to the
'sandbox back end.
'Do nothing.
Else:
'If the path string does not
contain "sb" set the variable "strBE" to the
'corresponding sandbox back
end.
If tblDef.Connect Like
"*KIDB_be.accdb" Then
strBE =
"KIDBsb_be.accdb"
ElseIf tblDef.Connect Like
"*KDMT_be.accdb" Then
strBE =
"KDMTsb_be.accdb"
ElseIf tblDef.Connect Like
"*FolderLists_be.accdb" Then
strBE =
"FolderListssb_be.accdb"
ElseIf tblDef.Connect Like
"*Keyword_be.accdb" Then
strBE =
"Keywordsb_be.accdb"
ElseIf tblDef.Connect Like
"*KULP_be.accdb" Then
strBE =
"KULPsb_be.accdb"
End If
'Debug.Print strBE
'Construct the link path with
the new path and the production back end.
tblDef.Connect = NewPath &
strBE
'Debug.Print Tdf.Connect
'Refresh the link.
tblDef.RefreshLink
End If
'Reset the new path variable to a
zero-length string.
strNewPath = ""
End If
End If
'Move on to the next table definition.
Next
End
Function
