Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-04-2019, 02:59 AM   #1
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 271
Thanks: 113
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Login and User Access

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

Emma35 is offline   Reply With Quote
Old 12-04-2019, 05:04 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,249
Thanks: 63
Thanked 1,681 Times in 1,636 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Login and User Access

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-04-2019, 05:31 AM   #3
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 271
Thanks: 113
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Login and User Access

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 ?

Emma35 is offline   Reply With Quote
Old 12-04-2019, 05:54 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,249
Thanks: 63
Thanked 1,681 Times in 1,636 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Login and User Access

Quote:
Originally Posted by Emma35 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Emma35 (12-05-2019)
Old 12-04-2019, 07:30 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,079
Thanks: 100
Thanked 1,891 Times in 1,727 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Login and User Access

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Emma35 (12-05-2019)
Old 12-04-2019, 09:30 AM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Login and User Access

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.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Emma35 (12-05-2019)
Old 12-04-2019, 09:40 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,921
Thanks: 120
Thanked 3,327 Times in 3,003 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Login and User Access

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 12-04-2019 at 09:46 AM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Emma35 (12-05-2019)
Old 12-04-2019, 10:04 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,079
Thanks: 100
Thanked 1,891 Times in 1,727 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Login and User Access

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-05-2019, 04:30 AM   #9
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 271
Thanks: 113
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Login and User Access

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 is offline   Reply With Quote
Old 12-05-2019, 04:58 AM   #10
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,921
Thanks: 120
Thanked 3,327 Times in 3,003 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Login and User Access

Sure. Good luck.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 12-05-2019, 06:54 AM   #11
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 271
Thanks: 113
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Login and User Access

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 ?
Emma35 is offline   Reply With Quote
Old 12-05-2019, 07:50 AM   #12
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,921
Thanks: 120
Thanked 3,327 Times in 3,003 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Login and User Access

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 12-05-2019, 09:06 AM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Login and User Access

Quote:
Originally Posted by isladogs View Post
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.
Mark_ is offline   Reply With Quote
Old 12-05-2019, 09:13 AM   #14
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,921
Thanks: 120
Thanked 3,327 Times in 3,003 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Login and User Access

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 12-05-2019, 12:39 PM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,079
Thanks: 100
Thanked 1,891 Times in 1,727 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Login and User Access

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
login and user access bigmac General 7 10-31-2013 08:01 AM
Login Form that brings back certain records according to the user login. mcsrta Forms 1 11-24-2012 06:06 PM
User login with access levels FunkyMonk150 Forms 3 03-26-2009 01:20 AM
Creating user login with access Scythed Queries 20 09-16-2008 05:15 AM
Using Access's User and Group Accounts or Using a Login Form to access database? sara82 Forms 3 09-09-2005 09:58 AM




All times are GMT -8. The time now is 06:00 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World