MS Access offers a bit of security with passwords that can encrypt the Database (both the Front and/or Back End) to limit who can open a Database. However, we are still faced with the issue that once a User has access to the Database, they have access, since the password encryption unlocks the entire Database, not just certain parts.
One of the options for managing multiple users is to have a Switchboard or menu of activities that can be performed within the Database, and the Users will only select those items that apply to them, based on their responsibilities in the work center. However, the primary challenge with such an option is that you open yourself to a user “exploring” places of the Database they shouldn’t be exploring. In most situations, this is not really a problem, because the average employee doesn’t want to sabotage their place of employment. However, when it comes to personal, confidential or even medical information, it behooves the Database Administrator to build into their Database certain safeguards to ensure that restricted information is not fallen upon casually or incidentally.
Another solution would be to create separate front ends for each type of User. When one User requires access to a particular set of Forms and Reports, those objects could be loaded into one Front End, linked to the Back End. Another User requires a different set of Forms and Reports, so the Database Administrator builds another Front End that suits. However, the obvious challenge with this solution is the constant expansion of different FEs. This can also cause a drastic duplication of effort, as some different Users (or different types of Users) may require the use of the same Objects. Then, every time one of these Objects is changed, all FEs containing that Object must also be updated.
I find myself in a situation like that in my work center. We only have 10 employees who use the Database on a regular basis, with sometimes 10 more who have a very limited access for short periods of time. However, those 20 employees have vastly different roles in the Database. Additionally, since nearly everything we touch is Privacy Act Information (I work for the Federal Government), we want to control access as much as possible.
Over time, I have developed a method for limiting access to the Database, based on the User. The method for doing this is rather simple:
- Create a Users Table
- Establish Public Constants and Variables
- Determine who has accessed the Database
- Determine what to do with that User
- Allow User-specific access to the Database
Creating a Users Table
Your Table may have different Fields, but my Table (tblUsers) has certain fields based on my requirements. Feel free to make your own list of requirements and build the Table appropriately. A description of my Table follows:
Expand|Select|Wrap|Line Numbers
- Field Name Data Type
- UserKey Long (AutoNumber)
- UserID Text
- UserName Text
- UserRights Integer
- UserMachine Text
- UserDirectory Text
- UserSigBlock Memo
The UserRights will be explained below, but this is a value indicating what level of access (or areas of access) the User will have. This comes into play in the final steps.
Since our IT Department often requests a list of our Users’ machine names, I have included that in my list, and this, too, is easily obtainable from the OS environment.
Occasionally, our Users will download reports or spreadsheets from the Database, and having a User-established Directory helps to make sure their items are always in one place.
Our office frequently sends out e-mail messages from the Database, so I have also included a User-customizable E-Mail signature block. This allows the Database to quickly add a signature block to outgoing E-Mails. This is very convenient.
Now that your Users Table is created, we need to figure out how to use it!
Establishing Public Constants and Variables
I’ve created a Module that holds my lists of Public Variables and Public Functions, called modSystem. Here is a list of what I use to limit User Access:
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- 'User Rights Constants
- Public Const UserAdmin As Integer = 1
- Public Const UserOIC As Integer = 2
- Public Const UserPromo As Integer = 3
- Public Const UserRecords As Integer = 4
- Public Const UserSrRecorder As Integer = 5
- Public Const UserRecorder As Integer = 6
- 'User Variables
- Public glngCurrentUser As Long
- Public gstrUserID As String
- Public gstrCurrentUser As String
- Public gintUserRights As Integer
- Public gstrUserDirectory As String
- Public gstrUserMachine As String
To explain my Public Variables, I like to allow the Database to have certain data always at its finger tips. For example, the gstrUserID is the System User ID, returned from the Operating System. The variable gstrCurrentUser is the actual name of the User (e.g. “Egbert Schmuckatelli”). This value is established when the Database Administrator sets up the User’s Account. The variable glngCurrentUser is the Primary Key of tblUsers. The other variables are self-explanatory.
I use these variables throughout the Database, whenever we want to identify a User as accomplishing a certain task or when User-specific information must be provided.
Determining Who Has Accessed the Database
This is the easiest step. When the Database opens, I use a Splash Form which automatically determines who logged into the Database, as well as what the Database will do with that User. In the OnOpen Event of the Form, I have the following:
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Open(Cancel As Integer)
- On Error GoTo EH
- Dim db As Database
- Dim rst As Recordset
- Dim strSQL As String
- gstrUserID = Environ("USERNAME")
- Set db = CurrentDb()
- strSQL = "SELECT * FROM tblUsers " & _
- "WHERE UserID = '" & gstrUserID & "';"
- Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
- If Not rst.EOF Then
- With rst
- .MoveFirst
- glngCurrentUser = !UserKey
- gstrCurrentUser = !UserName
- gintUserRights = !UserRights
- gstrUserMachine = !UserMachine
- gstrUserDirectory = !UserDirectory
- End With
- Else
- 'This User does not exist in the Users Table
- 'Determine what to do--whether to Quit or set up new account
- End If
- rst.Close
- db.Close
- Set rst = Nothing
- Set db = Nothing
- Exit Sub
- EH:
- MsgBox "There was an error initializing the Form! " & _
- "Please contact your Database Administrator.", vbCritical, "Error!"
- Exit Sub
- End Sub
Determining What to do With a User
Since my Splash Form has no actionable objects on it, after it completes doing several other things in the background (driven by the OnTimer Event), the Code simply tells the Form to Close. In the OnClose Event, I have placed the following:
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Close()
- On Error GoTo EH
- Select Case gintUserRights
- Case UserAdmin
- DoCmd.OpenForm "frmAdministrator"
- Case UserPromo, UserOIC, UserSrRecorder
- DoCmd.OpenForm "frmSwitchboard"
- Case UserRecords
- DoCmd.OpenForm "frmCommandRecords"
- Case UserRecorder
- DoCmd.OpenForm "frmPRFReview"
- End Select
- Exit Sub
- EH:
- MsgBox "There was an error Closing the Form! " & _
- "Please contact your Database Administrator.", vbCritical, "Error!"
- Exit Sub
- End Sub
However, you may notice that three different types of Users (userPromo, UserOIC and UserSrRecorder) all open the Switchboard Form. This doesn’t seem to jibe with this entire Article! If you have established different levels of User Rights, how can they all access the same Form? Won’t they all have access to the same information?
Here is where I have made a small twist on a classic capability of MS Access.
Allowing User-Specific Access to the Database
Many years ago, Microsoft Access began introducing the Switchboard Manager. I have used that standard format as the basis for my Switchboard, but have made some changes to provide User-specific access to the Menu Items. Keep in mind that if you currently use MS Access’ built in Switchboard manager, this method will NOT work—nor will you be able to edit the Switchboards using the built in Switchboard Manager. This is an overhaul to the Tables, as well as the VBA code that runs the Switchboard, so you may want to start from scratch.
First, I’ve made some modifications to the underlying Table for the Switchboard. I have renamed my Table “tblSwitchboards” (note the plural, as this Table does, in fact, hold data for multiple Switchboards). Here is my list of Fields:
Expand|Select|Wrap|Line Numbers
- Field Name Data Type
- User Integer
- SwitchboardID Integer
- ItemNumber Integer
- ItemText Text
- Command Integer
- Argument Text
Expand|Select|Wrap|Line Numbers
- ID Command
- 0 Page Title
- 1 Switch Page
- 3 Open Form
- 4 Open Report
- 6 Quit
Additionally, I have modified the Argument Field to be a Lookup Field, in which the Row Source is the following Query:
Expand|Select|Wrap|Line Numbers
- SELECT MSysObjects.Name
- FROM MSysObjects
- WHERE (MSysObjects.Type=-32768 OR MSysObjects.Type=-32764)
- ORDER BY MSysObjects.Name;
You may also notice that this Query will return SubForms and SubReports (as well as the Parent Forms and Reports). If you have a standard Naming Convention (as I know ALL of you do...) you can limit the Query further by changing the Where clause to:
Expand|Select|Wrap|Line Numbers
- WHERE (MSysObjects.Name Like "frm*" AND MSysObjects.Type=-32768) OR (MSysObjects.Name Like "rpt*" AND MSysObjects.Type=-32764)
It is very important that you set the “Limit To List” property of this Field to “No”. The reason for this will become apparent shortly.
Now that we have created this Switchboards Table, let’s populate it. Excuse the length of this list, but I wanted to illustrate more fully. Keep in mind that this list has been pared down significantly, but is used only for illustration (and please excuse the whacked columns, but I did not see a point in converting my tabs to spaces):
Expand|Select|Wrap|Line Numbers
- User ID No. Item Cmd Argument
- Administrator 1 0 ACC Officer Promotions Page Title Default
- Administrator 1 1 Task Management Switch Page 2
- Administrator 1 2 Administrative Tasks Switch Page 6
- Administrator 1 3 Quit Quit
- Administrator 2 0 Task Management Menu Page Title
- Administrator 2 1 Review Open Tasks Open Form frmTasks
- Administrator 2 2 Overdue Tasks Report Open Report rptOverdueTasks
- Administrator 2 3 Upcoming Tasks Report Open Report rptUpcomingTasks
- Administrator 2 4 Task Template Open Form frmTaskTemplate
- Administrator 2 5 Print Task Template List Open Report rptTaskTemplateListing
- Administrator 2 6 Print Continuity Book Open Report rptContinuityBook
- Administrator 2 7 Manage Projects Open Form frmProjects
- Administrator 2 8 Return to Main Menu Switch Page 1
- Administrator 6 0 Administrative Tasks Page Title
- Administrator 6 1 Command Records Switch Page 10
- Administrator 6 2 Release Actions Open Form frmPromotionRelease
- Administrator 6 3 Recorders Switch Page 11
- Administrator 6 4 Evaluation Timeliness Open Form frmEvaluations
- Administrator 6 5 STEP Quotas Open Form frmSTEPQuotas
- Administrator 6 6 Manage Users Open Form frmUsers
- Administrator 6 7 Administrator Functions Open Form afrmAdministrator
- Administrator 6 8 Return to Main Menu Switch Page 1
- Administrator 10 0 Command Records Menu Page Title
- Administrator 10 1 Screening Progress Open Form frmRecordScreening
- Administrator 10 2 Send Missing Evals Open Form frmSendMissingEvals
- Administrator 10 3 Command Records Database Open Form frmCommandRecords
- Administrator 10 4 Return to Previous Menu Switch Page 6
- Administrator 11 0 Recorder Actions Page Title
- Administrator 11 1 Recorder Listing Open Form frmRecorders
- Administrator 11 2 Recorder Scorecard Open Form frmRecorderScorecard
- Administrator 11 3 Return to Previous Menu Switch Page 6
- OIC 1 0 ACC Officer Promotions Page Title Default
- OIC 1 1 Task Management Switch Page 2
- OIC 1 2 Administrative Tasks Switch Page 6
- OIC 1 3 Quit Quit
- OIC 2 0 Task Management Menu Page Title
- OIC 2 1 Review Open Tasks Open Form frmTasks
- OIC 2 2 Overdue Tasks Report Open Report rptOverdueTasks
- OIC 2 3 Upcoming Tasks Report Open Report rptUpcomingTasks
- OIC 2 4 Task Template Open Form frmTaskTemplate
- OIC 2 5 Print Task Template List Open Report rptTaskTemplateListing
- OIC 2 6 Return to Main Menu Switch Page 1
- OIC 6 0 Administrative Tasks Page Title
- OIC 6 1 Command Records Switch Page 10
- OIC 6 2 Recorders Switch Page 11
- OIC 6 3 Evaluation Timeliness Open Form frmEvaluations
- OIC 6 4 Return to Main Menu Switch Page 1
- OIC 10 0 Command Records Menu Page Title
- OIC 10 1 Screening Progress Open Form frmRecordScreening
- OIC 10 2 Send Missing Evals Open Form frmSendMissingEvals
- OIC 10 3 Command Records Database Open Form frmCommandRecords
- OIC 10 4 Return to Previous Menu Switch Page 6
- OIC 11 0 Recorder Actions Page Title
- OIC 11 1 Recorder Listing Open Form frmRecorders
- OIC 11 2 Recorder Scorecard Open Form frmRecorderScorecard
- OIC 11 3 Return to Previous Menu Switch Page 6
- Officer Promotions 1 0 ACC Officer Promotions Page Title Default
- Officer Promotions 1 1 Task Management Switch Page 2
- Officer Promotions 1 2 Quit Quit
- Officer Promotions 2 0 Task Management Menu Page Title
- Officer Promotions 2 1 Review Open Tasks Open Form frmTasks
- Officer Promotions 2 2 Overdue Tasks Report Open Report rptOverdueTasks
- Officer Promotions 2 3 Return to Main Menu Switch Page 1
- Senior Recorder 1 0 Senior Recorder Actions Page Title Default
- Senior Recorder 1 1 PRF Review Open Form frmPRFReview
- Senior Recorder 1 2 Recorder Scorecard Open Form frmRecorderScorecard
- Senior Recorder 1 3 Quit Quit
Notice, also, how the Switchboard options are clearly different for each User Type. Now we just have to set up our Form to work with this Table.
- “Default” – this indicates that this is the Main Switchboard (Home page)
- Blank – indicating that this is a Switchboard Page Title (also indicated by the Command Field) or indicates to Quit the Database
- A number (this is saved as Text!!) and indicates the Switchboard page to which the User will be directed
- The name of a Form or Report
(Image 1)
As you may not be able to see from the picture attached, my Switchboard has ten Command Buttons and Labels. These Command Buttons and Labels follow a specific Naming Convention (because I know you ALL follow the same principles!) of “cmdOption1”, “cmdOption2”, etc. and “lblOption1”, “lblOption2”, etc. You can ignore some of the stuff on this Form, but you can see that it’s really just a modification of the old MS Access standard Switchboard. When we look under the hood, this is what we find:
The Form itself has the Record Source of tblSwitchboards. the Form's Properties of Allow Additions and Allow Deletions are both set to No. As the User navigates the Menu, the Form Filters the specific Record that it needs (one record and one record only is displayed). There are several procedures we have to put in place for this Form to Work with the Table:
NB: Some of my code is not displayed as it is irrelevant for this purpose.
Expand|Select|Wrap|Line Numbers
- Option Explicit
- Option Compare Database
- Private Const intButtons = 10
- Private Sub Form_Open(Cancel As Integer)
- On Error GoTo EH
- Me.Filter = "User = " & gintUserRights & _
- " AND ItemNumber = 0 AND Argument = 'Default'"
- Me.FilterOn = True
- Exit Sub
- EH:
- MsgBox "There was an error initializing the Form! " & _
- "Please contact your Database Administrator.", vbCritical, "Error!"
- Exit Sub
- End Sub
Then, when the Form opens, we filter by the “Default” Switchboard Page for that User. Very simple, so far, right?
When we choose another Switchboard Page to move to, the Code will filter the Form by the Page Title Selected (according to the appropriate User), so when that Filter is complete, we need to show the Switchboard options for this Page:
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Current()
- On Error GoTo EH
- FillOptions
- Exit Sub
- EH:
- Exit Sub
- MsgBox "There was an error moving to the current Record! " & _
- "Please contact your Database Administrator.", vbCritical, "Error!"
- End Sub
Then, the associated Procedure to fill the Options for the Form:
Expand|Select|Wrap|Line Numbers
- Private Sub FillOptions()
- On Error GoTo EH
- Dim dbOptions As Database
- Dim rstOptions As Recordset
- Dim strSQL As String
- Dim intOption As Integer
- Me.cmdOption1.SetFocus
- For intOption = 2 To intButtons
- Me("cmdOption" & intOption).Visible = False
- Me("lblOption" & intOption).Visible = False
- Next intOption
- Set dbOptions = CurrentDb()
- strSQL = "SELECT * FROM tblSwitchboards" & _
- " WHERE User = " & gintUserRights & _
- " AND ItemNumber > 0 AND SwitchboardID = " & Me.SwitchboardID & _
- " ORDER BY ItemNumber;"
- Set rstOptions = dbOptions.OpenRecordset(strSQL, dbOpenDynaset)
- If rstOptions.EOF Then
- Me.lblOption1.Caption = "There are no items for this Switchboard page"
- Else
- While Not rstOptions.EOF
- Me("cmdOption" & rstOptions!ItemNumber).Visible = True
- Me("lblOption" & rstOptions!ItemNumber).Visible = True
- Me("lblOption" & rstOptions!ItemNumber).Caption = rstOptions!ItemText
- rstOptions.MoveNext
- Wend
- End If
- rstOptions.Close
- dbOptions.Close
- Set rstOptions = Nothing
- Set dbOptions = Nothing
- Exit Sub
- EH:
- MsgBox "There was an error listing the Options on the Form! " & _
- "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
- Exit Sub
- End Sub
Expand|Select|Wrap|Line Numbers
- " WHERE User = " & gintUserRights
One last thing. How do we determine what to do when a Menu Item is clicked?
First, each Command Button and Option Label must have in its OnClick EvenT the following:
Expand|Select|Wrap|Line Numbers
- =SelectOption(x)
Expand|Select|Wrap|Line Numbers
- Private Function SelectOption(intOption As Integer)
- On Error GoTo EH
- RestoreForm Me.Form.Name
- Const optSwitchboard = 1
- Const optFormAdd = 2
- Const optFormBrowse = 3
- Const optOpenReport = 4
- Const optExit = 6
- Const ErrCancelled = 2501
- Dim dbOption As Database
- Dim rstOption As Recordset
- Dim strSQL As String
- Set dbOption = CurrentDb()
- strSQL = "SELECT * FROM tblSwitchboards" & _
- " WHERE User = " & gintUserRights & _
- " AND SwitchboardID = " & Me.SwitchboardID & _
- " AND ItemNumber=" & intOption & ";"
- Set rstOption = dbOption.OpenRecordset(strSQL, dbOpenDynaset)
- If Not rstOption.EOF Then
- Select Case rstOption!Command
- Case optSwitchboard
- Me.Filter = "User = " & gintUserRights & _
- " AND ItemNumber = 0" & _
- " AND SwitchboardID = " & rstOption!Argument
- Me.FilterOn = True
- Case optFormAdd
- DoCmd.Close acForm, Me.Form.Name
- DoCmd.OpenForm rstOption!Argument, , , , acAdd
- GoTo SelectOption_Exit
- Case optFormBrowse
- DoCmd.Close acForm, Me.Form.Name
- DoCmd.OpenForm rstOption!Argument
- GoTo SelectOption_Exit
- Case optOpenReport
- DoCmd.OpenReport rstOption!Argument, acPreview
- GoTo SelectOption_Exit
- Case optExit
- DoCmd.Quit
- Case Else
- MsgBox "Unknown option."
- End Select
- Else
- MsgBox "There was an error reading the Switchboards Table."
- GoTo SelectOption_Exit
- End If
- SelectOption_Exit:
- On Error Resume Next
- rstOption.Close
- dbOption.Close
- Set rstOption = Nothing
- Set dbOption = Nothing
- Exit Function
- EH:
- If (Err = ErrCancelled) Then
- Resume Next
- Else
- MsgBox "There was an error executing the command. " & _
- "Please contact your Database Administrator", vbCritical
- Resume SelectOption_Exit
- End If
- End Function
And that’s it! It’s really not too complicated, but perhaps some of you may have thought that it was too daunting a task to tackle. Now you have some options.
I’d also appreciate any feedback on better ways to execute this code. I’m always willing to learn!
Hope this hepps!