Login and User Access (1 Viewer)

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
HI All,
I've searched for a solution to this question but most of the suggestions i've come across are too complicated for me to follow. I have a database which is finished and working fine but what i need to add is a Log In form which gives a user either full access or just read only access to the database. I want to create a table (tblUsers) with the fields

UserName
Password
AccessType (Full or Reader)

I need to either let the user (Full) have full access to everything, or the user (Reader) have access to the various forms but just be able to read/print off reports etc. Would i need some code for the OnLoad event of the forms ?. I'd appreciate any help at all

Thanks, Em
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:21
Joined
Oct 29, 2018
Messages
21,358
Hi. Is your database split? Is so, one other option is to have two versions of the FE and give each user the appropriate version.
 

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
No it's not split yet. I've just seen a fairly easy to follow clip on Youtube about having two versions so i might go that direction if no-one has any ideas about the Form OnLoad code i was thinking of ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:21
Joined
Oct 29, 2018
Messages
21,358
No it's not split yet. I've just seen a fairly easy to follow clip on Youtube about having two versions so i might go that direction if no-one has any ideas about the Form OnLoad code i was thinking of ?

Hi. Even if you don't go with the two versions approach, you should still consider splitting your database.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
If the database is otherwise "secured" then the solution depends on your environment. If you have a formal domain at your site, you can ask the domain for your user's ID and then just look up their role in a table. This only works if you have "locked down" the DB to not show the navigation pane. I.e. you have a switchboard that keeps itself open and does not allow the user to see "behind the scenes."

Isladogs has posted several topics on using particular methods of identifying the user ID by asking the right questions of Windows itself. We also have many threads on "securing a database" that include the idea of isolating the user from the inner workings. What you seek to do will not work unless you have taken those steps.

Your description of what you did doesn't tell us specifics, so if you have already done this great! (And in that case I apologize for any incorrect assumptions.) But if you have NOT yet done anything to secure the DB, you have more work ahead of you.
 

Mark_

Longboard on the internet
Local time
Today, 11:21
Joined
Sep 12, 2017
Messages
2,111
Em,

If you don't feel up to doing a lot of coding, they least "Technical" way would be to split the data base first. Second import those forms and reports you want your "Reader"s to see into a NEW "Reader" front end and set the forms to read only. You would then keep the two front ends in different files and have your network administrator allow ONLY "Full" users to have permissions to the file with the original front end.

This is actually more common than you would expect. For complex systems there are often separate front ends for separate roles.
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
Emma
See if you can make use of this example app Password Login with Session Login Info

Personally I wouldn't create two versions of the app (or two different versions of the FE) as you would be doubling the work required both now and in any future developments

If interested, I also have many website articles and threads here at AWF on database security.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
I'm with Colin on this one. I'm going to ramble for a moment just to maybe give you an idea or two.

If you have a secured FE that employs an opening menu in order to hide the inner workings of your DB, then in the Form_Open of the opening menu, you can put the code that will decide what your current user can do. The Form_Open event code of the Opening Menu is the first code of yours that gets executed, in a new Access launch, so it is the first place in which you CAN take action.

In that context you don't have a lot of controls up yet, but you can make O/S calls or other actions to determine your user's ID so that you can do whatever you needed to do to set up the user role. I'm not going to say that my method works for you. As a general approach, it might. Who knows?

What I did was check the user ID from the O/S, then look up that name in a table of users and roles. I put the security code in a general module and put Public variables in the declaration area of that security code. Once that lookup was done, the role could be stored in a public variable in the general module, which means that it is persistent. I.e. you don't have to compute everything for every form. You just set aside some small number of key variables and put them in the general module. Then you reference them (with no significant lookup delay) when you need them.
 

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
Thanks everyone for taking the time to help. My VBA knowledge is deplorable so forgive me if some of what you're saying sails about 6ft over my head !.

Colin...i've downloaded your example database and i'm going to try to make it work but i'll be definitely back with some queries if that's ok ?

Thanks again
 

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
Ok if i incorporate the log in system into my database, how do i use the AccessLevel to determine what that User can and can't do when logged on ?
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
First add this to the module modFunctions

