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

Micosoft Access Front-End Security. Part 3: Authentication at the individual control level.

On some of the forms in our collection management database I have buttons that I want everyone to be able to use, some I only want a few to be able to use, and a few I only want one or two people to be able to use. I control these buttons by using the computer’s user name.

First I added a field called “Level,” to the users table, tblUsers. I used four levels, with level 4 being the administrator level, with no restrictions on what that person can do. Level 1 is the most restricted, users at this level can do little more than search and read data.

In the On Click event of each button that is not a level 1 control I wrap the event procedure in the following If-Then-Else logic:

    Dim intUser As Integer                  ‘This variable holds the number for the user level.

    ‘Pick up the user level by matching the computer’s username with the user ID in tblUsers

    intUser = DLookup("[Level]", "tluUsers", "[UserID] = '" & Environ("username") & "'")

    ‘For a level 2 control you want to allow all level 2, 3, and 4 users, so the code allows

    ‘for users greater than level 1.

    ‘For a level 3 control you want to allow only level 3 and 4 users, so the code allows only

    ‘users greater than level 2.

    ‘A level 4 control has code to allow only users greater than level 3.         

    If intUser > 3 Then

        ‘This is where you would put the code for your event procedure.

    Else:

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

    End If

Bound text boxes can be protected by putting this If-Then-Else logic in the Got Focus procedure. If the user level is too low to allow updating the data then reset the focus to another control. In this example level 1 users are not allowed to edit the collection title, but are allowed to edit the donor name.

    If intUser < 2 Then

        MsgBox ("Sorry, you are not authorized to edit the Collection Title")

        Me.DonorsName.SetFocus

    End If

Next Week: Back-End Security.

Microsoft Access Front-End Security. Part 2: Requiring Authentication to Open the Database

If you need, or want, to limit who can open your database there are at least two ways of doing so. One is easy and passive, it looks at the user ID for the computer and either opens the database, or not depending on whether or not you have allowed that user access. The other takes a little more work and requires input from the user.

Passive Authentication:
You can use this method if you are working in an environment that requires users to log onto their computers. Each computer stores the user ID and that can be picked up in the VBA code. An AutoExec macro triggering a user defined function and a table containing the allowed user IDs will do the trick. First you must define your function. To do this you can either put it in an existing module, or create a new module for it, either way we’ll call this function “Authentication()”.

Public Function Authentication()

'This function runs from the AutoExec macro when the database is opened.

Dim strUser As String          'This is the user ID picked up from the computer.

Dim intUser As Integer         'This is derived from the table of allowed users tblUsers.

'Pick up the user ID with the Environ() function.

strUser = Environ("username")

'Assign either a 1 or 0 to intUser by comparing strUser with the IDs in tblUsers.

'the DCount() function will count the number of times strUser appears in tblUsers.

'Be sure to user the null function Nz() to account for the null value returned when

'strUser does not match any value in tblUsers and convert it to a 0.
'This way any user in tblUsers will have a value of 1 and anyone not in the table
'will have a value of 0.

intUser = Nz(DCount(“[User]”, “tblUsers”, “[User] =” & strUser & “”), 0)

'If intUser = 0 the user ID is not in tblUsers and not allowed to open the database.

'In that case tell the user they are not authorized and close the application.

'Otherwise allow the database to open.

      If intUser = 0 then

          If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

              DoCmd.Quit acQuitSaveNone

          Else:

              'Correct log in opens whatever form you want your users to start with.
              DoCmd.OpenForm "frmStartPage"

          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 Authentication(). Save the macro. Now whenever the database is opened the function Authentication() will run before anything else happens.


Interactive Authentication:
This requires the user to enter a password, which can be either unique to each user or the same one for everyone. In either case you will want to mask the password as it is entered, so you will need to use a form instead of a function. For this type of authentication your users table, tblUsers, will have two columns. One column for the user name or ID and one for the password, can be the same for everyone. The form will need three text boxes. One text box for the username, one for the password, and a third one, set not to display, for the counter. There also needs to be a “Log In” button. The OnClick event for this button will run the following code:

Private Sub cmdLogIn_Click()

Dim strUser As String                      'This is the user name entered by the user.

Dim strPassword As String              'This is the password entered by the user.

Dim strAuthenticate As String           'This is the password from tblUsers.

Dim intCounter As Integer                'This keeps track of number of log in attempts.

intCounter = Nz(Me.txtCounter, 0)

'Me.txtCounter refers to a control on the Log In with its “visible” property set to “No.”

strUser = Me.txtUser

strPassword = Me.txtPassword

strAuthenticate = Nz(DLookup("[Password]", "tblUsers", "[User] = '" & strUser & "'"), “None”)

If strAuthenticate = “None” Then

       'Is someone not included in the users table attempts to log in close the database.

       If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

       DoCmd.Quit acQuitSaveNone

       End If

Else:

       If strPassword = strAuthenticate Then

             'Correct log in opens whatever form you want your users to start with.

             DoCmd.OpenForm "frmStartPage"

             'Then close the Log In form.

             DoCmd.Close acForm, "frmLogIn"

       Else:

             Me.txtUser = strUser

             Me.txtPassword = ""

             Me.txtPassword.SetFocus

             intCounter = intCounter + 1

            Me.txtCounter = intCounter

            'After three incorrect log in attempts close the database.

            If intCounter = 3 Then

                 If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

                      DoCmd.Quit acQuitSaveNone

                End If

            Else:

                MsgBox "Incorrect Log In. Please try Again"

           End If

      End If

End If

DoCmd.Close acForm, "frmLogIn"

End Sub

To mask the password as it is typed you will need an On Load event for the form:

Private Sub Form_Load()

       Me.txtUser.InputMask = "Password"

End Sub

If you would like the password to be case sensitive replace
If strPassword = strAuthenticate Then
With
If CBool(InStrB(strPassword, strAuthenticate)) Then

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter OpenForm and under “Arguments” enter frmLogIn. Save the macro. Now whenever the database is opened the log in form will open before anything else happens.

Of course, a user could just close or minimize the log in form, but if you had locked down the front end as described in the last post they would not be able to open any forms or interact with any of the tables.

Next week: Part 3: Authentication at the individual control level.

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.