Performance issues -- one .mdb or a several smaller ones? 1

[rcoutts]

I'm building a database for billing and time tracking in our office. Currently I have four separate files:

Expenses.mdb -- All hours, expenses, time card codes, etc.
Income.mdb -- Creates & tracks invoices, payments, etc.
JobsList.mdb -- All current and closed jobs
TimeCard.mdb -- Each employee has one of these

The way I've designed it is each employees has a time card database on their local computer. It has links to Expenses.mdb and JobsList.mdb (which reside on the Server) so the time card knows which jobs can be charged and what the expense and time codes are. At the end of the week, each employee clicks their "Submit Time Card" button and it gets uploaded to the tables in Expenses.mdb. Also once a week or so, Income.mdb is opened, which has links to JobsList.mdb and Expenses.mdb and generates invoices and reports.

I'm not done yet, but this organization seems to be working out okay (I should mention that this is my first ever Access project and don't yet know about multi-user and performance issues.) My question is, should I just go ahead and put Expenses.mbd, JobsList.mbd, and Income.mbd into a single file? The only reason I've been keeping them separate is that I thought maybe since more than one TimeCard.mbd file may be accessing shared tables at a time, that keeping the shared tables in a separate file (Expenses.mbd) might be faster for the user in case someone had Income.mbd opened. But, I'm making _way_ to many assumptions here and could some some advice for someone with experience with the performance issues of linking databases together.

It may be worth mentioning that our office is small -- 4 employees. It may grow, but I don't think they'll ever be more than a half dozen or so TimeCard.mbd files accessing shared tables at any given time.

Thanks in advance!
Rich

 

[morrisdev]

Rich,
with only 4 users, I doubt that you will have any need for more than one DB. Access has many limitations, but it can be designed to handle many users.
In a case like yours, where the users are filling out their own reports and others are not modifying them, the risk of corruption is minimal.
If your office gets to be more than 12-15 people, you may want to upgrade to an Access Project. Essentially, such an application consists of all of your forms and reports in Access, with all of your tables in SQL Server. I have built several with amazing success. With 4 people, however, that would be overkill.

Dan Morris