More Than One Backend Database

I have a backend database (Backend1.mdb)that has 3 main data tables,
I'll call them Data1, Data2 and Data3, plus several other associated
tables. All relationships are set in the backend database which is
linked to my frontend. Data3 will grow much more quickly than Data1 and
Data2 as it will have much more data added to it on a daily basis. I
would like to keep all of the data from Data1 and Data2 to a manageable
size so that it can easily be zipped and emailed if necessary.

Is there anything wrong with putting Data3 in its own backend database
(Backend2.mdb) and then linking both backends to the frontend and
setting any necessary relationships between the 2 backends in the
frontend?

Thanks in advance.

re: More Than One Backend Database

"Wayne" <cqdigital@volcanomail.com> wrote in message
news:1116542234.564884.310720@f14g2000cwb.googlegr oups.com...
[color=blue]
> Is there anything wrong with putting Data3 in its own backend database
> (Backend2.mdb) and then linking both backends to the frontend and
> setting any necessary relationships between the 2 backends in the
> frontend?[/color]

Business rules are best implemented as primary keys and foreign keys,
enforced by the database engine. I wouldn't do what you're suggesting.

Ease of email seems trivial to me. Don't you want to email Data3 aswell?

Mike

re: More Than One Backend Database

> Is there anything wrong with putting Data3 in its own backend database[color=blue]
> (Backend2.mdb) and then linking both backends to the frontend and
> setting any necessary relationships between the 2 backends in the
> frontend?[/color]

If "necessary relationship" = "referential integrity" then referential
integrity may not be maintained if either backend database is opened
directly.
The backend will be unaware of the rules of the frontend; in fact, it
will be unaware of the existence of the frontend.
I had occasion when this happened to me in a FoxPro application (this is
one of the reasons I left FoxPro and came to JET). I THOUGHT coded RI
was impenetrable. But I didn't take into account a curious System
Administrator who had access to everything. In his playing with the db
he entered two school classes without schools. When we totalled things
up we got 1093 classes; actually there were 1091 classes. This could
have resulted in the improper authorization of the hiring of about three
full time equivalent staff at a cost of about $200 000; someone would
have caught hell about that, maybe me. The error was caught when we did
some spreadsheet analysis of another problem using the FoxPro data and
we noticed the anomaly.
Perhaps you will get advice on how to solve your problem in another way.
My first inclination would be to use MS-SQL, but that's right off the
top of my head and may not be appropriate at all for your situation.

--
--
Lyle

To subject an enemy belligerent to an unfair trial, to charge him with
an unrecognized crime, or to vent on him our retributive emotions only
antagonizes the enemy nation and hinders the reconciliation necessary to
a peaceful world.

Justice Frank Murphy
Yamashita v. Styer, 327 U.S. 1 (1946)

re: More Than One Backend Database

You cannot have relationships that span databases. That means it's
impossible to enforce referential integrity.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Wayne" <cqdigital@volcanomail.com> wrote in message
news:1116542234.564884.310720@f14g2000cwb.googlegr oups.com...[color=blue]
>I have a backend database (Backend1.mdb)that has 3 main data tables,
> I'll call them Data1, Data2 and Data3, plus several other associated
> tables. All relationships are set in the backend database which is
> linked to my frontend. Data3 will grow much more quickly than Data1 and
> Data2 as it will have much more data added to it on a daily basis. I
> would like to keep all of the data from Data1 and Data2 to a manageable
> size so that it can easily be zipped and emailed if necessary.
>
> Is there anything wrong with putting Data3 in its own backend database
> (Backend2.mdb) and then linking both backends to the frontend and
> setting any necessary relationships between the 2 backends in the
> frontend?
>
> Thanks in advance.
>[/color]

re: More Than One Backend Database

Thanks for the replies and help folks. Looks like the only option is
to place all tables in the one backend database.