We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Password protecting Access Records using Groups and Users

clarwc asked
Medium Priority
Last Modified: 2010-05-03
I have an Access database and I need to restrict record access to records based upon the user and supervisor.  I have a "Code" field that will identify the record user and also first and second level supervisors.(or group and subgroup)  For example:  The code XXX1A1 is a user and created the record.  Supervisors XXX1A and XXX1 would also need to access the record but no one else.

Code would certainly help.
Watch Question

I think you should add to your query a where clause.

Thes best solution should be creating a global function where you pass the query as parameter and you add the Where clause as following as you need.


I am VERY new to VB and I don't understand your answer.

Add a clause in your queries like following (adapt for your code)

Select ***** From ***** Where *****
and ((Mid(Security, 4, 1) = '1') or (Mid(Security, 4, 2) = '1A')
or (Mid(Security, 4, 3) = '1A1'))

You should do something like that, of course.

what waty means is something like this in your SQL string used to get the records from access

 Sqlstr = "SELECT * FROM YourTable Where Code Like '" & Trim$(Usercode) & "*'"
Set yourRS = yourDb.OpenRecordset(Sqlstr, dbOpenSnapshot)

this would get the files for all records where supervisor usercode = XXX1A and XXX1 and XXX
you need to be careful with this , a usercode of "" will have access to everything!

if this works better than watys , reject and i'll post as answer, he beat me by 1 min, spewing!!


I am not using any SQL queries.  I have a login screen.  I would like to be able to have my program look at the username when logging in to determine what access will be granted.  For example the XXX1A1 user would have access to just his files.  The XXX1A user would have access to XXX1A1, XXX1A2, XXX1A3 etc...

My guess is that I need to set up a Group and user structure to do this.  Then when the user logs on the program could determine which groups he is a member of and grant access based upon that.

you could muck around with the LIke statment a bit to specify exactly the format that you need, have a look at the LIKE (microsoft jet sql)


I guess what I was looking to try and do is build groups and users.  For example:

Group XXX1
   Sub Group XXX1A
       User XXX1A1
       User XXX1B

And then when the user logs on to the program the username determines what groups he is a member of and allows access to records accordingly.

are you using the database control table property and then controls linked to this data control?

are you using the database control table property and then controls linked to this data control?


Yes I have several forms with data controls and controls linked to the data control.
Unlock this solution and get a sample of our free trial.
(No credit card required)

woops, sorry I made some assumptions, you will have better luck with:

Dim Sqlstr As String, usercode As String
Dim yourRS As Recordset
Dim yourDB As Database
Dim ws As Workspace

Set ws = Workspaces(0)
Set yourDB = ws.OpenDatabase(Data1.DatabaseName)
'set usercode here
Sqlstr = "SELECT * FROM Functions Where AccessCode Like '" & Trim$(usercode) & "*'"
Set yourRS = yourDB.OpenRecordset(Sqlstr, dbOpenDynaset)
Set Data1.Recordset = yourRS

I didn't test it on its own before i sent it, bad huh!


Where would I put this code.  In my login screen?  And how does it effect the other data controls on my other forms.  My login screen loks like this now:

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpbuffer As String, nSize As Long) As Long

Public OK As Boolean
Private Sub Form_Load()
    Dim sBuffer As String
    Dim lSize As Long

    sBuffer = Space$(255)
    lSize = Len(sBuffer)
    Call GetUserName(sBuffer, lSize)
    If lSize > 0 Then
        txtUserName.Text = Left$(sBuffer, lSize)
        txtUserName.Text = vbNullString
    End If
End Sub

Private Sub cmdCancel_Click()
    OK = False
End Sub

Private Sub cmdOK_Click()
    'To Do - create test for correct password
    'check for correct password
    If txtPassword.Text = "" Then
        OK = True
        MsgBox "Invalid Password, try again!", , "Login"
        txtPassword.SelStart = 0
        txtPassword.SelLength = Len(txtPassword.Text)
    End If
End Sub

you could stick it in the cmdOK Click event, all you need to do is get the usercode in some way before sending the SQL. it will effect the other data type controls not at all, but all the other controls(textboxes, option buttons etc..) linked to this data control(ie. data1 in my code) will use only the subset of the records (with the conditions matching the where ..like clause)returned by the sql control and assigned to this data control.

I should note that at design time, your are esentialy doing the same thing as in my code, with the data controls propertys RecordSource et al, using an sql like this lets you filter and arrange the records before you use them.  

if you what the same rs to be used by all data controls then just put a list of them and set them all to yourRS like:

set Form1.data1.recordset = yourRS
set form2.data1.recordset = yourRS
however it would make more sense to declare yourRS as a global recordset and assign the data control to it in the form load event on each form.
hope this is what you need


Thanks for all your help.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.