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 Front-End Security. Part 1: Locking the Front End

In this week’s post I am assuming that you have split your database into two distinct files: one for the front end, which is what your users interact with, and one for the back end, which is where the data are actually stored. There are several things you can do to make you data and database more secure. I will discuss three of them here. First there is protecting the back end and your VBA code, Second is requiring a password to open the database, third is limiting certain functions to certain people.

Locking down the back end and VBA Code. By default anyone using Microsoft Access, even with a split database, can open up the tables and work directly in it. They can also open the VBA editor and alter the code. To prevent this you need to lock down the front end.

Important! Be sure to make a back-up copy of your deployed production front end and store it in a different directory. There may well be a way to unlock a front end, but I don’t know what it is. It’s safer to have a back up anyway.

In Access 2007 you lock down by going to the Office button, then select “Access Options” at the bottom. In Access Options choose “Current Database”. In Access 2003 go to “Tools”, “Start up”, “Advanced”. Now, unselect “Use Special Keys”, “Enable Layout View…”, “Enable design changes…”, “Allow Full Menus”, and “Allow Default Menu Shortcuts”. Then click “OK”.

When the database is next opened no user will be get to the back end tables except through the forms and will not be able to alter the forms or the VBA code.


Next week: Part 2: Requiring a password to open the database.