Security Level

[pastorkc]

How do I get access limited based on security level I have assigned in the user table. Instead of using a name as I started doing in this vba code:

Private Sub CombineFYBtn_Click()
If TempVars("Username") = "Ken" Or TempVars("Username") = "Staton" Or TempVars("Username") = "Kelly" Then
DoCmd.OpenReport "Combined FY", acViewReport
Else
MsgBox "Access Denied"
End If
End Sub

 

[Joe4]

If sharing an Access database with multiple users, the proper thing to do is to split the database into a front-end and a back-end database.
The back-end holds all the data, and there is just one copy of that.
Each user should get their own copy of the front-end (this helps prevents issues with multiple people trying to use the database at once, and makes it much easier to work on database updates while people are using it).

Because you would be giving individual users their own copy of the database, you can actually have different versions of the front-end database. So, if you wanted some to show certain things to some users and others not to, it is much easier to control that way, by giving them the appropriate version of the front-end database.

See here for more details: Split an Access database - Microsoft Support

 

[pastorkc]

That sounds exactly like I need to do. Do you have any reference of examples of what the front-end for each user would look like?

 

[Joe4]

That sounds exactly like I need to do. Do you have any reference of examples of what the front-end for each user would look like?

It all depends on your needs, what you want to give/expose to each user.

Once you create the version that you need, you can turn it into an ACCDE, so the user cannot view the VBA code and change anything.
Instructions on how to do that are here: What's an ACCDE File and How Do You Open One?.

Just make sure that you save the ACCDB version (before changing to ACCDE) for yourself, so you can make changes in the future.

The process is as follows:
1. Split your database into a back-end ACCDB and a front-end ACCDB.
2. Create as many versions of the front-end ACCDB that you need. You can simply copy the one front-end you created in the initial split, name it anything you want, and make any changes that you want to it.
3. Convert your front-ends from ACCDB files to ACCDE files.
4. Distribute the appropriate ACCDE file to each user. These are what they should be using to access the database. It can be stored on their Desktop, hard-drive, or personal network directory.

You do not necessarily need to create a separate front-end for each person, just different versions.

For example, maybe you have a "limited access" copy, that allows read-only, for a group that only needs read-only access.
Then you would create that front-end version, and share a copy of it with all users who only need read-only access.

You could then have another version of the front-end which allows for data entry, and share a copy of that with all users who need to enter data.

Obivously, the key is that each user should only be able to see/access the front-end version that you want them to use.
So you would not want to put copies of the front-end database on a public network drive that everyone has access to.

 

[pastorkc]

OK I think I get it. Thank you.

 

[Joe4]

You are welcome!

 

[pastorkc]

When creating a front end after splitting and I want users to have different access. Would I create a front end for each user group. Like I wanted the office staff to only be enter data into the forms created. I would only have those options available for the office staff on the front end. They wouldn't even see report or table options correct? Then for Managers I would include the report options on their front end.

 

[Joe4]

Yes, you would create different versions of the front-end to distribute, based on the group's needs.

 

[pastorkc]

OK this is a lot more clear now. Thank you.

 

[welshgasman]

Yes, you would create different versions of the front-end to distribute, based on the group's needs.

PMFJI, but that sounds like a nightmare to maintain?
I would have names, that are in groups and assign levels to the groups.
That is how I used to setup my DBs when that sort of security needed.