Maintaining relationships between two or more DB tabkes,

[HMJ]

I current have two databases setup as follows:
Database #1:
a) has 18 tables
b) contains ALL the data
c) is 387300 KBytes (.39GBytes)
Database #2:
a) has ALL querys, forms and reports
b) is linked to database #1
c) is 19750KBytes (.20MBytes)

I have reached the point where response time is beginning to suffer some and I want to split database #1 into two databases - database #1a and database #1b. Currently all tables are relationshiped together via a field called key_no. This field is in each table.

QUESTION: If I split database #1 into two parts, how do I maintain the relationships between the tables? I can't see a way to do this doing relationships, unless I can do it in database #2 after I link to database #1a and #1b.

Any guidance or suggestions?
Has anyone done this?

THANKS. Harry Jessen
HMJessen@Yahoo.com

 

[JeremyNYC]

Harry,

A) That won't improve performance.

B) I don't think you can maintain relationships across databases.

http://www.fmsinc.com/tpapers/faster/index.html

is a paper that has some good tips for how to speed up Access databases.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Darrylles]

Hi,

(A) Jeremy's right - why should performance improve?

(B) You CAN maintain relationships between databases via link tables.

Is the performance issue due to your original table designs? Are they normalised?

The fact that one field links all tables tells me they aren't, meaning that probably 70% of the stored data shouldn't even be there.
So you re-design the database tables, or put up with the performance issue.

Some changes will improve performance, but it's always going to be downhill (or an uphill struggle) from now on.

(Sorry if I sound like 'Jobs comforter', but I think I'll be right).

Regards,

Darrylle







Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[JeremyNYC]

Darrylle,

Of course I could test this myself but...if I link to two databases and make relationships in the front end between the linked tables, will RI be enforcable across databases?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.