Linking to tables from multiple Access databases

Posted on 2004-11-09
Last Modified: 2006-11-17
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?

Question by:rugby148
    LVL 34

    Accepted 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.
    LVL 41

    Expert Comment

    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?

    Author Comment

    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.

    LVL 34

    Expert Comment

    Sounds like you have things under control.

    Good luck!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now