Need an informed opinion of database architecture
Posted on 2011-03-24
I have a relatively small multiuser database that I built off of the Sales pipeline template that comes with Access 2007.
I began running it in a small, multi-user environment so I used the Database Splitter in AC07 to split it into a front-end and back end.
My network consists of 4 computers all running Win7 with a GB LAN in a small office environment. One PC serves as a shared files location, and ALL files reside on an external hard drive connected to it. Each PC maps their MyDocuments to a subdirectory on the drive.
The front end is located in each users MyDouments folder (which is on the external drive), and the back end resides in a shared folder on the external drive.
We have one PC with a 100 base T network card, and all others use 1000 base T cards and the switch is 1000 base T as well. The office is only 1600 sqft so runs are short.
As I add users, I wonder if this is the best way to configure this database. It currently has about 60,000 records.
One more thing, two workstations share the same username, so when they login they also share the same MyDocuments, and therefore the same front-end. Would this cause any type of file locking issues?
We just moved from 1 or 2 people accessing the table at a time, to 3 users. I don't think this is a very big database, and the network is very fast, so my initial thought is that I should be OK. However we started seeing some goofy stuff, such as slow responses and records not showing updated until the front-end is closed and re-opened and there are too many records to risk corruption.
I'd like to ask those who are familiar with this type of setup what they think. Am I on the wrong road, or should this type of setup work ok?
This database is for tracking daily business, and I am only a novice programmer.