Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Linking to tables from multiple Access databases

Does anyone know about the effects to performance of dividing one database which is accessed by another into 3 databases.  My reason for doing so is to make the databases a little more organized and manageable and to maintain smaller file sizes.

Unfortunately I will not be able to migrate to SQL for atleast another 9 months.

The current database includes over 75 tables and is over 800 mb.  The split databases will be appr:
20 tables 65mb
40 tables 180mb
15 tables 400mb

The other side benefit is that most of the users will regularily be accessing the smallest and one of the other two databases.  Most will not be accessing all three.

Will performance improve or decline?

  • 2
1 Solution
id say improve.

Considering your current db is over 800mg, im guessing it will already be slowing down, and if you are using Access 97, then it will be almost dead.

I am 100% behind you in you approach.
I use a similar schema. So long as your tables are correctly indexed on all key fields, you should not notice any particular drop in performance - in certain circumstances, performance will actually improve as Jet has to traverse less space to read/write your information (although the improvement may be marginal).

Your filesizes do seem rather high though - do you compact on a regular basis?
rugby148Author Commented:
Thank you both for your insight.

I do compact the databases regularily.  programmatically it is being done 2 times per day.  The database holds multiple tables with a million plus records.

My logical seperation will group all of the operational / generic things in the smallest primary database and data related to each of two major areas in another.

It is all running Access2003.  Some of my client connections are VB.Net apps.

All forms are unbound, all tables are normalized and indexed.  it is rather impressively efficient; however, every day it continues to grow.

The reason for my questions is due to an impending growth accelleration.  currently the database represents 14 months of data.  the rate data is being added will nearly double by year end!

Thanks much for your help and insight both of you.  I don't know how to give (if possible) both answers accepted status and points.

I will accept flavo's answer and create another questions that shanesuebsahakarn can answer.

Sounds like you have things under control.

Good luck!


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now