Re: How to use a logon screen to log into a secured Database?

From: Jeff Conrad (jeffc_at_ernstbrothers.com)
Date: 08/11/04 

Date: Tue, 10 Aug 2004 19:23:08 -0700

You're welcome Norman.
Trust me, it works. I've been using it for a long time now.
:-) 

-- 
Jeff Conrad
Access Junkie
Bend, Oregon
*282 Days Left*
<anonymous@discussions.microsoft.com> wrote in message
news:3d7201c47f30$a16783d0$a401280a@phx.gbl...
> Jeff,
>
> That looks like a smart solution!
> I haven't implemented or tested it, but it makes a lot of
> sence.
>
> thanks a lot for remebering and sharing your expierience!
>
> Kind regards
>
> Norman
> >-----Original Message-----
> >Hi Norman,
> >
> >Here is a work-around I use when I need a custom login
> screen as an entry point to a fully secured
> >Access database. You may be able to do the same. These
> are copy/pastes from previous posts of mine.
> >Just bear with me and read through everything.
> >
> >Here ya go.....
> >
> >> No, 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, see my
> >steps
> >> in a post from a couple of months ago:
> >>
> >> http://tinyurl.com/32nfg
> >
> >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.htm
> l
> >
> >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. I have not fully tested that scenario.
> >
> >Hope that helps.
> >
> >-- 
> >Jeff Conrad
> >Access Junkie
> >Bend, Oregon
> >*284 Days Left*
> >
> >"Norman Fritag" <muenchr@ozemail.com.au> wrote in message
> >news:BLdRc.280$VJ.16716@nnrp1.ozemail.com.au...
> >> Hi there,
> >>
> >> I wounder how I would go on about building a logon
> screen where as people
> >> log into a secured database?
> >>
> >> At the moment I am using a shortcut with a string
> sequence as a prompt and I
> >> am interested to get to a log in screen solution.
> >>
> >> any suggestions???
> >>
> >> Regards Norman
> >
> >
> >.
> >