Relationships - Multiple Back End Files

[Jeff Freilich]

Hi All

I have a quetion regarding Relationships

I have created a standard FE/BE database that works over a network -
most users can access the system find and the speed is manageable -
there are a couple of users who are having speed issues so I am
assuming this is mostly a network issues (I have tweaked queries,
maximized my use of indexes, and tried a bunch of different variations
to try and speed things up)

What I am trying now is to split my back end file into 2 files - one
that will store all of the User level information -BackEndD,mdb
(information that gets updated on a regular bases - Activities,
Opportunities etc.) and the othee one with the Lookup/Validation
tables that do not get updated regularly BackEndV.mdb - my thought was
to place the BackEndV.mdb on the users system (will actually copy a
new version of this over each time the user logs in to the system so
they will always have the most up to date Validation tables).

With more information residing locally - and less residing on the
network I was assuming this would in face speed up everything as it
would not be pulling as much information over the network (have the
tables would reside in a local back end file) - but the reverse
actually happened - it actually slowed things down considerably - when
I split the Back end file into 2 files it broke many of the
relationships that were originally stored in the back end file.

So after my long explanation I have a couple of questions:

1. is it a good idea to have multiple back end files - is what I am
attempting even proper?
2. If I have multiple back end files where should the relationships
reside (my understanding is that relationships should be stored in the
back end - asuming a signel back end)
3. if I "re-attach" the relationships in the front end (with links to
all tables in both Back End Files) will it make any difference to
performance and speed?

If you need any more details on anything please let me know - if you
have any other thoughts or ideas it would be greatly appreciated

Thanks a lot for your feedback

Jeff

 

[Douglas J. Steele]

You cannot have relationships between databases. If you want to have
relationships, you cannot split your back-end.

 

[Jeff Freilich]

Thanks Doug

That is what I thought - so if I understand correctly than - even if I
"add" relationships in the front end through would not do anything?

So is it ever a good idea to split back end databases in the manner in
which I was trying to do - or only if no relationships exists at all
with the tables in the different BE files?

And since the system seems slower with the 2 BE files and no
relationships probably not a very wise move to go this route I guess

Jeff

 

[Paul Shapiro]

If only some of the computers are slow, I'd look at those computers.
Assuming they already have enough ram and a decent cpu, network drivers can
have a big impact on speed, so see if you can find newer ones. Are the slow
computers running the same OS and Access version, with the same service
packs and hotfixes?

Thanks Doug

That is what I thought - so if I understand correctly than - even if I
"add" relationships in the front end through would not do anything?

So is it ever a good idea to split back end databases in the manner in
which I was trying to do - or only if no relationships exists at all
with the tables in the different BE files?

And since the system seems slower with the 2 BE files and no
relationships probably not a very wise move to go this route I guess

Jeff

 

[Douglas J. Steele]

Correct. Relationships in the front-end do nothing.

If you think about it, it makes sense. The main point of relationships is to
provide referential integrity. You cannot enforce RI if the data's in
different databases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug

That is what I thought - so if I understand correctly than - even if I
"add" relationships in the front end through would not do anything?

So is it ever a good idea to split back end databases in the manner in
which I was trying to do - or only if no relationships exists at all
with the tables in the different BE files?

And since the system seems slower with the 2 BE files and no
relationships probably not a very wise move to go this route I guess

Jeff