Microsoft Access Security All Versions
October 1, 2009 | 11:57 amThis Microsoft Access security log on solution will work with all versions of Access
Four steps to create this solution:
Step One
Create a new table called “tblUsers”
Add these fields:
Field Name Data Type
UserName Text
Password Text
Administrator_Rights Yes/No
Make this a hidden table and do not show hidden table in your Access program (See the video below on how to do this.)
http://www.youtube.com/watch?v=kJjQaAWHmG8
Step Two
Create a new module and paste this code into it:
Public user As Variant
Public pw As Variant
Public Function getuser()
getuser = user
End Function
Public Function getpw()
getpw = pw
End Function
Step Three
Create a new query based on this SQL statement:
SELECT tblUsers.UserName, tblUsers.Password, tblUsers.Administrator_Rights
FROM tblUsers
WHERE (((tblUsers.UserName)=getUser()) AND ((tblUsers.Password)=getPW()));
The above is done by going to the SQL View of the query and pasting in the above SQL statement.
Name the query “qryRS”
Step Four
Create a form “frmLogOn”
Add a text box control and call it “txtUserName”
Place this code in it’s After Update event:
user = txtUserName
Add a text box control and call it “txtPassword”
Place this code in it’s After Update event:
pw = txtPassword
Add a button and place this code in it’s Click event:
On Error GoTo errline
If IsNull(Me.txtUserName) Then
MsgBox “Please enter a User Name.”, vbInformation, “Missing User Name”
Exit Sub
End If
If IsNull(Me.txtPassword) Then
MsgBox “Please enter a Password for the user name ” & Me.txtUserName & “.”, vbInformation, “Missing User Name”
Exit Sub
End If
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(“qryRS”)
With rs
If .EOF And .BOF Then
MsgBox “Your log on information was incorrect. Try again.”, vbCritical, “Failed Log On”
GoTo exitline
Else
If !Administrator_Rights = False Then
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
GoTo exitline
End If
End If
End With
If chbxEditTable = -1 Then
DoCmd.OpenTable “tblUsers”, acViewNormal
DoCmd.Close acForm, “frmLogOn”
Else
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
End If
exitline:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “There was an error in the program. Please notify database administrator of the following error: Error Number: ” & Err.Number & “ ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
A demononstration of this solution is here:
http://www.youtube.com/watch?v=0RwU1roBJ6I
Hide Table Video:
http://www.youtube.com/watch?v=kJjQaAWHmG8
from youtube
Log On Demo:
http://www.youtube.com/watch?v=0RwU1roBJ6I
from youtube
Bonus article from Smart Access
Even some experienced Access developers shy away from writing SQL directly. That’s a shame, because, unless you’re willing to write SQL, you can’t use subqueries, which are a powerful tool for solving some especially thorny data retrieval problems. In this article, Mike Gunderloy introduces subqueries and shows how you can use them in Access.
http://www.vb123.com/smart/go.asp?rfdby=myap





