Splitting DB in more than 1 BE

[Guest]

Hello experts, need your help!

First of all, I will describe our department needs and available technical
solutions. Then, I will ask you a few questions concerning the application of
FE/BE principles in our case.

We are in an Inventory Control Department. Corporation just allowed us to
create DBs on MsAccess. We do and will not have any kind of access to SQL
server, even in read-only. So, we develop solutions using MsAccess, MsExcel
and programming.

Here is our situation: Our DBs being oversized, we are starting currently to
divide them in more than 1 BE for 1 FE (BE being actually DB with tables
only). I know the importance of splitting FE/BE for avoiding corruption,
increase efficiency and performance. How can we maximize our database when in
this case we can not use the Database Splitter fonction integrated in
MsAccess because, obviously, it divide DB in 1 FE and 1 BE?

Considering that each user will have an independant FE on his PC that will
retreive and manage selected data from our several "BE" and that all "BE"
will remain to be read-only and static, should we just point on our "BE" as
it is (DBs with tables only, not splitted with Database Splitter) or should
we modify it as real BE and if so, how?

Thanks a lot for your advice,

Pascale

 

[Douglas J Steele]

Splitting manually is pretty simple.

Make a copy of the MDB. Delete all of the queries, forms, reports, macros
and modules. Poof! You're done.

If you want multiple back-ends, make copies of the MDB from above. In each
copy, delete all but those tables you want in that back-end. Remember that
you cannot have relationships between tables if they're not in the same
physical MDB: if you want to enforce at least some RI, keep that in mind
when deciding which tables to put in each back-end.

 

[Roger Carlson]

I'm not sure I understand the question, but I do have a question of my own:
How do you plan to split your BE? There are two methods that I can see:

1) Have identical BE databases, each separated by some time periods. (Both
Customer table and Order table in all BE's but one BE is for 2004 while the
other covers 2005.)

2) Leave all the tables intact, but put them in separate BE. (For instance,
your Customer table in one BE covering 2004-2005, while the Order table
would be in another BE.)

Each method has advantages and disadvantages. What was your plan?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L