Please share your thoughts/experiences on this with me as I'm not sure what the best solution is. Here is the scenario:

I have built an Access2000 database that manages loan customers and prospective loan customers for a bank; it has roughly 25 tables, 20 forms, 20 reports. The thing is that loan officers in different offices will be accessing this database; I'm probably looking at about 20-40 users, maybe concurrently. I would like to have this on a shared server, so that if I need to make an update I only have to do it one time.

I have tried this approach with another database I built for them which has about 5-10 users. I know Access is supposed to be able to handle something like 255 concurrent users but occasionally I will run into weird errors that require everyone to get out of the database and I have to repair it. So it's kind of working but doesn't seem to be completely stable, and makes me nervous about trying this on a larger scale.

I've read about splitting the database in the archives here. As I understand it everyone has their own front-end and views the same back-end on a server somewhere. So it seems that I would have to update each front-end locally for each loan officer, which does not make a lot of sense to me. Also, some of the reports in my database are fairly beefy, and experimenting with splitting led to an increase in load times. Also, can you add tables, fields..etc after splitting without messing up the front-end, back-end relationship?

So basically my question is how should I set this up? Ideally the database would be in one location, and they would all be able to access it concurrently without a problem.

Any suggestions are appreciated, thanks.

If you're using a database with several concurrent users, splitting it is the only way to go.

Run a search for auto-update. This will make updating the front end easier instead of visiting each pc or getting the user to copy a new FE.

You shouldn't have too many speed issues with only 20-40 users. How modern is your network? As long as you're on CAt5 and not co-ax cable you should be ok.

There's no problem with adding tables / reports / forms etc once you've split your db. Any new objects just ned to be linked to the BE.

Thank you. I will look in to the auto update.

So let's say in this environment (split DB on network) I needed to add a new field to a report. Could someone explain the general process?

From what it sounds like I would first add the field to the table in the backend, then update the report on the front end... then link the new report to the backend? or maybe use the auto updater to update all the production frontends.

I'm not sure I understand how this would work. It sounds as if this is the only way to go, so I will do some more reading....

You're on the right track.

Once you've split and distributed your db make sure you keep a copy to test any changes before they are implemented.

On your master copy, add any extra fields, change reports, make changes etc then replace the db on your server with the new updated version. Your auto update program will distribute the new FE next time users log in.

There's loads to read regarding auto update and splitting a dtabase in UA, good luck!

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/compute.gif)