How to protect records in a multi-user database.

I developed an MSAccess database which hold records of daily work accomplishments. This is shared among our small group of less than 15 people. Before a user add or edit or delete a record, one must enter a valid Username and Password first on a startup login form.
Once validated, a common form appears and could start navigating and adding or deleting data.
How can I make my form work in such a way that a user can edit or delete records ONLY those that "belong" to him.
Who is Participating?
mike1086Connect With a Mentor Commented:
Use a query as the recordsource. The username in the recordsource should equal the current username

So instead of a recordsource which looks like


SELECT * FROM Table Where Table.<<user_name>> = CurrentUser()

where <<user_name>> is the field in your Table
MessHallManConnect With a Mentor Commented:
Since you are having all users login with a username and password to the database. You can save the information in a global variable.  This global variable can be store with each database record as the record is saved.  If a user goes to edit that particular record, can you check to see that the global variable equals the stored one to see if permission to edit/delete should be granted.
Each table should include a field "Owner", which identifies the User that 'owns' that record in that table, as suggested by mike1086


Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

criscagampanAuthor Commented:
What I have actually is a table were users register their unique Username , Password, Name, PhoneNo  and other info. Creating that query from the table doesn't seem to work or maybe I missed something. I'm new in access so I'll very much appreciate it if you could give me more details on how to do it.

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

Table Name is: tblLogin
Field1: UserName
Field2: Password

Log in button Name: cmdLogin
Textbox1 Name: txtUserName
Textbox2 Name: txtPassword

(I believe LSMConsulting posted this a while ago, I have modified it slightly to use in my own projects, and it works well)

Private Sub cmdLogin_Click()

Dim rst             As New ADODB.Recordset
Dim strAttUserName  As String
Dim strAttPword     As String

strAttUserName = Me.txtUserName
strAttPword = Me.txtPassword

    rst.Open "SELECT UserName FROM tblLogin WHERE UserName = '" & strAttUserName & "'" & " AND Password = '" & strAttPword & "'", CurrentProject.Connection

    If Not (rst.EOF And rst.BOF) Then
        'Log them in
        'DO NOT Log them in!
    End If

End Sub

Hope this helps

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: mike1086 {http:#16649207} & MessHallMan {http:#16649225} & Arthur_Wood {http:#16649479} & boag2000 {http:#16900367}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.