Reissuing a DAO Query
 

Hi there,

I've got a DAO query that I want to run twice, once before a database
update and again after the update:

Sub ArchiveData()
' This function Copies Data from the Activity Table to the Achive
Table via a query then
' performs a second query to delete the entries from the Activity
table

'Define the Variables
Dim dbBackupRecord As DAO.Database
Dim qryActivity As DAO.QueryDef
Dim qryAchive As DAO.QueryDef
Dim rsActivityCount As DAO.Recordset
Dim rsAchiveCount As DAO.Recordset

Dim DatabaseName As String
DatabaseName = Sheets("MyQuery").Range("DatabaseLocation").Value

' Define the RecordSets
Set dbBackupRecord = OpenDatabase(DatabaseName)
Set qryActivityCount =
dbBackupRecord.QueryDefs("qryCountActivity")
Set qryArchiveCount = dbBackupRecord.QueryDefs("qryCountArchive")

' Get the number of records for both tables and store them on the
form
Application.StatusBar = "Querying the size of the Activity and the
Archive Tables"
Set rsActivityCount = qryActivityCount.OpenRecordset(dbReadOnly)
Set rsArchiveCount = qryArchiveCount.OpenRecordset(dbReadOnly)
ActivityBefore = rsActivityCount.Fields("ActivityCount")
ArchiveBefore = rsArchiveCount.Fields("ArchiveCount")
frmMaintenance.ActivityBefore = ActivityBefore
frmMaintenance.ArchiveBefore = ArchiveBefore

' Close the Queries so they can be reopened and requeried
rsActivityCount.Close
rsArchiveCount.Close
Set rsActivityCount = Nothing
Set rsAchiveCount = Nothing
Set qryActivity = Nothing
Set qryAchive = Nothing

' Execute the Append Query
Application.StatusBar = "Copying Aged Records to the Archive
Table"
dbBackupRecord.QueryDefs("qryAppendOldRecordsToArc hive").Execute

' Execute the Delete Query
Application.StatusBar = "Deleting copied records from the Activity
Table"

dbBackupRecord.QueryDefs("qryDeleteOldRecordsFromA ctivity").Execute

' Get the number of records after the change for both tables and
store them on the form
Set qryActivityCount =
dbBackupRecord.QueryDefs("qryCountActivity")
Set qryArchiveCount = dbBackupRecord.QueryDefs("qryCountArchive")
Set rsActivityCount = qryActivityCount.OpenRecordset(dbReadOnly)
Set rsArchiveCount = qryArchiveCount.OpenRecordset(dbReadOnly)

ActivityAfter = rsActivityCount.Fields("ActivityCount")
ArchiveAfter = rsArchiveCount.Fields("ArchiveCount")
frmMaintenance.ActivityAfter = ActivityAfter
frmMaintenance.ArchiveAfter = ArchiveAfter

' Close the Records Sets and the Database
rsActivityCount.Close
rsArchiveCount.Close
dbBackupRecord.Close
Set rsActivityCount = Nothing
Set rsAchiveCount = Nothing
Set qryActivity = Nothing
Set qryAchive = Nothing
Set dbBackupRecord = Nothing

frmMaintenance.ActivityChange = ActivityAfter - ActivityBefore
frmMaintenance.ArchiveChange = ArchiveAfter - ArchiveBefore

frmMaintenance.Show

End Sub

The phrase:
' Close the Queries so they can be reopened and requeried
rsActivityCount.Close
rsArchiveCount.Close
Set rsActivityCount = Nothing
Set rsAchiveCount = Nothing
Set qryActivity = Nothing
Set qryAchive = Nothing

.... seems a little clumsy, but I coded it to ensure my queries run
again once reopened. Is there a more elegant way to do this?

Regards

Greg

p.s. On another matter, I'm running an 'append query' and then a
'delete query'. Is there such a thing a 'move query' to enable me to
do the database maintenance in one step?