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

Microsoft Access Back-End Security.

Now that your front-end is secure you’ll want to make sure that users can’t just go to the directory and open the back-end and alter data. Here I learned something new: I had thought it was impossible to mask an input box, but some code written by Daniel Klann allows you to mask the password in an input box. I found the code for this at: http://www.tek-tips.com/faqs.cfm?fid=4617

The beauty of using an input box is that the user can interact only with the input box until log in is successful. Nothing else in the application will work while the input box is open and the database will close if the input box is closed or canceled. Using the input box also makes the password case sensitive.

To secure your back-end you can either put Mr. Klann’s code in an existing module, or in a new module. I made a new module and named it basLogIn so that I can just import it into other databases as needed. Then add the following function:

Public Function LogIn()

Dim strPassword As String

'Pick up the password from the first line in tblUsers.

strPassword = DLookup("[Password]", "tblUsers", "[ID] = 1")

'You can just replace the DLookUp() function with the password like this:

'strPassword = "Password"

'But by putting in a table you will be able to change it from the front end

'using a "change password" accessible only to administrators.

'Debug.Print "strPassword = " & strPassword

If InputBoxDK("enter password", "Log In") = strPassword Then

      'A successful log in closes the input box and allows the user to open tables.

Else:

      'An unsuccessful log in gives the user a second chance to log in.

      MsgBox ("Sorry, Incorrect Password" & vbNewLine & "Please enter Password")

      If InputBoxDK("enter password", "Log In") = strPassword Then

      'A successful log in closes the input box and allows the user to open tables.

      Else:

            'An unsuccessful log in gives the user a third chance.

            MsgBox ("Sorry, Incorrect Password" & vbNewLine & "Please enter Password")

            If InputBoxDK("enter password", "Log In") = strPassword Then

            'A successful log in closes the input box and allows the user to open tables.

            Else:

            'No more chances.

            'An unsuccessful log in closes the application.

            MsgBox ("Sorry you are not authorized to access this database.")

            DoCmd.Quit

            End If

      End If

End If

End Function

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter RunCode and under “Arguments” enter LogIn(). Save the macro. Now whenever the database is opened the function LogIn() will run before anything else happens.

Now I may have to go back and rework some of my front-end security to use input boxes instead of forms, all thanks to the excellent work of Daniel Klann.