Code:
Public Function GetAccessLevel() As Integer
    GetAccessLevel = Nz(DLookup("AccessLevel", "tblUsers", "UserName = '" & GetUserName & "'"), 0)
End Function

This will return a number e.g. 1 for Delilah, 2 for Samson in my example.

Now add code like this wherever you want different actions depending on the user AccessLevel

Code:
Select Case GetAccessLevwl

Case 1
'do something e.g. Open a form, hide a control etc

Case 2
'do something else e.g. Open a different form, show a control etc

Case 3
'do something else again

Case Else
'do nothing or Exit Sub etc
'needed in case errors in your code cause the user name and access level to be 'lost'

End Select

I often use code like this to open forms with different openargs depending on user level.
Hope that helps
 

Mark_

Longboard on the internet
Local time
Today, 11:21
Joined
Sep 12, 2017
Messages
2,111
Personally I wouldn't create two versions of the app (or two different versions of the FE) as you would be doubling the work required both now and in any future developments

For an experienced developer I'd agree entirely.

For someone who's still trying to learn to code though, making a copy of the FE and going in to each form and changing the forms setting so you can't add/edit/delete is a bit tedious but a quick way to deploy the "View only" version. This meets their initial need while giving them time to learn a better way.
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
We'll have to disagree about this.

If the app was 'cast in stone' with no further development planned, that might just possibly have some limited merit.
However, I would still advise against it for any developer, even those who are beginners.
I still say they will spend longer creating/maintaining two versions than the time needed to set up select case code or similar for different types of user.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
And I will back that up by saying that having multiple FE files that are different will lead to innumerable rounds of confusion as to what is being done where, and whether it needs to be done two places or only one, and which one gets which fix. Questions like that will abound when doing multiple-file FE setups.
 

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
Thanks for all the suggestions guys.....just got distracted with something else there for a couple of days. Going to make some changes and see how it goes then will be back to pester you with more queries :)
 

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
First add this to the module modFunctions

Code:
Public Function GetAccessLevel() As Integer
    GetAccessLevel = Nz(DLookup("AccessLevel", "tblUsers", "UserName = '" & GetUserName & "'"), 0)
End Function

This will return a number e.g. 1 for Delilah, 2 for Samson in my example.

Now add code like this wherever you want different actions depending on the user AccessLevel

Code:
Select Case GetAccessLevwl

Case 1
'do something e.g. Open a form, hide a control etc

Case 2
'do something else e.g. Open a different form, show a control etc

Case 3
'do something else again

Case Else
'do nothing or Exit Sub etc
'needed in case errors in your code cause the user name and access level to be 'lost'

End Select

I often use code like this to open forms with different openargs depending on user level.
Hope that helps

Ok i've changed the module modFunctions and i have two people left in tblUsers. Billy has access level 1 and Delilah has access level 2.

Level 1 is total access and Level 2 is read only. How do i use the SelectCase argument to allow users the correct access to each form ?

Thanks
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
Code:
Private Sub Form_Load()

Select Case GetAccessLevel

Case 1 'full access
Me.AllowEdits =True

Case 2 'read only
Me.AllowEdits = False

End Select

End Sub

Note - as the form will by default be editable, the code in Case 1 isn't really needed but I've included it for completeness
 

Emma35

Registered User.
Local time
Today, 11:21
Joined
Sep 18, 2012
Messages
455
I've changed the password for Billy to 'billy' and the password for Delilah to 'delilah'. When i try to use the Log In form it tells me the passwords are invalid and to try again ? I've changed the passwords in tblUser
Have i done something wrong ?
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
I've changed the password for Billy to 'billy' and the password for Delilah to 'delilah'. When i try to use the Log In form it tells me the passwords are invalid and to try again ? I've changed the passwords in tblUser
Have i done something wrong ?

Yes you have if you have just typed those passwords in tblUsers.
Remember that the passwords are meant to be stored in an encrypted form.
Look back at the screenshots in the original link.
You might be better going back to the original version
 

Users who are viewing this thread

Top Bottom