Moving Records to Seperate Tables in a Different .mdb File

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I was wondering if anyone knows how I can do this.

What I would like to do is .... after selecting a record
in my form (sign inventory) and subform (sign history), I
would like to take the sign and its history I've selected
and click a button/control and move the records to another
table(s) in a seperate .mdb file.

For example for archived signs, if the sign is archived,
the sign and it's history do not need to be in the tables
any longer but I want to keep an archive date file by year
of the archived data (ArchivedSignInventory2003 &
ArchivedSignHistory2003 tables).

Would it be possible to have a button (on the form) to
take the records I've selected from the form and place
them in the two archived tables in a seperate .mdb file?

I would appreciate anybody's expertise. Thanks!
 
You can certainly do this, but its almost never necessary. Just use the
record date to limit the records returned in your queries for normal use.
As long as the records are indexed by date, it makes negligible difference
to the handling time to retrieve from a huge file, or a small one. Then you
still have the history easily available, without having to write
queries/forms for another database. The only case where physically moving
the records would be justified is if the .mdb file is getting too big, say
over 1 GB or so.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
I was wondering if anyone knows how I can do this.

What I would like to do is .... after selecting a record
in my form (sign inventory) and subform (sign history), I
would like to take the sign and its history I've selected
and click a button/control and move the records to another
table(s) in a seperate .mdb file.

For example for archived signs, if the sign is archived,
the sign and it's history do not need to be in the tables
any longer but I want to keep an archive date file by year
of the archived data (ArchivedSignInventory2003 &
ArchivedSignHistory2003 tables).

Would it be possible to have a button (on the form) to
take the records I've selected from the form and place
them in the two archived tables in a seperate .mdb file?

I would appreciate anybody's expertise. Thanks!
Click to expand...

You'll need to run several queries: an Append query for the inventory
table, followed by a second Append query for the history (to maintain
relational integrity), followed by one or two Delete queries
(depending on whether you have Cascade Deletes enabled). You can use
File... Get External Data... Link to link to the archive database,
permanently or just for the occasion.

It may be prudent to wrap all the queries in a Transaction - see the
online help for the OpenTransaction method for details, or post back.
 
Back
Top