After further checking, the most important post was not archived on Google, probably because I had a
small screen shot attachment on it. Here are all the steps again:
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 ErrorHandler
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
Dim strPath As String
strPath = Chr(34) & "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" & Chr(34) & " "
_
& Chr(34) & "<Full Path To Your Database Here>" & Chr(34) & " " _
& "/wrkgrp " & Chr(34) & "<Full Path To Your 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
ErrorHandler:
fncErrMessage err.Number, err.Description
Resume ExitPoint
End Sub
You will need to enter your full path to the database file and MDW file in the appropriate places.
(Also adjust your error handing instead of mine.)
5. Add this code to the click event for cmdExit:
Private Sub cmdExit_Click()
On Error GoTo ErrorHandler
DoCmd.Quit
ExitPoint:
Exit Sub
ErrorHandler:
fncErrMessage err.Number, err.Description
Resume ExitPoint
End Sub
(Again, adjust your error handing instead of mine.)
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)
Me.Visible = True
fSetAccessWindow (SW_HIDE)
End Sub
(Add in some error handling code)
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"
If set up properly using the DDL argument only users who are members of the Admins group will be
able to turn off this feature. Details on that can be found here by the Mighty MichKa:
http://www.mvps.org/access/general/gen0040.htm
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. I hope I didn't miss any steps.
I'm sure I'm going to get yelled at for this (...head ducking...), but I attached a screen shot to
demonstrate. A picture is worth a thousand words (although I appear to be getting close to that!).
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.
WHEW!!! I should be a Microsoft Tech writer!
Good luck and I hope it helps!
I then forgot something important so here is the second post:
I knew I would miss something.
In the code for the cmdLogin you need to specify the exact location to the Msaccess.exe file. So
double check that location on your machine against the code I gave you before proceeding.
You could also use code to find the location yourself. Something like so:
' Code Start
Private Sub cmdLogin_Click()
On Error GoTo ErrorHandler
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
Dim strPath As String
Dim strAccDir As String
Dim strAccPath As String
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
ErrorHandler:
fncErrMessage err.Number, err.Description
Resume ExitPoint
End Sub
' Code End
(Again, substitute your own error handling)
This may not work, however, if more than one Access version (and/or a runtime) is installed on the
same machine.
Hope that helps.
