Creating seperate User permissions without VBA

[ToryM]

Hi Everyone

I was wondering if someone would know how to do this or if it is possible.

So I have a database set up and working (so far!). The problem is I need it to be only editable by 1 user, but allowing multiple other users to access it and view the records- just not able to add/edit/delete them. I would prefer to not use VBA as I don't seem to be able to grasp it all that well and my database is set up entirely of Macros so I can pass it on to the future administrator who hasn't got any idea on programming either- kind of like the Blind leading the Blind for us right now!

I was thinking maybe splitting the database would be the easiest solution from looking at the other threads but I don't really know how to go about it or if it actually is the ideal way Am I on the right track or has my train derailed altogether?

The other question I had about the Split database would be- Does having a split database mean the data needs to be updated in 2 tables or am I taking Splitting databases too literally?

Thanks in advance for any help/ clarification anyone can give! You have no idea how much I appreciate it!

Tory

 

[BlueIshDan]

ToryM

With little research done, I've tested the Split Access Database method. It seems like the front-end copy of the access database is still fully editable and I have not seen a way to make tables and/or fields read-only on one side.

Other methods I have tried include:
Manually Linking the table to a front-end access database as read-only (Failed)
Connecting to a read-only Access Database ODBC connection. (Failed)

I have a question for you... are you using any forms in this database or is this database manually managed all of the time?

 

[AOB]

Hi ToryM,

I'll answer your second question first... Your database comprises two basic 'sections'. You have the tables, which contain all your records. And you have your forms, reports, queries and (if you used VBA) modules, which is basically the interface you use to manipulate that data. When you split the database, you separate these two into separate files. So the tables go into one (called the 'backend') and your forms, reports and queries go into another (called the 'frontend')

This has advantages over a standalone (unsplit) database in that you can create multiple copies of the frontend and distribute them to several people, but they all link back to the same backend. The tables that appear in your frontend are linked tables - they are not actually in the frontend, they are only linked. So several people can open their own frontend and view the same common tables (and edit the records) Everyone has visiblity to the same data but no one person locks everybody else out.

So to answer that second question...

Does having a split database mean the data needs to be updated in 2 tables

...no. There's only one table - in the backend. What you see in the frontend (in every frontend) is just a link to that same table.

For the first question - I would say it is easier to do it with VBA. I do something similar with all my DB's but I keep a table of users and have boolean (Yes/No) fields for various types of permissions (i.e. CanEdit, CanDelete etc.) Then you can write a very simple function which can look up the username against this table to see if they have the appropriate permission, prior to taking an action, and either allow it or deny it.

Of course, you have to be able to identify the current user - do they have to log in or do you use their Windows environment login? How do you know who is using it? Also, can this '1 user' change over time? Could it be me today but somebody else in 6 months? Could it change to 2 users at some point? To incorporate this flexibility, maintaining a user table makes it a lot easier. But it involves an element of VBA.

The other option would be to develop two frontends. Make one such that edits can be made and give this to your one designated user. Make the other such that edits cannot be made and give copies of it to everybody else. I personally don't like this option as it means you have to maintain two frontends. But if you don't anticipate there being much development down the line, it would achieve your goal without requiring VBA.

VBA isn't as bad as you think it is - you will get plenty of help in this forum (specifically in the 'Modules and VBA' section) if you get stuck.

Best of luck with it!

Al

 

[vbaInet]

VBA isn't as bad as you think it is - you will get plenty of help in this forum (specifically in the 'Modules and VBA' section) if you get stuck.

That's even the second most popular forum.

So ToryM, there's no macro way to do what you're trying to do but if all your forms are bound and you're not using running Insert/Delete/Update queries by the click of a button, then you can:

1. Create two sets of Front Ends
2. Set the following properties accordingly in both. One will have all set to Yes and other will have all properties set to No and you do that for each form. The properties are:

Allow Additions
Allow Deletions
Allow Edits
Recordset Type - Snapshot for readonly and Dynaset for read/write.

But then again, do you really want to have to manage two sets of front ends every single time an update is made? I would doubt it.

 

[ToryM]

ToryM

With little research done, I've tested the Split Access Database method. It seems like the front-end copy of the access database is still fully editable and I have not seen a way to make tables and/or fields read-only on one side.

