How to Move records?

T

Tim McGavin

I need to write some code that will
move some records from one table to another.

Is there an easy way to do this? or do I have
to put every single value into a variable when passing
it from one table to the other?

help
 
P

pietlinden

I need to write some code that will
move some records from one table to another.

Is there an easy way to do this? or do I have
to put every single value into a variable when passing
it from one table to the other?

help
Click to expand...

I'd use an append and a delete query wrapped in a transaction if I
absolutely had to move the records. Otherwise, I'd just put a boolean/
flag variable in the table (like Active/Inactive) and then just set
that to true or false...
 
K

krissco

I will add that often this questions is a result of trying to archive
data. With Access it is almost always better to archive old data by adding
a simple binary (yes - no) field so you can tell access that it is archived
and then hide of display as you wish.
Click to expand...

Better? I've never heard that before. How is it better? Maybe easier -
sure. You wouldn't have to set up relationships or create new queries
for the Archived table. I'm wondering about query speed - the larger
the table . . . This may not be an issue if things are properly
indexed.

I'm interested to hear your thoughts.

-Kris
 
R

Rick Brandt

krissco said:
Better? I've never heard that before. How is it better? Maybe easier -
sure. You wouldn't have to set up relationships or create new queries
for the Archived table. I'm wondering about query speed - the larger
the table . . . This may not be an issue if things are properly
indexed.

I'm interested to hear your thoughts.

-Kris
Click to expand...

Table size should rarely equate to peformance (when things are done right).

The archive flag is better because it more closely models the real world and is
less likely to have things go wrong.
 
K

krissco

Table size should rarely equate to peformance (when things are done right).
The archive flag is better because it more closely models the real world and is
less likely to have things go wrong.
Click to expand...

Hmmm. I have one db in development right now. . . I'll have to try
this out. Would you recommend indexing the Archive flag? It seems a
little wasteful being as how there are only two possible values.

-Kris
 
T

Tim McGavin

As noted a append and delete query is the usual way. What specific
needs do you have?

I will add that often this questions is a result of trying to archive
data. With Access it is almost always better to archive old data by
adding a simple binary (yes - no) field so you can tell access that it is
archived and then hide of display as you wish.
Joseph Meehan
Click to expand...

Joseph,

Thanks for your remarks. You hit the nail on the head. I was wanting
to move records for archiving purposes. But part of the reason I wanted
to archive records is because when I do a search it takes forever. And
this situation would still exist even if I made an Archive flag right?

If I do end up moving records, I would love to get it done quickly using
a SQL string. But from what I can tell I have to actually make a recordset,
and import the date into variables right?
 
K

krissco

Thanks for your remarks. You hit the nail on the head. I was wanting
to move records for archiving purposes. But part of the reason I wanted
to archive records is because when I do a search it takes forever. And
this situation would still exist even if I made an Archive flag right?
Click to expand...

So the underlying issue is not archiving - but rather speed of the
table. Are the queries you use looking up values on non-indexed
fields? Are you talking about a sequential scan of your table (all
records) taking forever?

Examine your indexes - heck, you might even get some use out of the MS
analyzer.

If I do end up moving records, I would love to get it done quickly using
a SQL string. But from what I can tell I have to actually make a recordset,
and import the date into variables right?
Click to expand...

If you need to move things into variables - its new to me. *Classic*
archiving can be accomplished by an INSERT and DELETE query pair
(insert into tblArchive Select * from tblCurrent. . . delete * from
tblCurrent. . . ).

-Kris
 
Top