Split DB

[rfoye]

I have developed several multi-user databases, and keep reading about how you should always split multi-user apps into a front end and back end. I have a couple questions.

1. Does splitting make record locking and simultaneous updates of the same record MORE or LESS of a problem?

2. Does the front end REALLY need to go on the user's local machine, or can they get the same benefits of only pulling necessary data from the back end if they use a front end that is also on the server? I am either in constant fiddle mode on the forms and reports, (thanks to supervisors who always want to make "one more little change") or my users would have no idea how to install a new version. I would much rather maintain ONE live version of the front end than worry that everyone has the most recent version.

-------------------
Rob Foye
Database Management
Regions Bank

 

[FancyPrairie]

1. No difference

2. The FE should go on the user's machine for 2 reasons.

1) The main reason is that if you only have one copy on the server that everyone is using, sooner or later someone is going to corrupt the database. If that happens, all users are dead in the water. If the FE is on individual machines, and the db gets corrupted (less likely), then only one user is effected.
2) The screen will be displayed quicker if the FE is installed locally.

I have one database (Access 95) that is installed on 188 machines. This is how I handle it (search Tek-Tips for other methods).

I created a table that contains (among other things):
1. Name of the FE database
2. Name of the user's computer
3. Name of the primary user of the machine
4. Phone number of the primary user
5. Office location
6. New Version Flag
7. Broadcast message flag
8. Force user out of database flag

The user launches a batch job (vbscript) and the first thing the batch job does is opens the table to see if the Force User out of database flag is set. If so, then I don't want anyone in the database at this point, so I pop up a message that the system is currrently down and exit the batch job.

If that flag is not set, then I check to see if the New version flag is set. If so, then the batch job issues the command to copy the new version of the FE from the server to the user's machine, then clears the flag, and opens the database.

If this is the first time the database is run on the user's machine, then it hasn't been registered yet and no record exists in the table. Consequently, I open the database with a command argument to execute a macro. The macro opens a form the asks the user to register his/her pc (i.e. fill in the table information).

So, if I need to distribute a new version of my FE database, all I have to do is open the table and set the New Version flag for each machine. I have created a maintenence form that allows me to select just the machines I want to receive the new version (i.e. for testing purposes before it is released to everyone). This form also contains a button that will set all of the New version flags for me.

All of the systems I have put together use this same feature. That's why one of the fields in the table is the name of the database. Cosequently, I can filter the form so that it only shows the database I'm currently messing with.

Also, if you need to contact someone, you have their name, phone number, and location recorded.

At startup, I open a form (invisible) whose timer is set to 1 minute. Every minute the program checks to see if either the Broadcast message flag is set or the Force user out of database flag is set. If Broadcast message flag is set, then I pop up a form that displays whatever message I want the user to see (i.e. System going down in 5 minutes). Upon exiting, the flag is cleared. If the Force user out of database flag is set, then I issue the Application.Quit command which closes the user's database.

It may sound rather complicated, but it really is not and well worth the effort.

1. Create the table
2. Create the batch job
3. Create a form to register the user
4. Create a timer form

Once you have this information in place there are other things you can do, time permitting. For example, suppose someone has a lock on the database or you need everyone to get out of the FE and somebody is still in. There is a way to figure out WhosLoggedIn (search Tek-Tips, I've published some code that returns this info). Based on the information returned, you can have a form that shows who is still logged into the FE with their name, phone number, and office location included. Now you have a way to contact them.

 

[willir]

Great response FancyPrairie

1) Record locking
Record locking will be controlled by the database where the tables are stored. With a "split" database, this means the back end will manage the ecord locking (and data integrity, etc).

Gotcha -- If a user is setup to have exclusive access to the database, this user may lock up the database.

2) Where to store the front end
You can store the front end on the local PC or on the network. The front end can be shared.

Storing the front end locally improves performance but makes it more challenging in deploying any updates to the forms, etc. Most of us store the front end on the local workstation.

Gotcha -- Access is not real smart when handling data across a network. Unless you use various strategies, Access will load the data from an entire table when a form is opened. If you have a transaction table with 100,00 records and use the defaults when creating the form, Access will load the entire 100,000 records for anybody who opens up the form. This may affect performance on the network and leave users wondering why things are so slow on their local PC. Search for items on performance for various strategies.

Hint:
Remember to move "static" data tables to the front end.
You can have more than one front end -- one for admin, for for queries and one for data entry.

3) Fiddle mode:
As stated you, you can have more than one front end. You and your manager can "play" with the "beta" front end until statisfied and roll out the new one.

There is some code in this forum for perform automatic updates of the front end. I think BillPower wrote one.

Richard