How to protect records in a multi-user database.

Posted on 2006-05-10
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

    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
    LVL 7

    Assisted Solution

    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

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



    Author Comment

    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

    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

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now