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.