Moving record from a table to another one

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi ,

I want to use this for archives...What is the easiest way to do this ?

Is the DELETE from the source and ADDNEW to the new table is the way to do
it ? It must have a easiest way.

Tks...
 
Hi ,

I want to use this for archives...What is the easiest way to do this ?

Is the DELETE from the source and ADDNEW to the new table is the way to do
it ? It must have a easiest way.

Tks...
Click to expand...

Well... add first, THEN delete! Otherwise you're throwing away the
record before you add it!

I'd suggest an Append query to add the new record, followed by a
Delete query to delete it. Wrap both in a Transaction to ensure that
the delete only happens if the addition succeeds (see "Transaction" in
the online help).

Unless your table is many hundreds of thousands of records, and you
have demonstrated performance problems, you may want to instead just
add an indexed yes/no field Archived to the table. Base your form on a
query displaying only the records where this is False (or of course
True to see only archived records). Just checking the checkbox will
"archive" the record with no code, no queries, and no effort.

John W. Vinson[MVP]
 
Just a thought..

Why would you archive records to a seperate table, too much work. Just
create and Archive field in the table and make it Yes/No. Then you and run
an UPDATE query to say 'Yes', hence toggling the archived records not to
show on your form or combo box, etc...

HTH,
Gina Whipp
 
The reason I want ot do this is i'm starting to have over 100 000 records in
my DB and only ~1000 of them are used or active , the other ones are closed
or inactive.

I want to use only the active records when doing the transactions , the rest
is there only for reports or graph.

Is there a way to keep them in the same table and split the active from the
inactive records ?
 
The reason I want ot do this is i'm starting to have over 100 000 records in
my DB and only i m using ~1000 of them and must be active , the other ones
are closed
or inactive.

I want to use only the active records when doing the transactions , the rest
is there only for reports or graph.

Is there a way to keep them in the same table and split the active from the
inactive records ?
 
Is there a way to keep them in the same table and split the active from the
inactive records ?
Click to expand...

Yes. Reread Gina's and my suggestions. That's precisely what we're
telling you how to do - use an indexed Yes/No field and use Queries
selecting those records which are active for your form.

You've got a tradeoff. There may be a bit of a performance hit
searching for just active records (not much of one, at this scale);
but if you have the data in two tables you'll have a MUCH bigger
performance hit creating the necessary UNION query for your charts.

John W. Vinson[MVP]
 
Back
Top