splitting database

[Jean-Paul]

Hi,
I know how to split my database so tables are in a different file then
the rest of my project.
But, I want to setup my project so that some tables are in file a.mdb
and some are in file b.mdb.
How can I do that?
Thanks
JP

 

[Douglas J. Steele]

Assuming the tables all exist in one place now, the easiest approach is
probably to make multiple copies of the existing database, then go into each
copy and delete whatever you don't want in that particular one. (Remember to
compact the database after you've done the deletions).

The complication then becomes one of knowing which table belongs to which
database if you move one or more of the back-ends and have to relink. One
approach is to have a table in your front-end that knows which from which
back-end each table comes.

 

[Jeff Boyce]

Jean-Paul

You've described "what" you want to do.

Now, "why"? What business need are you trying to solve by putting some
tables in one .mdb file and some in another?

I ask, not from simple curiosity, but because knowing more about your
underlying business need might help the folks who volunteer here offer more
specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

Hey Jeff.... thanks for the reply and the "willing to help".

I try to explain what I'm up to (in the best English I know <BG>)

I wrote a project for a school my wife works in.
I use a table with all students, adresses and so on, and a table with
the teachers details.
Beside these tw tables, I use tables where teachers all store their
proper data in. These tables differ form teacher to teacher.
The idea is that when I put the first 2 tables in file a and the rest in
file b, I only have to send each teacher file a when data change. The
tables in file b are "individual" and will never be changed in global.

Hope you understand the situation better now.

Now I splitted the project in a table file and a "program" file, but I
want to get 2 different table files and a program file.

Thanks for your kind help
JP Belgium (normally speaking Dutch)

 

[Jeff Boyce]

Jean-Paul

You could "split" your database as you've described, and, depending on how
you name your "file b", you might not have any issues.

Another consideration, since you are talking about the possibility of
replacing the "file a" with a new/updated version, could be to keep the
application/forms/queries/reports/etc in "file a" AND the two tables you'll
be updating. Then the only portion that would need to be linked would be
the tables in "file b".

Just a thought...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

Hi,
Sorry to keep bothering you....

When I want to split the project as you suggested (keeping the 2 main
tables in the same file as the report/querry...) I haven't got the
possibility to select which table I want in the new file... all tables
finally arrive in the new "splitted" file.

Any help is welcome

JP

 

[Jeff Boyce]

Jean-Paul

I'm having a bit of difficulty envisioning your situation.

If I had a single .mdb file with everything in it, and wanted to end up
with, say 5 tables in another .mdb file, and link to those from a "new"
front-end (?your "file a"), here's what I'd do:

* make a couple copies of the (currently single) .mdb file
* remove EVERYTHING except those 5 tables from one of the copies (your new
"file b")
* remove THOSE FIVE tables from the other copy (your new "file a")
* open "file a" to the database window, click the Tables tab, and "link"
to the 5 tables in "file b"

Run "file a" ... it has all the forms/code/queries/etc., the two tables, and
links to the 5 other tables.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

seems to be OK now....
Thanks Jeff, just forgot there was something like a right-mouse click!
JP