Thursday, August 29, 2013

Using VBA to Redirect the Links to the Back End



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

No comments:

Post a Comment