[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to protect records in a multi-user database.

Posted on 2006-05-10
Medium Priority
Last Modified: 2012-05-05
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.
Question by:criscagampan
LVL 11

Accepted Solution

mike1086 earned 100 total points
ID: 16649207
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

Assisted Solution

MessHallMan earned 100 total points
ID: 16649225
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.
LVL 44

Expert Comment

ID: 16649479
Each table should include a field "Owner", which identifies the User that 'owns' that record in that table, as suggested by mike1086


NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 16655234
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.

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 16900367

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

LVL 27

Expert Comment

ID: 16963388
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question