Limit access to a database to a few users.

I would appreciate if someone would help me with this.

I am using Access 2007. My boss has given me a project to create a tool for our team. I'm done with most part of the project, but for the last part I want editing and updating of database restricted to a few members of our team. However others can access the database and run queries etc. through buttons on a form.

I went about creating a login form for users to get access to the database and in turn created a table with fields UserID, UserName, UserPW and a yes/no field titled IsAdmin. For those who would be limited access would log in as 'Other' user and same as password. I am then using the code to determine if the user should be treated as admin or not by looking up the IsAdmin field in the table. Following is what I have so far, but it needs correction some places and I can't figure where:

Private Sub cmdLogin_Click()

Dim xxAdmin As String

'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboUser) Or Me.cboUser = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboUser.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Check value of password in RATusers table to see if this matches value chosen in combo box
If Me.txtPassword.Value = DLookup("UserPW", "RATusers", "[UserID]=" & Me.cboUser.Value) Then
MyUserID = Me.cboUser.Value
'Close logon form and open main form
DoCmd.Close acForm, "frmLogin", acSaveNo
DoCmd.OpenForm "Audit Tool Summary"
xxAdmin = DLookup("IsAdmin", "RATusers", "[UserID]=" & Me.cboUser.Value)
If xxAdmin = 1 Then
Me.AllowEdits = False
Me.AllowLayoutView = False
End If
Else
MsgBox "Password Invalid. Please Try Again", vbCritical + vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub

Please can someone help me with this? Programming is not my cup of tea

Needs correction where? What line is giving you the problem? I don't see anything that controls who can click what button... or is that the part that os missing?

Maybe something on one of these pages will help...
http://www.access-diva.com/f7.html
http://www.access-diva.com/f10.html

Have a look at the above line of code. I assume Me.cboUser.Value is a field on your logon form. If so, you have closed the logon form before you open the "Audit Tool Summary" form, therefore Me.cboUser.Value is not available.

I'm sorry I didn't mention it clearly. It is the xxAdmin line of code that causes me a trouble.

I guess that makes sense because when you open the database the login form appears and me.cbouser.value is the combo box value where users can select their name and then type pw.

So I guess my question now is how can I apply this concept to the rest of the database? I want editing etc. be restricted to only users who have admin rights based on the table that has a yes/no for IsAdmin field?

Is it possible to achieve what I am trying to do?

You can adapt this code...
http://www.access-diva.com/f10.html
...to do that.

You could also look at this site on another forum where I developed a login database. Some of the code could may suit your needs. Look at message 44 has a sample database.

http://forums.databasejournal.com/s...e-user-login-form-(MS-Access-2000-2003)/page3

I'd just modify your design to perform all of your login checks, password etc and admin check:
xxAdmin = DLookup("IsAdmin", "RATusers", "[UserID]=" & Me.cboUser.Value)
before you close the login form and then open the main form allowing edits etc according to the value of xxAdmin

David

I tried using one of the links you posted above. The following SQL code in it isn't working for me:

strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
" SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"
DoCmd.RunSQL strSQL
Forms("frmMainMenu").Tag = "Continue"
DoCmd.Close acForm, Me.Name

I get the Compile Error, Expected: End of Statement and the debug highlights the "UserName" for me. I can't figure out what is wrong since the code seems to be continued properly on the next line. Is it the quotations? Appreciate the help so far.

Type...


...in the Immediate window and press Enter. What happens?

I get my Windows Username. (One through which I am logged in on the machine)

Okay, then do you have a tblUsers with these fields...

tblUsers
uNetworkID
uFirstName
uLastName
ueMail
uLastLogon
uLogonCount
uSecurityID
uActive

If not, then you need one. If you are using another table you need to change the names accordingly.

I have it under the name RATusers and different field names (slightly). But I altered the code accordingly and the error is still the same. Below is the altered code.

strSQL = "INSERT INTO RATusers ( NetworkID, FirstName, LastName, LastLogon, LogonCount, SecurityID, Active )" & _
" SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', Now(), 1, 9, True"
DoCmd.RunSQL strSQL
Forms("frmLogin").Tag = "Continue"
DoCmd.Close acForm, Me.Name

Why is it erroring out the UserName?

Try ...


SQL INSERT INTO Statement

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Click to expand...

The code went through. Thank you for the help.

But may I ask what the error was for?

Firstly you wanted to put single quotes around Environ("UserName").

Secondly you also wanted to put it outside the string in the same way you did with Me.txtFirstName.

Thirdly you wanted Values() instead of SELECT.

@Nigel,

Hmm, never had that problem before, at least, not with that piece of code but I see I will have to put a side note on my page, okay to cite you?

You can if you like but all I did was to use standard SQL syntax for inserting values into a record, for which I quoted a reference, and tidied it up a bit.

Nothing special, just a few years of experience.

LOL, well, since this is the first time I have run into that I'll give you the credit.

Oh. You'll have me blushing.

I'm still working on getting the desired user level security through one of the links you sent me. The following code errors out in the IsDevelepor section as 'Sub or Function not defined'. (ChangeProperty I guess...)

Private Sub Form_Load()
On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim myQuery As String

myQuery = "SELECT * FROM RATusers WHERE NetworkID = '" & Environ("UserName") & "'"

Set db = CurrentDb()
Set rst = db.OpenRecordset(myQuery, dbOpenDynaset, dbSeeChanges)

If Not rst.BOF And Not rst.EOF Then
rst.Edit
rst.Fields("LogonCount") = rst.Fields("LogonCount") + 1
rst.Fields("LastLogon") = Now()
rst.Update

Me.txtSecurityID = rst.Fields("SecurityID")
Me.txtDelete = rst.Fields("Delete")
Me.txtUserID = rst.Fields("UserID")
Me.txtOverride = rst.Fields("SpecialPermissions")
Me.txtPassword = rst.Fields("Password")

DoEvents
Else
DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal
Me.Dirty = False
Me.Visible = False

Do Until Me.Tag = "Continue"
DoEvents
Loop
End If

Set rst = Nothing
db.Close
Set db = Nothing

If IsDeveloper Then
ChangeProperty "AllowBypassKey", dbBoolean, True
Else
ChangeProperty "AllowBypassKey", dbBoolean, False
End If

Form_Load_Exit:
Exit Sub:

End Sub

Any thoughts of working around this one?