Other methods I have tried include:
Manually Linking the table to a front-end access database as read-only (Failed)
Connecting to a read-only Access Database ODBC connection. (Failed)

I have a question for you... are you using any forms in this database or is this database manually managed all of the time?

Hi Dan

Thanks for the help! I have several forms in the database that all link to each other from a Main Form. Most of them are pop-up or subforms in the Main Form.

 

[ToryM]

Hi ToryM,

I'll answer your second question first... Your database comprises two basic 'sections'. You have the tables, which contain all your records. And you have your forms, reports, queries and (if you used VBA) modules, which is basically the interface you use to manipulate that data. When you split the database, you separate these two into separate files. So the tables go into one (called the 'backend') and your forms, reports and queries go into another (called the 'frontend')

This has advantages over a standalone (unsplit) database in that you can create multiple copies of the frontend and distribute them to several people, but they all link back to the same backend. The tables that appear in your frontend are linked tables - they are not actually in the frontend, they are only linked. So several people can open their own frontend and view the same common tables (and edit the records) Everyone has visiblity to the same data but no one person locks everybody else out.

So to answer that second question...



...no. There's only one table - in the backend. What you see in the frontend (in every frontend) is just a link to that same table.

For the first question - I would say it is easier to do it with VBA. I do something similar with all my DB's but I keep a table of users and have boolean (Yes/No) fields for various types of permissions (i.e. CanEdit, CanDelete etc.) Then you can write a very simple function which can look up the username against this table to see if they have the appropriate permission, prior to taking an action, and either allow it or deny it.

Of course, you have to be able to identify the current user - do they have to log in or do you use their Windows environment login? How do you know who is using it? Also, can this '1 user' change over time? Could it be me today but somebody else in 6 months? Could it change to 2 users at some point? To incorporate this flexibility, maintaining a user table makes it a lot easier. But it involves an element of VBA.

The other option would be to develop two frontends. Make one such that edits can be made and give this to your one designated user. Make the other such that edits cannot be made and give copies of it to everybody else. I personally don't like this option as it means you have to maintain two frontends. But if you don't anticipate there being much development down the line, it would achieve your goal without requiring VBA.

VBA isn't as bad as you think it is - you will get plenty of help in this forum (specifically in the 'Modules and VBA' section) if you get stuck.

Best of luck with it!

Al

Aw Al thanks for the explanation- that has definitely cleared things up for me on Split databases!

I'll go with your suggestion on creating a user login table but I think I'll have to go with creating my own user login details. As for the 1 user to manage it all- they can change, and it is currently possible that there could be 2 users maintaining the records since this might be going to another student to do daily updates.

But I guess I'm going to have to face my fears with VBA

Thank you for the help again!
Tory

 

[ToryM]

Hi ToryM,

I'll answer your second question first... Your database comprises two basic 'sections'. You have the tables, which contain all your records. And you have your forms, reports, queries and (if you used VBA) modules, which is basically the interface you use to manipulate that data. When you split the database, you separate these two into separate files. So the tables go into one (called the 'backend') and your forms, reports and queries go into another (called the 'frontend')

This has advantages over a standalone (unsplit) database in that you can create multiple copies of the frontend and distribute them to several people, but they all link back to the same backend. The tables that appear in your frontend are linked tables - they are not actually in the frontend, they are only linked. So several people can open their own frontend and view the same common tables (and edit the records) Everyone has visiblity to the same data but no one person locks everybody else out.

So to answer that second question...



...no. There's only one table - in the backend. What you see in the frontend (in every frontend) is just a link to that same table.

For the first question - I would say it is easier to do it with VBA. I do something similar with all my DB's but I keep a table of users and have boolean (Yes/No) fields for various types of permissions (i.e. CanEdit, CanDelete etc.) Then you can write a very simple function which can look up the username against this table to see if they have the appropriate permission, prior to taking an action, and either allow it or deny it.

Of course, you have to be able to identify the current user - do they have to log in or do you use their Windows environment login? How do you know who is using it? Also, can this '1 user' change over time? Could it be me today but somebody else in 6 months? Could it change to 2 users at some point? To incorporate this flexibility, maintaining a user table makes it a lot easier. But it involves an element of VBA.

