Seldom does a Database Designer build a Database for themselves and themselves alone. It’s usually built around the requirements of a Customer. Many times, the Database will have multiple Users, and these various Users may (or may not) perform different functions within the Database.
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 UserID is the value returned from the OS environment that identifies who is currently logged onto the machine. All our computers require Smart Card Access, so security is double-authentication throughout. Only people who are verified users can access the Database.
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
I have established constants for the various types of Users because it is easier to remember the Type of User than it is to remember the value of their User Rights. Additionally, if I use these Global Constants throughout the Database, and need to make any changes to the actual values of these Constants, I don’t need to find every instance of the User Rights. The Global Constant will take care of that.
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
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
So, at this point, the Database knows the User’s rights within the Database, but we just have to make the system direct them appropriately.
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
You will notice that, based on the Rights of the User, a different Form opens. This is completely transparent to the User, as our “Records” staff only ever uses the Command Records Form. They don’t know any different. A Recorder never has need to access our Switchboard, but only performs duties on the PRF Review Form.
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
Aside from the first Field (“User”) I believe this Table is identical to the MS Acces Switchboard Table generated automatically. One thing I have done is Create a related Table, tblSwitchboard Commands, which is used as a Row Source for the Command Field. Its values are as follows:
Expand|Select|Wrap|Line Numbers
ID Command
0 Page Title
1 Switch Page
3 Open Form
4 Open Report
6 Quit
This just makes the Table more intuitive when looking at it in Datasheet View.
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;
MSysObjects is a System Table within MS Access, so don’t go around trying to change it! This Query only returns the Names of Forms and Reports, which are the only objects my Users have access to (PERIOD!). With a little research you can expand this query to include any other objects you desire.
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)
You can experiment as much as necessary to get this Query to return the proper list of values.
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
NB: The Argument Field can have one of four types of entries:
“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
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.
Please see the picture of my Form
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
We establish the Constant intButtons to indicate how many buttons this Form uses. It’s nice to know we can always expand if we need to.
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
This must go in the OnCurrent Event of the Form, otherwise the initial Page will not populate properly.
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
You may have noticed that the key clause for this code to work lies here:
Expand|Select|Wrap|Line Numbers
" WHERE User = " & gintUserRights
Only the Switchboard Items that apply to that specific User will show up.
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)
Where “x” is the specific number of the Command Button or Option Label (1-10). That will execute the following Function:
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
If you have your Switchboard's Table set up properly, you should never experience any errors. But, just in case, there is error handling included (because I know you ALL include error handling in ALL your code, right?)
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!
Attached Images
Main Menu.png (65.6 KB)