Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
Problem with .Background Query option of ODBC Query | Excel Programming |