query to remove records?

[lisa]

I want to remove all records marked as old from my
database, and transfer them preferably into a new
database at the click of a button. Do I use a query to
do this?

 

[Guest]

This is how I did it

Dim rst4 As DAO.Recordset
Dim rst5 As DAO.Recordset
Dim rst6 As DAO.Recordset

Set rst4 = db.OpenRecordset("ApprovedTable")
rst4.MoveFirst
Set rst5 = currentdb.OpenRecordset("HistoryTable")
Set rst6 = db.OpenRecordset("EmployeeTotalTable")
rst6.MoveFirst

Do Until rst4.EOF
rst5.AddNew
rst5!EmployeeNumber = rst4!EmployeeNumber
rst5!HoursWorked = rst4!HoursWorked
rst5!HoursToBePaid = rst4!HoursToBePaid
rst5!HoursToComp = rst4!HoursToComp
rst5!Shift = rst4!Shift
rst5!Reason = rst4!Reason
rst5!Note = rst4!Note
rst5!Rate = rst4!Rate
rst5!CostCenter = rst4!CostCenter
rst5!DateWorked = rst4!DateWorked
rst5!DateEntered = rst4!DateEntered
rst5!ApprovedBy = rst4!ApprovedBy
rst5.Update

rst4.Delete
rst4.MoveNext
Loop

Do Until rst6.EOF
rst6.Delete
rst6.MoveNext
Loop

MsgBox ("Last year's entries have all been moved")
Exit Sub

NoRecords:
MsgBox ("There are no entries for this year")

Of course I did some error checking to begin with.

For instance, if Pending Table had records I wouldn't let
this happen, if there were overtime to pay values or
overtime comp values no go either.

 

[Immanuel Sibero]

Hi Lisa


Yes, use queries.

- Use an append query to load the history table from the active table. This
query should filter only those records marked as old.
- Use a delete query to delete the above filtered records from the active
table.

If you wish to transfer the old records to a different database, you may do
so by creating the new database and the history table. You then link the
history table to your active database.

Please post back if you need more assistance.


HTH
Immanuel Sibero

 

[John Vinson]

I want to remove all records marked as old from my
database, and transfer them preferably into a new
database at the click of a button. Do I use a query to
do this?

Not one query but two: an Append query to copy the records from the
current table into the archive table, followed by a Delete query to
delete the records.

Do you have a *DEMONSTRATED NEED* to do this? It's often more trouble
than it's worth to archive such data; if your tables are properly
indexed, you should be able to get decent performance even with all
the old data included. You can even put in a Yes/No field Archived and
set it to True for records you want flagged as "old"; your forms and
reports would use a query to select only False values for this field.