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]

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.

No worries. Always good to hear other ideas.

Years ago, when Access database were "mdb", they had security features that I used once, though they were a pain to maintain, and I thought that they did away with that in later versions ("accdb").

Can you go into a little more detail about what you are suggesting?

 

[welshgasman]

On my phone at the hospital atm.
Will respond when I get home, but basically a table of users who belong to certain groups and assign level/rights on the group. That is how my old mainframe used to do it and I just emulated that.

 

[Joe4]

On my phone at the hospital atm.
Will respond when I get home, but basically a table of users who belong to certain groups and assign level/rights on the group. That is how my old mainframe used to do it and I just emulated that.

Great! I would love to hear how you were able to do that.

I never had need for more than 2 different front-ends. I used to do a little trick that made updating the database a snap.
Instead of directly giving users a copy of the appropriate database, I made up a batch file (.bat) for each one, and put a shortcut to that on their Desktop.
What the batch file did was the following:
1. Deleted the old copy of the database from their C drive
2. Copied the referenced front-end name from a spot on the network to their C drive
3. Opened that new copy from their C drive

So every time that clicked that button their desktop, it downloaded a fresh copy of their front-end and opened it.
The nice thing about doing it this way is it also made updates a snap. I would just overwrite the copy of the front-end on the network with the new verion.
Then the next time they clicked the button, they would get the new version.

So after initial set-up, maintenance and updates were easy.

 

[pastorkc]

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.

So what would your example look like in the table and in the code?

 

[Micron]

I agree with wgm - too much effort to manage multiple versions. User names (and other useful user info) in a table along with a permissions level field is the way to go. To answer the original question, use DLookup on the the user table to get the user permissions level, then open the forms/reports accordingly. I would also make the permissions an integer field (with a description field) because you can use comparison operators on numbers, such as <, >, = etc.

 

[welshgasman]

So what would your example look like in the table and in the code?

I used the standard switchboard form and added the user level to the switchboard table

User level in tblUsers

I could probably make it a little easier now, as I know a fair bit more about Access, but it would also deter anyone trying to fathom how it worked.
I had to go back and look at the code it has been so long.
On login I set TempVars so I had the pertinent information to hand all the time

TempVars("EmployeeID").Value = Me.cboEmployeeID.Column(0)
TempVars("Employee").Value = Me.cboEmployeeID.Column(1)
TempVars("UserLevel").Value = DLookup("DataOrder", "tblLookup", "LookupID = " & Me.cboEmployeeID.Column(3))
DoCmd.OpenForm "Switchboard"

Source for cboEmployeeID is

SELECT tblUser.EmployeeID, [Forename] & " " & [Surname] AS FullName, tblUser.UserPassword, tblUser.UserLevel, tblUser.UserActive
FROM tblEmployee INNER JOIN tblUser ON tblEmployee.EmployeeID = tblUser.EmployeeID
WHERE (((tblUser.UserActive)=True));

The the switchboard form recorsource was

SELECT *
FROM [Switchboard Items]
WHERE ((([Switchboard Items].ItemNumber)>0) AND (([Switchboard Items].SwitchboardID)=[TempVars]![SwitchboardID]) AND (([Switchboard Items].UserLevel)>=[Tempvars]![UserLevel]))
ORDER BY [Switchboard Items].ItemNumber;

So I never presented anything to anyone who should not see it.
I only supplied an accde so they could not see any forms/tables etc.
I was lucky that I did not have any colleagues who would go looking on how to break it.

 

[welshgasman]

Great! I would love to hear how you were able to do that.

I never had need for more than 2 different front-ends. I used to do a little trick that made updating the database a snap.
Instead of directly giving users a copy of the appropriate database, I made up a batch file (.bat) for each one, and put a shortcut to that on their Desktop.
What the batch file did was the following:
1. Deleted the old copy of the database from their C drive
2. Copied the referenced front-end name from a spot on the network to their C drive
3. Opened that new copy from their C drive

So every time that clicked that button their desktop, it downloaded a fresh copy of their front-end and opened it.
The nice thing about doing it this way is it also made updates a snap. I would just overwrite the copy of the front-end on the network with the new verion.
Then the next time they clicked the button, they would get the new version.

So after initial set-up, maintenance and updates were easy.

I used Bob Larsens Auto Updater.
I have a thing about not doing something if it is not needed.

So they only got a new copy when a new copy was available. A little more complicated to setup, but used infrequently, although the amend log would say that was not the case , but DB was still in development then as well.
I also kept a log ov version number and amendments.