How to allow 180 users using the same Access database and block them to see each others data

[cindyLiu]

Hello Everyone,



How can I build a Access database which allows about 180 users to use, but block them to see/edit/delete each others data? All the records and reports only can be seen by manger team.

Thank you,
Cindy

[June7]

180 is a lot of users. Not sure that will cause issues for Access. How many users at same time?

Multi-user database needs to be split. Each user runs their own copy of frontend on their local machine. Use database settings and code behind forms to manage what records are available to the user. This requires the db to 'know' who opens the frontend. Code either a user login process or grab their Windows username. Will need a table of users with info that defines user privileges. Records will need fields with values that can be used to filter records based on who the user is. However, even with all these efforts on the frontend, if users know the location of the backend (and it is an Access file), not a whole lot you can do to prevent them opening the backend and viewing records directly.

This has all been discussed in numerous threads.

[cindyLiu]

Thank you! I will do some search about this topic.
Cindy.

[alansidman]

Cindy,
You might want to review this thread if you are looking to restrict access to various forms, etc.

https://www.mrexcel.com/forum/micros...ns-system.html

[ssanfu]

And with that many possible users , I would suggest using SQL Server Express (free) or SQL Server Standard ($$$$) for the BE.

[Micron]

Not sure, but I don't think the concurrent user limit has changed in quite a while. 2016 is 255 users.
https://support.office.com/en-us/art...8-98c1025bb47c

Read much of the treatise at the provided link; have to say I don't agree with characterizing zero or null as 'nothing'. This is the value of an object variable to which no object has been assigned. You cannot test if nothing is equal to anything (such as zero). However, I do advise the use of public functions whenever possible to do common tasks, such as checking if a control is null or contains an empty string. More on this here http://allenbrowne.com/vba-NothingEmpty.html

You will probably get lots of good advice on things to do when setting up a project like this and much of it may be relative to the level of user Access knowledge and the likelihood they have nefarious intent. AFAIC, at its best, Access security is not as good as the least security imposed by most other programs. So I'll attempt to add something to that bucket of good advice.

a) you can password protect a back end (preventing it from being opened directly) and provide this password during the table linking process. Yes, this password can be exposed (but I'm not going to advertise how since we don't want to raise the knowledge level of those nefarious users ). Like many other steps you can take, this would be best done in conjunction other steps. In this case, hiding tables and controlling on startup the Access options with respect to showing the nav pane. In this situation, it is not required to include password passing code anywhere since Access does not have to 'open' the back end (BE) to read the tables. The danger should be obvious - forget the password or fail to provide a means to uncover it at your own peril.

b) I agree with a user table, but how to set it up should depend on the required complexity. If it only requires basic levels (like User, Admin & Supervisor) why not just create these groups (like Windows user policies) in tblGroups and assign the group level ID to the user? You check their numeric level when deciding which parts to expose. If you want to make < or > comparisons (e.g. anyone greater than 3 can see this), include a Rank field to enforce an order with ascending/descending values. If it's more complex, then a more robust Group/Permissions association can still be made via tables. Regardless, exposing only the ID numbers from those tables would complicate things for anyone attempting to manipulate their permissions, as they will not understand seeing numbers in fields beside their name - assuming they got into the tables in the first place.

c) code the be to send you an email that someone opened it, and include their Windows login id if you think that would be necessary.

d) as for the id, I've never heard of anyone ever having problems with fosUserName (Google it) but I know a very knowledgeable Access guy who had to re-write where he used Environ, as it stopped working for some reason. Thus I don't see why I'd use anything but the aforementioned function.

e) regardless of how you get the user credentials, consider creating a custom user object (e.g. dbuser) to which you can assign as many properties as you wish. These might be Fname, Lname, LoginID, EmplID, MachID, dbLevel, etc. Then anywhere in code, you can reference the required property when deciding what to expose as well as record who changed what. You can even tell what pc they did it on. I included error checking for #91 (object variable...not set) because code breaks during development could require the object to be reset, so I rebuilt it easier. This is akin to any other built in Access db object and is even supported by Intellisense. You might, for example write
If db.usrLevel = "admin" Then (or one can use numeric attributes instead of text).

f) splitting as June7 advised is of paramount importance. Include a version number value in some be table and have the fe check for it on startup. If it doesn't jive, provide at least a notice to the user and close the fe. At best, provide a means for the user to launch an updater by accepting a prompt. You must update this value upon each release of an updated fe.

g) Even if you implement the "each user has their own fe" policy, you might encounter a situation where you have to edit be table design. You will not be able to do this if users have forms open that are reading these tables. Consider a table containing a 'lockout' checkbox field which a hidden timer form in the fe checks for (not too often as this will keep querying over the network). The fe should check this flag on startup and not allow launching if it's True. The timer code can force a shutdown after a number of cycles if you can live with the potentials of forcing a shutdown on unsaved data. The cycle can be augmented by duration and frequency fields in this table if you want an easy way to alter the way the procedure works.

That's all that comes to mind at the moment.

[cindyLiu]

Thanks, I will use your link to study.
Cindy

[cindyLiu]

yes, you are right. I used SQL server and CF built web application four years ago, it works fine. But now they want to design change, and also the time issue. I am not full time developer, and thought I might can use Access to get what they want.
Thanks,
Cindy

[cindyLiu]

Thank you so much for your solid knowledge, I will think about it.
Cindy

[Micron]

If I may embellish a bit on point g).
While I never employed the following tactic, I figure I would if I ever again implemented checking for a back end lockout flag.
That would be to check the flag before opening any form that represents the start of a new process, so as to prevent the user from beginning that process if you've started a lockout. If there was a concern regarding network traffic, the flag value could be stored local to the fe and be refreshed according to the time form cycle, say every 10 minutes. That would not be too onerous for most networks.

[cindyLiu]

Thanks, Micron!
Cindy