Maintain a Database used by more than 100 users.

[Forbes]

Hello everyone,

After a over a month and a half of planning and fine tuning my database is up and running and in use, we were able to get any initial user bugs out of the way and it's all going smoothly. The system essentially allows employees to condition previous employees that have received the workload prior but did not do a specific part of there job, so that management can attend to the most important issues along the chain. These conditions pile into a database and are never changed unless the rare occasion that somebody falsely reports an employee but I have taken steps to prevent this. The mgmt. can then print a report based on Date limits (Monthly reports)

I have a few questions for a lot of you veterans and I don't mind compiled answers:

1) Should I be worried about the db as it grows, it will be used for hopefully a few years or until something better comes along but with all that data stored up in the tables, could it have an effect on performance other than the reports generating slower? I have the db split with the BE and FE on a shared drive.

2) I have a shortcut to the FEDB on all of the users desktops, this hasn't caused any problems and I have the db set to open minimized so the users can only see the form and all menus are hidden so essentially they can only close and open it. Is this best practice for security? also encrypting the data prevents user access on the forms, I expected this but can I encrypt the BE and allow users to access or make records without the encryption password.

3) Are there any other best practices or add ins or honestly anything nothing is a stupid suggestion but just tips and tricks to keep the db running smooth or good advice about db on a network with high user volume.

[RuralGuy]

You really do not want just a shortcut on the users desktop. You need the full FE. Here's a good multi user link:
http://www.accessmvp.com/TWickerath/.../multiuser.htm

[ranman256]

That sounds pretty good.

I had us tUsers table.
authorized users had to be in the list and they then had to login using their windows login.
The tUser table also had their 'level'.
some were just browsers and had read only view.
some were data entry,and had limited screens. Some managers,and Admin.

When a form loads, it would get the user level and lock controls or show some data, depending.

[JoeM]

Do users have a shortcut to the same Front-End? That could be problematic. They should all have their own copies of the Front-End.
What I often do is put a shortcut on their Desktop that is a batch or VBScript file. What this does is get a Copy of the Front-End from some central network location that they have access to, copy it to their hard-drive, and then open that copy on their hard-drive. This accomplishes two things:
- makes sure that they are running their own copy of the front-end
- makes it easy to push out updates (just update the copy on the central network, and the batch file will copy it down to their computer the next time they run it)

You mention that it may be used by more than 100 users. How many may be using it simultaneously? If it is close to 100, that could be problematic. From what I have read in the past, if you get beyond 15-20 concurrent users, it might be asking a bit much from Access. It really wasn't intended for such widespread simultaneous use. You may want to consider moving the back-end to something a little more stable/powerful like SQL or MySQL (Access can still act as the front-end). If you keep it on Access, be sure to Compact and Repair the back-end regularly to keep the size/bloat down. Access has a 2 GB limit on their databases.

And most importantly, make sure that you have a good backup plan (I would say at least daily). It is not uncommon for Access databases to have corruption issues.

[JoeM]

I was just doing some more digging, and apparently if you database is designed well, it should be able to support 100 users.
See: http://blog.fmsinc.com/microsoft-acc...an-it-support/

[Forbes]

Wow great article! Yeah it's not simultaneous so much more like 5-10 at the same time I think would be where it would max because the users are simply selecting 3 categories from drop down lists and selecting submit to create the record then the FEDB closes on the screen, unfortunately I do not have a server at this moment to utilize but I will definitely play with that on hyper-v at home and see what I can learn. The tUsers thing is a great Idea using the windows login, right now I have it set so to print the actual reports managers have to login with an sUsers module I'm running as well I want to make it simple for the users as I have it now, click the icon, fill out the drops downs, click submit, move on. So having the users login would essentially take the ease out of the process as well I could make the db record the windows login for the record creation in fact I will do that just for the sake of reducing false records... So thanks for that one! Keep em coming guys all great info!

[CJ_London]

It is not uncommon for Access databases to have corruption issues

to be fair, its not that common and usually down to poor design and/or against MS advice. Infrastructure wise, a shared front end is a classic reason as has already been mentioned. Use over wireless network also comes to mind. Providing users with a .accdb rather than a .accde means that it is more chance of a corruption, plus users can get into design view and make unauthorised changes.

[JoeM]

to be fair, its not that common and usually down to poor design and/or against MS advice. Infrastructure wise, a shared front end is a classic reason as has already been mentioned. Use over wireless network also comes to mind. Providing users with a .accdb rather than a .accde means that it is more chance of a corruption, plus users can get into design view and make unauthorised changes.

The point I was trying to make, is that it CAN happen (for whatever reason), and it is extremely important to back-up regularly. Not just for the potential of corruption, but for other reasons, such as network issues, cyber attacks, accidental deletion, user error (i.e. deleting a bunch of data that they shouldn't, etc).

One might think that is common sense, but it never ceases to amaze me when someone has some big issue with a database, and the advice is to restore your latest backup, and the user comes back saying that they do not have any! Or they have a backup plan, but it is something like weekly, or monthly, so they lose a lot of data in their restore.

[Micron]

The tUsers thing is a great Idea using the windows login...
As long as you're doing that, you might as well use the api call to get the user credentials, then there's no manual login at all. IMHO, it's more reliable than the Environ function. My last couple of dbs took this approach coupled with a user table. Consider learning how to create a custom object (dbUser) and give it properties for Fname, Lname, emplID, email, UserLevel, whatever. You can write dbUser.EmplID to any tracking field for any action such as writing to a record. Any time you need to check a user level for allowing form views or form controls access, just refer to dbUser.Level or whatever you call it. I also got the machine ID (another api) but didn't us it much.

As for backups, I used Windows Task Scheduler on a machine that was dedicated to running several nightly backups because this machine was never turned off. WTS seems to have the capability of running tasks even if one is not logged in, but I never tried it. My schedule event used a shortcut with a cmd line switch "Otto Mayshun" (dumb pun?). If the machine opened the db, a bunch of code was averted by checking the db Command property (it signifies that a cmd line was used in the db opening). It didn't matter what the Command value was to me, just that it wasn't an empty string or Null. When the backup routine runs, a command line (batch) file copies the db and over-writes to ensure the day's data is captured. Then all the tables update via ODBC. Then the batch runs again to over-write the backup only if the update goes off without a hitch. This ensures the day's data plus the ODBC updates are captured and all is ready to go at the start of the day.

Maybe some of that will get you thinking...

[Forbes]

Wow very cool, and yes got me more than thinking that's for sure I'm going to be following my project like my first born as I spent a lot of time on it learning as I went on quite a bit. I am always going to be improving and changing things, I know I will have to implement multiple things as it takes off, on the very first day (today) I have had to fix and redeploy 3 separate bugs although they fixes were compiled on the first re deployment, now off without a hitch. Thank you very much for all this I appreciate the advice and community on here!

[Paul H]

Interesting discussion. I'm going to follow up on some of these links and ideas for myself. I've had trouble with some multi user applications and am now migrating me back ends to SQL Server where possible. Using SQL Server backing up data would then become the purview of the DBA, right?