Thanks Jeff, this is exactly what I was looking for. I'm
leaving for vacation in a few minutes so I won't be able
to try this out for about a week, but I'm sure it will
work just fine. Thanks again!
-----Original Message-----
Hi Dave,
OK, here's the steps necessary.
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.ht ml
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
