Question Database setup for multiple users

[HGCanada]

I am creating a clinic database to store patient information, for multiple users (clinic managers, care providers, clerks). I've noted some background info, and my plans below. Does this sound reasonable, especially in terms of not splitting the database? I want to make sure that I am not overlooking anything. Thank you so much.

Here are some background notes:

- the database has- a main table, 10 reference tables, a single data entry form, and I am working on around 5-10 queries/reports
- it will be housed on a network drive folder, with access restricted to authorized individuals
- may be used by up to 5-10 people per day, I am guessing closer to 5 using it continuously, and the rest using it less often
- users may sometimes access the database from different computers
- data entry into the forms will be frequent, with a hopefully low likelihood of multiple people attempting to modify the same record simultaneously
- users may view the same reports simultaneously.
- the end users only want to view reports and forms - no tables or design functions - basically they will see just forms, reports, and buttons taking them from one place to the next. Only 1 person (me) will actually design queries and reports.
- eventually, some people might be accessing the database from off-site, through a remote link.
- There may be an option for our IT department to save the tables to an SQL database server, and I've been advised that we should likely not need to do this, given the relatively low number of users.

My plan is as follows:

- I will create a separate copy of the data entry form for each of them, each form linking to the *same* single data entry table (no separate tables, no synching)
- I may create separate accounts for them - not yet decided
- I will not split the database, since they have no need to be able to modify the front end - just to view it
- I will set up shortcuts to the database on all of their desktops

Does this sound reasonable? Many thanks in advance.

 

[CJ_London]

Does this sound reasonable?

No

you must split the database - even if you have one user at a time. If you don't you run a greater risk of having a non recoverable corruption. Each user should have their own copy of the front end - unless all users are logging into the same machine so no two users can use it at the same time, which does not appear to be the case in this instance.

Only time it could be considered to be acceptable to not split is during initial development or for a 'quick and dirty' db which does not form part of business process.

You should not need separate copies of the data entry form for each user - they all use the same one - but from a copy in their own front end.

There may be an option for our IT department to save the tables to an SQL database server, and I've been advised that we should likely not need to do this, given the relatively low number of users.

they won't be bothered until your db is deems 'process critical' or similar at which point a great outcry will come from IT who will insist the backend is on SQL server, the front end must be written in html or similar and it is going to cost $50k+ and we haven't got the budget. - Happened to me on a number of occasions

eventually, some people might be accessing the database from off-site, through a remote link.

depends on how they are linking, can be quite slow. If your IT department has remote servers or Citrix, that would be the place to put it. Alternatively that is the time to look as SQL Server and HTML.

data entry into the forms will be frequent...
...
- the end users only want to view reports and forms

does not compute - are you saying you have users who do data input and end users who read reports?

 

[HGCanada]

Thank you so much for your feedback. I'll look into how to split the database.

To answer your question, what I meant to say is that all users will be entering data into forms and viewing reports - some of them on a daily basis. However, none of them will be accessing any data table views, Access functions, or any of the the back end.

Quote:
data entry into the forms will be frequent...
...
- the end users only want to view reports and forms
does not compute - are you saying you have users who do data input and end users who read reports?

 

[MarkK]

You should split the database. You should decouple your data from your user interface. Having those two major elements in the same file is a maintenance liability, and a corruption risk.

 

[HGCanada]

Thank you. And this is why I ask the experts.

 

[HGCanada]

I have further questions about this. Here is my plan:

