You cannot change the default Access login screen that appears with a secured database. This object
is not exposed to the developer.
You can, however, make your own custom login form in a second unsecured database. Your users will
probably never know the difference. For a detailed walk-through on how to set this up, follow these
Step-By-Step 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
2. 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).
3. Create a command button called cmdLogin and one called cmdExit.
4. Copy/paste this code into the click event for cmdLogin:
Private Sub cmdLogin_Click()
On Error GoTo ErrorPoint
Dim strPath As String
Dim strAccDir As String
Dim strAccPath As String
If IsNull(Me.txtUserName) 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 IsNull(Me.txtPassword) 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
You will need to enter the full path to the database file and the MDW file in the appropriate
places. Make sure to exclude the <> marks. They are only there for illustration.
5. Add this code to the click event for cmdExit:
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
6. 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.
- Compile the code, save the module and name it modHideAccessWindow.
- Open your login form and go to Design View.
- Open the code behind the form and copy this code into the Form's Open event:
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
7. Make all the formatting changes you wish to the form. Don't make it the Startup form yet!
8. 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 and then
hit the Login button. You should see 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.
9. Close everything and then re-open the login database. Open the form and test with an invalid user
name/password combination. You will be presented with the default Access login form. To me it's no
big deal nor to my users. When they ask what it is I simply say it's the "gatekeeper." "You must
have entered your information incorrectly." They completely understand and don't even give it a
second thought. No problems at all.
10. Once you're 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.
11. Make a backup of this MDB file. Now make an MDE file from this MDB. Open up the MDE file and
make it the Startup form by going to Tools--Startup. Close the database and the re-open.
Double-check to make sure everything is working properly one more time. Close everything. Now hold
down the Shift key and open the MDE file again to bypass the Startup. Go back to Tools-Startup and
uncheck everything. Probably not really needed, but let's do it anyway. Close the MDE file.
12. Download MVP Albert Kallal's nifty database utility that will turn off the Shift key bypass:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html
Look for: "By Pass Shift Key Code"
13. Disable the shift key bypass on the MDE file using the utility. Open the MDE file one last time
and you should not be able to stop the form from displaying. Only the form should show in the middle
of the screen with no Access Window. The Exit button to completely close the database is necessary
because you will see NOTHING in the Task Bar for this database!
14. Pat yourself on the back for a job well done.
This setup solved several problems for me:
- 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.
- I needed an easy way to back up the BE tables (this is single workstation setup).
A simple button on the form copies the whole BE to a folder and date stamps it.
Since they're not accessing the tables from this unsecured database I don't have to worry
about corruption. Even if by chance the tables are being accessed my error handling
stops the process and flashes up a nice message box.
- I needed a way for the users to enter a Product Code before using the program.
- I wanted to have a EULA form pop up first and have them agree to it before accessing
the main database program.
It works really, really nice for me.
I have had some success getting the User Name text box to display the name of the last user to open
the database by reading a registry value. I'm not sure if you can set up a combo box to display all
the users in the MDW file for the secured file from the unsecured. It may be possible, I just have
not tried to make that work before. I may play around with this for fun.
Good luck and I hope it helps!
