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?

Thanks,
John
rugby148Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flavoCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shanesuebsahakarnCommented:
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?
0
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.

Thanks,
John
0
flavoCommented:
Sounds like you have things under control.

Good luck!

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.