A2K - how to copy a database structure with relationships?

[Deano]

I'm experiencing bloat problems when I restore my backup files.
Users can take a look at the reports of any previous backup without having
to ensure they have a backup of their current data - this means I have to do
a silent backup of the existing database, restore the selected mdb, then
restore the silent backup when they are finished viewing the restored data.
To the user it appears that they are are just able to see reports relating
to any backup file they select

It all works lovely but after an evening of testing this the mdb had bloated
to over 250mb!

Sooooo, I really need a more grown up solution.

One way is to refer to the data directly i.e linking of which I don't have
much experience. However the real problem is this;

I want to copy the backup file (it's password protected) to a temp mdb and
create the relationships between the tables as they exist in the main
database.

Then I can link to this file and amend my code so that the reports run using
this data. Various queries get executed so I need the relationships in
place. I would then delete the temp mdb.

Googling for this hasn't been productive. TransferDatabase only copies one
object at a time (I suppose I could do a loop) so is there a better way of
copying all the tables to a new temp mdb?

And then creating the relationships - I know what these should be so it is
feasible to establish these using some DAO? I have about 98 tables to deal
with.

The database is still single-user and unsplit at this time.
Thanks
Martin

[Deano]

Ah and one more thing, how would I get my queries to use tables in another
mdb as opposed to the local mdb tables that they currently reference? Would
I have to have separate queries or something?

[Phil Stanton]

I mess about with a club membership database. Currently I am involved with
4, no 5 different clubs. I gave a form wich on pressing a button with the
appropriate club name detaches most of the tables on the club that I no
longer want, and links the tables from the other club.

Looks similar, but there is a lot of code and tables and forms. Might be
able to cabbage something together if you are desparate

Phil

"Deano" <ma**********@gmail.com> wrote in message
news:41***********************@ptn-nntp-reader04.plus.net...

I'm experiencing bloat problems when I restore my backup files.
Users can take a look at the reports of any previous backup without having
to ensure they have a backup of their current data - this means I have to
do
a silent backup of the existing database, restore the selected mdb, then
restore the silent backup when they are finished viewing the restored
data.
To the user it appears that they are are just able to see reports relating
to any backup file they select

It all works lovely but after an evening of testing this the mdb had
bloated
to over 250mb!

Sooooo, I really need a more grown up solution.

One way is to refer to the data directly i.e linking of which I don't have
much experience. However the real problem is this;

I want to copy the backup file (it's password protected) to a temp mdb and
create the relationships between the tables as they exist in the main
database.

Then I can link to this file and amend my code so that the reports run
using
this data. Various queries get executed so I need the relationships in
place. I would then delete the temp mdb.

Googling for this hasn't been productive. TransferDatabase only copies
one
object at a time (I suppose I could do a loop) so is there a better way of
copying all the tables to a new temp mdb?

And then creating the relationships - I know what these should be so it is
feasible to establish these using some DAO? I have about 98 tables to
deal
with.

The database is still single-user and unsplit at this time.
Thanks
Martin

[Deano]

Phil Stanton wrote:

I mess about with a club membership database. Currently I am involved
with 4, no 5 different clubs. I gave a form wich on pressing a button
with the appropriate club name detaches most of the tables on the
club that I no longer want, and links the tables from the other club.

Looks similar, but there is a lot of code and tables and forms. Might
be able to cabbage something together if you are desparate

Phil

Cheers, if you're able to simplify something and post the code relating to
that aspect I would be grateful.

[Phil Stanton]

Sorry. Need to send a complete Db

Let me have your email address

Phil
"Deano" <ma**********@gmail.com> wrote in message
news:41***********************@ptn-nntp-reader04.plus.net...

Phil Stanton wrote:

I mess about with a club membership database. Currently I am involved
with 4, no 5 different clubs. I gave a form wich on pressing a button
with the appropriate club name detaches most of the tables on the
club that I no longer want, and links the tables from the other club.

Looks similar, but there is a lot of code and tables and forms. Might
be able to cabbage something together if you are desparate

Phil

Cheers, if you're able to simplify something and post the code relating to
that aspect I would be grateful.

[Deano]

Phil Stanton wrote:

Sorry. Need to send a complete Db

Let me have your email address
OK, mail me at martin dot dean at gmail dot com

thanks

Phil
"Deano" <ma**********@gmail.com> wrote in message
news:41***********************@ptn-nntp-reader04.plus.net...

Phil Stanton wrote:

I mess about with a club membership database. Currently I am
involved with 4, no 5 different clubs. I gave a form wich on
pressing a button with the appropriate club name detaches most of
the tables on the club that I no longer want, and links the tables
from the other club.

Looks similar, but there is a lot of code and tables and forms.
Might be able to cabbage something together if you are desparate

Phil

Cheers, if you're able to simplify something and post the code
relating to that aspect I would be grateful.