|
Creating A Customized Login Screen For A Secured Database
By Jeff Conrad, Access MVP 
Note: These instructions were written for Microsoft Access versions 97, 2000, 2002, and 2003. This has not been tested with multiple Access versions installed on one machine and/or just a Runtime installation.
(Bend, OR -- Apr. 2, 2005) A question that comes up from time to time in the Access newsgroups is "How do I change the appearance of the login screen that comes up when using a secured Access database? I would like to customize that form."
The short answer to this question is that you cannot change the default Access login screen that appears with a secured database. This object is not exposed to the developer and probably never will. This is unfortunate from a developer's standpoint, but understandable since this object is integrated into the Access/Jet Security Model.
So how do you work around this limitation? One option available to the developer is to make your own custom login form in a second unsecured database. Using this new form, you can customize it any way you like with buttons, graphics, other controls, et cetera. The possibilities are endless. At the heart of this login form will be two text boxes that will be used to capture the user name and password information. A Login button will then launch the secured database file using the workgroup file you specify, pass in the appropriate User ID information, and then close the unsecured database. Your users will probably never know that they are using two (or more) database files.
Create A Custom Login Form
To illustrate how this process works, follow these instructions:
1. Create a new blank, unsecured database.
2. Create a new form with the following properties:
- Pop up: Yes
- Modal: Yes
- Auto Center: Yes
- Allow Edits: Yes
- Allow Deletions: No
- Allow Additions: Yes
- Scroll Bars: Neither
- Record Selectors: No
- Navigation Buttons: No
- Dividing Lines: No
- Border Style: Thin
- Control Box: Yes (for now)
- Min Max Buttons: No
- Close Button: Yes (for now)
- Shortcut Menu: No
3. Add a text box for the user name (txtUserName) and one for the password (txtPassword). Set the Input Mask for the password text box to "Password" (no quotes though).
4. Create a command button called cmdLogin and one called cmdExit.
5. Copy/paste this code into the click event for cmdLogin:
'************Code Start************
Private Sub cmdLogin_Click()
On Error GoTo ErrorPoint
Dim strPath As String
Dim strAccDir As String
Dim strAccPath As String
If Len(Nz(Me!txtUserName, "")) = 0 Then
' User Name box is empty
MsgBox "Please enter your User Name before continuing.", _
vbInformation, "Enter User Name"
Me.txtUserName.SetFocus
GoTo ExitPoint
End If
If Len(Nz(Me!txtPassword, "")) = 0 Then
' Password box is empty
MsgBox "Please enter your Password before continuing.", _
vbInformation, "Missing Password"
Me.txtPassword.SetFocus
GoTo ExitPoint
End If
strAccDir = SysCmd(acSysCmdAccessDir)
strAccPath = strAccDir & "MSACCESS.EXE"
strPath = Chr(34) & strAccPath & Chr(34) & " " _
& Chr(34) & "<Full Path To Database File Here>" & Chr(34) & " " _
& "/wrkgrp " & Chr(34) & "<Full Path To MDW File Here>" & Chr(34) & " " _
& "/User " & Chr(34) & Me.txtUserName & Chr(34) & " " _
& "/Pwd " & Chr(34) & Me.txtPassword & Chr(34)
Shell strPath, vbMaximizedFocus
Application.Quit
ExitPoint:
Exit Sub
ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
'************Code End************
*** Important note: ***
You will need to enter the full path to the secured database file and the workgroup information (MDW) file in the appropriate places. Make sure to exclude the <> marks when you enter the path. They are only there for illustration.
If you examine the code carefully you will notice that we are building up a very long text string that mimics what you would see in a desktop shortcut link for a secured database. We use the SysCmd(acSysCmdAccessDir) function to determine the path to the current msaccess.exe file.
6. Now add this code to the click event for cmdExit:
'************Code Start************
Private Sub cmdExit_Click()
On Error GoTo ErrorPoint
DoCmd.Quit
ExitPoint:
Exit Sub
ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
'************Code End************
This code will simply close the unsecured database. We need this just in case the user opened this file by mistake. This code is also needed especially if you use the next step below to hide the Access window.
7. This step is optional. I prefer to hide the Access window for this unsecured database. To the user all they see is a single floating form in the middle of their screen. This step is not required in order to make this custom form, so use this only if you desire to see the same effect. To hide the Access Window go to this link:
http://www.mvps.org/access/api/api0019.htm
- Copy/paste all the code to a new standard module in the unsecured database file.
- Compile the code, save the module, and name it basHideAccessWindow.
- Open your login form again and go to Design View.
- Open the code behind the form and copy/paste this code into the Form's Open event:
'************Code Start************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint
Me.Visible = True
fSetAccessWindow (SW_HIDE)
ExitPoint:
Exit Sub
ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
'************Code End************
Why the Me.Visible = True line?
That is needed because there can sometimes be a timing problem with the fSetAccessWindow code. The Me.Visible line assures that the form is visible to Access before we hide the Access window.
8. Make all the formatting changes you wish on the form. Do not make it the Startup form just yet, though.
9. Compile the code, save and close the form. Then open it up to test. The Access window should be hidden and only your form should be showing. Test entering a valid user name and password for the workgroup file used to secure the secured database file and then hit the Login button. You should see just a bit of screen flickering and then your secured database should open, followed immediately by the unsecured one closing. I have pretty slow processors and this happens really quickly. On faster processors it should be almost instantaneous.
10. Close everything and then re-open the login database. Open the form and test with an invalid user name/password combination for the workgroup. You will be presented with the default Access login form since the login failed. To me it is no big deal, nor to my users, since from time to time we all make mistakes when entering user names and passwords. When my users would first ask what this other form was for, I would simply tell them that this screen is the Gatekeeper. I tell them that they must have entered their information incorrectly so they need to try again. They completely understand and do not even give it a second thought. I have had no problems or complaints from the users when following this setup. The users simply re-enter their login information on the default Access login screen and are then allowed into the secured database.
11. Once you are sure everything is working properly and you are happy with the layout, go back to the Form's Properties and make the following changes:
- Control Box: No
- Close Button: No
Save and close the form.
12. Make a backup of this MDB file since we will be creating an MDE file from this master MDB. Whenever you are going to make an MDE file, always make a backup in case something unforeseen happens in the conversion process and your MDB file is rendered unusable. Now make an MDE file from this MDB so you can distribute a file to your users that will not allow them to tamper with your code. Once completed, open up the MDE file and make this login form the Startup form by going to Tools | Startup and selecting the name of this form from the drop-down list in the "Display Form" field. Close the database now, re-open the file, and double-check to make sure everything is working properly one more time. Close everything when you have completed your testing. This time we will hold down the Shift key and open the MDE file again to bypass the Startup options. Go back to Tools | Startup and uncheck everything. This step helps to make sure that the user will not accidentally trigger something that would cause some support calls. Close the MDE file once again.
13. Now we need to disable the shift key bypass before we distribute the MDE file to your users. You can either do this manually if you have the code or you can download Access MVP Albert Kallal's nifty database utility that will turn off the shift key bypass for any selected database application:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
Look for the file called "By Pass (sic) Shift Key Code."
14. Disable the shift key bypass on the MDE file using the utility or by using code you may already possess. Open the MDE file one last time for testing. You should not be able to stop the form from displaying, even if you hold down the shift key. Only our custom login form should show in the middle of the screen with no Access window. The Exit button is necessary to completely close the database. You will see nothing in the Task Bar for this database, so without the Exit button, the Windows Task Manager would otherwise be needed to shut down the database application.
That concludes the basic steps necessary to create your own customized login form.
A Custom Login Form Can Provide Multiple Solutions
Creating my own custom login form solved several problems for me:
1. I needed a way to log into not only the main program, but a sample database as well. A simple checkbox on my custom login screen accomplishes that. If the user ticks the checkbox on the form, then my code opens a different MDB file that is a mirror image of my main application. This sample database is used for training purposes so users can become comfortable with using the application before using live data.
2. I needed an easy way to back up the back end (BE) data tables. A simple button on the form copies the whole BE file to a specific folder and date stamps it. Please note that this particular setup was for a single workstation application and would not be applicable for a multi-user scenario. Since my custom login form is in an unsecured database file with no links to the BE data tables, I do not have to worry about copying the file while it is in use. Even if by chance the tables are being accessed, my error handling stops the process and flashes up a nice message box for the user. This is a very easy process for the users to make a backup.
3. I needed a way for the users to enter a Product Code before using the program. After considering many possibilities for implementation, I decided to utilize my custom login form. Clicking a button on the form actually takes the user to a completely different database file where the Product Code information is entered. Once a correct Product Code is supplied, the users can access the main application file.
4. I wanted to have an End User License Agreement (EULA) form pop up first and have the user agree to it before accessing any areas of the program. Having this custom form in an unsecured database proved extremely valuable in this instance. When the application is first deployed, my EULA form is set as the Startup form under Tools | Startup. When the user first opens the file he is presented with the EULA information. There is a button for "I Agree" and one for "I Disagree." Naturally, if the user chooses not to agree to the terms, the application is simply closed. If the user clicks the "I Agree" button then my custom login form is presented. However, the EULA form will continue to be the first form seen unless they also tick a check box on the form that says, "Do not display this information again." If the user clicks the "I Agree" button and the checkbox, I have code that changes the Startup form under Tools | Startup from the EULA form to the login form. This is easy to implement in an unsecured database since all users are members of the Admins group (because they are silently logged in as the Admin user). Only members of the Admins group can change the Startup form, so this presented a problem with my secured database. Moving the EULA form and code to my unsecured login database not only proved to be a better fit, but also a lot easier for coding purposes.
Below is an actual screen shot of a custom login form from one of my applications that utilizes the techniques we have discussed. The users are completely unaware that they are actually using four different database files!
You can now see how all of the elements from our discussion are tied together. The only area not mentioned was the button with the "question mark." I use this button to serve as a "help" screen for the users and to explain the login process.
Working Example Provided – See This Technique In Action
If you would like to see a working example of this custom login form, I have provided two sample files. There is one file that can be used with Access 97 and one file that can be used with Access 2000, 2002, or 2003. Please make sure you download the correct file version to match your Access version.
Login97.ZIP
Login2000.ZIP
A few points to make on the download file:
1. The sample file does not have all the buttons and code you see from the screen shot above. This file just contains a login form with the User Name/Password fields, a sample Help button, the Login button, and the Exit button.
2. I have commented out the specific code to launch the secured database in this sample file. Simply fill in the appropriate file paths, remove the message box code, and uncomment the "launch" code when you are ready to test the procedure. You could, by the way, also enter those paths into table fields and grab that information through code.
3. Along with this sample file, I also included the EULA form and code that I discussed in the second part of this article. The EULA form is not required to make this custom login form. I just included it in case you were curious to see how the procedure worked. Study the code behind the form to see how we manipulate the Startup Form property. This EULA form could easily be integrated into your existing applications. The EULA information simply comes from a table in the database.
4. To see the forms and table in Design View, simply hold down the Shift key while you open the database to bypass the Startup options. I have not disabled that ability in the sample file, but make sure you do not forget that step before deploying the database to your end users.
5. I am sure I will be asked about this, so I'll answer the question now. Yes, you can certainly display a "Splash" screen with your company logo using this setup. Just manipulate the Startup Form Properties and/or use code to set up the chain of events you wish to see.
I hope this article gives you some ideas for your own projects. Use your imagination and have fun!
Jeff Conrad
Access MVP
Access Junkie
Copyright © 2005 - 2006 Jeff Conrad. All rights reserved.
 Top
For questions regarding this tutorial, please contact Jeff at:
You can read about your favorite expert contributors, or check out Jeff's MVP profile and Jeff's Web site:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Top
Visitors since 9 June '05:
|