The other option would be to develop two frontends. Make one such that edits can be made and give this to your one designated user. Make the other such that edits cannot be made and give copies of it to everybody else. I personally don't like this option as it means you have to maintain two frontends. But if you don't anticipate there being much development down the line, it would achieve your goal without requiring VBA.

VBA isn't as bad as you think it is - you will get plenty of help in this forum (specifically in the 'Modules and VBA' section) if you get stuck.

Best of luck with it!

Al

Aw Al thanks for the explanation- that has definitely cleared things up for me on Split databases!

I'll go with your suggestion on creating a user login table but I think I'll have to go with creating my own user login details. As for the 1 user to manage it all- they can change, and it is currently possible that there could be 2 users maintaining the records since this might be going to another student to do daily updates.

But I guess I'm going to have to face my fears with VBA

Thank you for the help again!
Tory

EDIT: Forgot to also ask/mention- I had a little VBA in my database before and it kept causing a macro in on of the pages to stop working after the whole database was shutdown completely- will adding VBA cause this to happen again or was it just because the VBA code was related to the same thing as the Macro?

Thanks
Tory

 

[AOB]

No problem - take it slow and if you run into difficulty, start a new thread specific to the issue and you are almost certain to find a solution.

As regards your last question - difficult to say without knowing what the VBA was and also what the macro was - but generally, no, I wouldn't worry about having both in a database. I usually have both although, to be honest, I tend to restrict the number of macros as much as possible. If / when you suspect they are conflicting, hit the forum and we'll figure it out.

 

[BlueIshDan]

Sorry but just one idea to throw at you. Instead of creating a user login screen and adding one more login and password to the users' life.

You could have it default to read only and make it so that you can enter a code to make it editable.

In my recent experience. People tend to get rattled up when they learn they have to remember another password.

 

[AOB]

Strongly agree with BlueIshDan on that one! It's also something else to add to your list of things to maintain (what if someone forgets their password?)

I find the easiest way is to use the Windows environment login and use that to identify the current user.

 

[BlueIshDan]

Public Function GetUsername() As String
    If Not IsNull(Environ("USERNAME")) Then: GetUsername= Environ("USERNAME")
End Function

Then place this in your onload event of your forms.

      If GetUsername() <> "[your-username]" Then
           ' Set controls to read-only
      End If

 

[ToryM]

My goodness! Thank you both!

Unfortunately I think I need to keep to a user logon sort of scenario since the plan was that only one department in my work could use the database and, since there are people who move around the company, if there was just a password set on it then they could easily gain access to it when they move departments. I don't see why this would be a problem but that's pretty much the set up I would have to keep with.

BlueIshDan I'm going to try your code here and see how it goes but can I just ask, is that username setup for the windows Enivronment login or would it work with a user login details table?

 

[BlueIshDan]

That will gather the username the user used to log-in to the machine with.

You could also simply keep a list of allowed to use usernames in a table.
id, name, group-id[ or is_admin ]

