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.
criscagampanAsked:
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
Table

use

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

where <<user_name>> is the field in your Table
0
 
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.
0
 
Arthur_WoodCommented:
Each table should include a field "Owner", which identifies the User that 'owns' that record in that table, as suggested by mike1086

AW

0
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.





0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
criscagampan,

Assumptions:
Table Name is: tblLogin
Field1: UserName
Field2: Password


Form
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
    Else
        'DO NOT Log them in!
    End If

End Sub

Hope this helps

0
 
jjafferrCommented:
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.

jjafferr
EE Cleanup Volunteer
0
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.