Moving Data from One Access Table to another

[Nidge]

I have two tables in an MS Access database and the tables have a one to many relationship.
The data in TableCurrentLog is the Parent table (the one in the relationship)has the Primary Key, LogNumber.
The data in TableCurrentLogProgressions has multiple instances of the LogNumber and so has a Foreign key called LogNumber.

At a certain point some of the data in these two tables is considered to be no longer current (expired).
When this happens the data from each of these tables is individually rounded up with a query, using two Ado data controls.
For example....

adoCurrentLog.Recordsource = "SELECT * FROM TableCurrentLog WHERE LogNumber = " & DataCombo1.Text
adoCurrentLogProgressions.RecordSource = "SELECT * FROM Table TableCurrentLogProgressions " _
"WHERE LogNumber = " & DataCombo1.Text

The DataCombo1 purely holds all of the LogNumbers that require moving (or Archiving) it just aids the selection.


The resulting ado.Recordsets contain all of the data needed.

The CurrentLogProgressions Recordset can hold many records while the CurrentLog will only ever return one record in the query.
Next this data held in the recorsets needs moving to two other tables which have been created in Access and are indentical
except for their names.
The two other tables are ArchiveCurrentLog & ArchiveCurrentLogProgressions. ( 1 to many relationship )
What I want to do is to be able to create the queries on each of the first two tables (as above) and move the resulting recordsets from each query to the new table.
The Archive tables will grow as new data is added, therefore the existing data will need to be preserved as new archives are added.
I understand the .AddNew......Delete.......Movelast.........MoveFirst functions and can extract the required data, but don't know how to move it to a new table, easily and reliably.
Is it true that when data is removed (deleted) from a table that the database filesize does not dynamically shrink with it?
How can this be overcome?

I am relativly new to DB programming...be kind if you can...

[JHausmann]

Originally posted by Nidge

The CurrentLogProgressions Recordset can hold many records while the CurrentLog will only ever return one record in the query.
Next this data held in the recorsets needs moving to two other tables which have been created in Access and are indentical
except for their names.
The two other tables are ArchiveCurrentLog & ArchiveCurrentLogProgressions. ( 1 to many relationship )
What I want to do is to be able to create the queries on each of the first two tables (as above) and move the resulting recordsets from each query to the new table.
The Archive tables will grow as new data is added, therefore the existing data will need to be preserved as new archives are added.
I understand the .AddNew......Delete.......Movelast.........MoveFirst functions and can extract the required data, but don't know how to move it to a new table, easily and reliably.
Is it true that when data is removed (deleted) from a table that the database filesize does not dynamically shrink with it?
How can this be overcome?

I am relativly new to DB programming...be kind if you can...


Because the structures are the same, you can use a "Insert Select" statement to copy the data from one table to the other. I assume you will delete the records from the old table after copying them. It will be faster to use the insert/select and then delete than to step thru the data using ".AddNew....Delete.....Movelast.......MoveFirst ".

for example the following SQL will move the data:

insert into ArchiveCurrentLog SELECT * FROM TableCurrentLog WHERE LogNumber = " & DataCombo1.Text
insert into ArchiveCurrentLogProgressions "SELECT * FROM TableCurrentLogProgressions WHERE LogNumber = " & DataCombo1.Text

As to your second question, periodically compacting the database will recover the "lost" disc space.