- the database will be split, and housed on network folder 1
- subfolder 1a will contain the data tables
- subfolder 1b will contain multiple copies of the front end, which will contain forms and reports - one for each work station (I don't expect that the end users will be creating their own queries - just working with the forms and reports that have already been created)
- each work station will have a shortcut to its respective copy of the front end database

A few questions:

1. Is this a sound plan?
2. Can I modify a primary copy of the front end, and then upload that simultaneously to all the users? They won't need to modify or save any of their changes other than data entered into forms, so I don't believe there will be any risk in over-riding their work.
3. I did a test split of a database. One copy has only tables, and the other has everything, including the tables. Is this supposed to be the case? I thought the second one would have everything but the tables.

Thanks.

 

[Minty]

1. No - I wouldn't use the front end over a network, unless you are talking about sub folders on a terminal server or similar. Put the front end on the end users local machine.

2. Not really unless you know they aren't using it. I would have a google of Auto-Updating Front ends. This is where when the user opens their local FE copy it will check and if necessary update them to the latest version.

3. Are the tables in the FE linked tables or are they a copy of the actual tables? They should be linked.

 

[The_Doc_Man]

- each work station will have a shortcut to its respective copy of the front end database

Is this a sound plan?

No, if the read the statements literally, though it is possible that I mis-read your intentions.

Use the shortcut for one thing and one thing only - to let the users open a shortcut to the folder (NOT the application) - so that they can drag a copy to their chosen work folder on their machines. Pointing to a copy of the FE file (which is literally what you said) negates any advantage of having a split FE.

The others didn't tell you why you need separate FE files, but I will. There is this thing called locking that occurs in Windows file systems. When you open a file, you take out a lock on it. Depending on the exact way in which you open it, that lock can be shared or exclusive. For a shared lock, every user that attempts to open an already open file object has to go through lock negotiation, which slows down the the process of accessing the file.

Add on top of file locking the fact that Access does block locking and, though the duration of a block lock is short, it isn't zero. If everyone is using the same form, they are taking out a lock on the same item, and this is called "lock contention." This lock contention starts when you take out the lock on the DBEngine, then on its component Workspace, then on the Forms Collection, then on the Form. Using separate forms for each user only obviates the Form lock but none of the other.

Giving everyone a COPY of the FE file to be run PRIVATELY on each user's machine means that your file and content locks are ALSO only on each user's machine - no contention for them. The ONLY thing that will still experience contention is the BE file and its tabular content. ALL of the FE locks are now PRIVATE.

Can I modify a primary copy of the front end, and then upload that simultaneously to all the users? They won't need to modify or save any of their changes other than data entered into forms, so I don't believe there will be any risk in over-riding their work.

Probably not "simultaneously" because there is no guarantee that their machines are talking to the network. Do your users ever turn off their machines? Search this form for topics similar to "Automatically Download Front-End" which would open a BATCH file to remove the older private FE file then copy a new FE and launch THAT. So instead of you pushing the file, you let them launch something that pulls the file.

One copy has only tables, and the other has everything, including the tables. Is this supposed to be the case?

That is EXACTLY the way it should look.

I'll toss in a reminder on using the Linked Table Manager. In order to make this work when you run the copies on each person's individual machine, you have to use the Linked Table Manager from the Database Tools ribbon. It is easy to use, but you have two choices.

Choice A is to assure that every user's machine maps the same drive letter to the shared folder on the BE host machine. Then use LTM to point to the BE file as the location of the required tables.

Choice B is to use URS mapping ( \\machine\share\folder\...\folder\BE-file-name-and-type) when you use LTM - in which case you don't CARE about whether a drive letter mapping exists. Hint: The shorter the folder path, the faster your access will be, because security access arbitraration will occur for EVERY FOLDER in that path when you try to open the BE.

Note also that the folder structure you described will have to grant MODIFY access to every user who needs to use the DB. There are two ways to do this, but I'll put dollars to donuts that your IT staff will have a preference for option #2 below.

1. Just open up every folder in the path from the BE's host machine to subfolders 1a and 1b. All of them will grant MODIFY access to Windows group "Authenticated Users."

2. If you have a domain structure with domain-based login, you can do this. Have your IT group create a group and grant the GROUP the required MODIFY access. Then using the domain logins, identify the domain-based usernames of your users and have them added to the group.

(I don't expect that the end users will be creating their own queries - just working with the forms and reports that have already been created

Don't just "expect" users to behave that way. Use a switchboard form and force the issue by having the switchboard hide the ribbon and navigation items. Let the users only see what they need to see to do the job. (If that includes letting them see the innards of the system, then you either have a major design flaw in your security or you haven't finished the job of protecting your DB.)

Can I modify a primary copy of the front end,

Yes and no. Search the forum for the concept of a "Design Master." This is the developer's copy of the FE and is not fully secured. The developers, unlike the users, CAN see the innards of the DB. But when you are about to deploy a new version, you COPY the design master and finish setting it up for deployment. You NEVER deploy the design master; you always deploy a fresh copy thereof.

 

[HGCanada]

Thanks.

Re: 1 - I am not sure if it a terminal server - will try to figure that out. It's a network folder that the division has access to. The issue is that we want to do this on our own, but only IT has access to actually save anything on their computers. And getting all the staff to copy the new version manually - not going to happen.

Can you please tell me why this is a bad idea? I will need to pass this on to the clinic manager. Edit: never mind, I see this has been addressed in the next reply. Thanks.

2. Based on response to #1 - yes, I think this would be ideal, then. I hope this will be simple and effective to implement.

Re: - 3 - I used the Database splitter wizard, so I assume they should all be linked properly. If I try to modify the tables of the FE copy, I get an error message that it's a linked table whose design must be modified in the source table. Ideally, what I want it for the front end to really just show reports and forms, navigated through buttons.

 

[The_Doc_Man]

I used the Database splitter wizard, so I assume they should all be linked properly

The are, until you move the FE file to the local machine. The splitter automatically links the BE tables using the local machine's folder paths. Once you move EITHER FILE to another folder, you have disrupted the links and need to relink them.

only IT has access to actually save anything on their computers

Then you need to talk to IT about how to solve this problem because now you have a data management issue, not a technical data issue. Every user should at least have a folder in which to store private information. No, not juicy love-letters, but rather each person will work on different things, different cases, different events, ... different SOMETHINGS - and will need local storage for same. Trust me, Word or Excel would go berserk if you can't store your work SOMEWHERE. Access is no different.

And getting all the staff to copy the new version manually - not going to happen.

Then search the forum for that batch job that automagically makes the copy as part of the application launch process.

Using a terminal server complicates matters in one way, since you STILL need each user to have a separate copy of the FE file in storage set aside for them on the TS host. There is also a licensing issue for using Terminal Services and opening Access. That has also been covered in this forum under the topic "Terminal Services" so is another source of search material.

 

[HGCanada]

Many thanks to all for this incredibly useful information. I will take this back to the team so that we can plan for this. And yes - one of the downstream steps is definitely to remove some of the functionality so that end users only see the specific forms and reports that they should access, and will not be modifying any of these, even on their own copies.

 

[Minty]

If you only install the access runtime rather than full version access they can't do much to the FE at all with a bit of planning.

(It's also free, so gives you some leverage over the IT dept for the other things you want )

 

[BeeJayEff]

And getting all the staff to copy the new version manually - not going to happen.

That's what I thought, but now all users have come round and are used to taking the new version when needed; they get fed up with the warning message telling them they're using an old version (of course I could just close it down unless they use the latest).

My main reason for posting on this thread however is for the benefit of others looking at the thread later - to point out one other feature of the split/don't split question which I don't think has been mentioned. And that is performance. With an unsplit db, every time a form is displayed the whole rendered form has to be transferred across the network, whereas with a split db, only the data has to be transferred.

 

[The_Doc_Man]

BeeJayEff - good point about rendering.

 

[gemma-the-husky]

I add a couple of things. I am sure most of us do similar.

1. I put a "version required" somewhere in a table in the back-end. If the users' front-end version is less that the required version it informs the user to update the database to a newer version.

The only reason it's a bit tricky is because you can't update the copy while you are in the database - so you need a way to exit the database, update the database, and then open again. And you need a failsafe in case the update failed etc. Or you need to manually do part of this. So that's what the auto-updaters do - part or all of this.

2. I add code to limit the drive letter to say a max of "F" - which might be a partition on my hard drive. If the user tries to run the network master copy, it finds a drive letter greater than "F", gives an error message and exits.

 

[CJ_London]

The only reason it's a bit tricky is because you can't update the copy while you are in the database - so you need a way to exit the database, update the database, and then open again

I have code which checks the table on a regular basis - typically in the current event of the main navigation form and then displays a message in a label (not a message box).

The table also contains an urgency flag (or you could include the keyword 'URGENT' or similar in the message). Yellow means a 'soft update' - 'there is an update available, please close and reopen the database at a convenient opportunity' or a Red flag which gives the message 'There is an urgent update required, further work cannot be undertaken please exit immediately and reopen for the updates to take effect'.

The code then disables all but the exit/close button. Then starts a timer, if the user has not exited within say 2 minutes the application is closed automatically.

I don't use a msgbox because this effectively stops the code from running if the front end needs to be closed and the user is away from their desk to click the OK button - although I'm sure someone will point out they need to be at their desk to generate a current event anyway.