and on load of all forms

    If DCount("username", _
              "users", _
              "username = """ & GetUsername() & """") = 0 Then: _
              ' Exit Application
              
    If DCount("username, is_admin", _
              "users", _
              "username = """ & GetUsername() & """" + _
              " AND is_admin = TRUE") = 1 Then: _
              ' Set Controls to editable

 

[ToryM]

Hi guys!

Just a quick update: User permissions are working!
I have a table with the users, their passwords, and their user permissions (I.e Admin, User with Editing, and Read-Only) and created a form to load on start up so once they enter their name and password it will set the permissions. It looks something like this:

On the Log In Form:

Option Compare Database
Option Explicit
Public intLoginAttempts As Integer
Private Sub CMDlOGin_Click()
If IsNull(Me.cboUserName) Then
MsgBox "You need to select a username", vbCritical
Me.cboUserName.SetFocus
Else
If Me.txtPassword = Me.cboUserName.Column(1) Then
DoCmd.OpenForm "Start up Page"
Me.Visible = False
Else
Dim NoAllowedTries As Integer
NoAllowedTries = 3
intLoginAttempts = intLoginAttempts + 1
If intLoginAttempts = NoAllowedTries Then
MsgBox "too many tries", vbOKOnly
Application.Quit
End If
MsgBox "Password Invalid You have " & NoAllowedTries - intLoginAttempts & " remaining"
End If
End If
End Sub
Private Sub Form_Load()
intLoginAttempts = 0
End Sub

And On the Start up form:

Private Sub Form_Load()
Dim varX As Variant
varX = DLookup("[User Permissions]", "tblUserLogon", _
"[UserName] = '" & Forms!frmLogon!cboUserName & "'")
If varX = "ADMIN" Then
Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True
ElseIf varX = "USER WITH EDITING" Then
Me.AllowEdits = True
Me.AllowAdditions = False
Me.AllowDeletions = False
Else
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
End If
End Sub

So now it's working for that page, I just now have to get it done for the rest of them! Thank you for your help!

Tory

 

[AOB]

Excellent - well done ToryM!

If I read this right, you're planning to use the same code in the Load event of multiple forms. If so, you might consider putting it into a separate module as a generic function, and then just pass the form object to it each time? So for example :

Public Sub SetPermissions(frm as Form)
 
    With frm
 
        Select Case DLookup("[User Permissions]", "tblUserLogon", _
                            "[UserName] = '" & Forms!frmLogon!cboUserName & "'")
 
            Case "ADMIN"
 
                .AllowEdits = True
                .AllowAdditions = True
                .AllowDeletions = True
 
            Case "USER WITH EDITING"
 
                .AllowEdits = True
                .AllowAdditions = False
                .AllowDeletions = False
 
            Case Else
 
                .AllowEdits = False
                .AllowAdditions = False
                .AllowDeletions = False
 
        End Select
 
    End With
 
End Sub

And then you can just put the same line on the Load event of each form in play :

Private Sub Form_Load
 
    Call SetPermissions(Me)
 
End Sub

Saves you loading up your project with lots of repeated code

Also - bear in mind that your code requires your frmLogon form to be open at all times (if it is closed, the sub can't evaluate Forms!frmLogon!cboUserName)

If it is open, and 'available', what is to stop someone from logging in under their own name, then just going back to the login form and selecting a different username (with different permissions) from the combo? Do you force a logout / password re-entry with an AfterUpdate event?

One possible workaround would be to create a Public variable for the username and set it at login - then you can close the form but store the value publically for all the other modules to access when determining permissions.

Just some thoughts... Well done with what you've done so far!

Al

 

[AOB]

Duh, just saw that you make the Login screen invisible, sorry... :banghead:

 

[vbaInet]

ToryM, you're still missing one last piece of the puzzle. Remember I mentioned four properties that you should set. Here's the last one.

Recordset Type - Snapshot for readonly and Dynaset for read/write.

The Allow"whatever" only locks the form, but the records being sent across are editable. It's a waste of bandwidth sending across a read/write dataset when you can send across a Snapshot. This is very important especially in your case.

 

[ToryM]

Thanks vbaInet!

Just one problem I seem to have now when I put the .RecordSetType onto 2 of my forms that already have Macros on them - the information that comes up is the first record on each of their tables, no matter what record I double click. Will I have to remove the Macros? I need them to bring up the right info that's from an OpenForm action which uses a "Where" condition using a Temp Variable.

 

[vbaInet]

Are you talking about a macro or are you referring to the code you have in your other post? Changing the recordset type doesn't limit the amount of records that gets pulled through, it only sets the state of the records that get pulled through.

Here's how you do it:

DoCmd.OpenForm "[FormName]", acNormal, , "[WhereCondition]", [COLOR="Blue"]acFormReadOnly[/COLOR]

What that code does is set the Recordset Type of the form's Record Source to 2 (i.e. Snapshot).

If you're using code there's no need to mix it up with macros so if you need a local or global variable you don't need the LocalVars or TempVars macro actions, declare variables in Modules/Classes.

 

[gemma-the-husky]

In think it isn't as simple as making a dbs read only. if you do that, you will find you cannot change the values of some things like check boxes and combo boxes.

you will most likely need to use code to manage a lot of forms.,so you can be selective about the editable fields.

this may have already been mentioned.