Compacting multiple times while DB open (Access 97)

[janerussel]

I have a long procedure that I have broken into separate functions.  But I need to compact the DB four times during the procedure.  I have pasted some of the code below to give an idea of what I mean.  I tried the function described above in the 2000 posting.  It didn't work for me.<br>The name of the database is budget.mdb<br><br><br>Private Sub RunUpdate_Click()<br><br>    modDeleteTables<br>    <br>    modImport<br><br>    *Need to compact here<br><br>    modQ1<br>    <br>    *Need to compact here<br><br>End Sub<br><br>Function modDeleteTables()<br><br> If ObjectExists(acTable, "PROJECT") Then DoCmd.DeleteObject acTable, "PROJECT"<br> If ObjectExists(acTable, "VENDOR") Then DoCmd.DeleteObject acTable, "VENDOR"<br> If ObjectExists(acTable, "EMPLOYEE") Then DoCmd.DeleteObject acTable, "EMPLOYEE"<br> If ObjectExists(acTable, "Exprate") Then DoCmd.DeleteObject acTable, "Exprate"<br> If ObjectExists(acTable, "TblCDPeta") Then DoCmd.DeleteObject acTable, "TblCDPeta"<br> If ObjectExists(acTable, "TblJobExpPeta") Then DoCmd.DeleteObject acTable, "TblJobExpPeta"<br> If ObjectExists(acTable, "TblLaborSumPeta") Then DoCmd.DeleteObject acTable, "TblLaborSumPeta"<br> If ObjectExists(acTable, "TblLbr") Then DoCmd.DeleteObject acTable, "TblLbr"<br><br>modDeleteTables_Exit:<br>    Exit Function<br><br>modDeleteTables_Err:<br>    MsgBox Error$<br>    Resume modDeleteTables_Exit<br><br>End Function<br><br>Function modImport()<br><br>' Import records from Sema4<br>    <br>    DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _<br>        acTable, "Exprate.dbf", "Exprate", False<br>    <br>modImport_Exit:<br>    Exit Function<br><br>modImport_Err:<br>    MsgBox Error$<br>    Resume modImport_Exit<br><br>End Function<br><br>Function modQ1()<br><br> DoCmd.OpenQuery "QEMPLOYEE", acNormal, acEdit   'Make EMP NEW<br>  DoCmd.Rename "EMPLOYEE", acTable, "EMPLOYEENEW" 'RENAME EMPLOYEE<br>  'DoCmd.OpenQuery "QJobExpAppnd", acNormal, acEdit<br>  <br>  If ObjectExists(acTable, "JOBEXPAC") Then DoCmd.DeleteObject acTable, "JOBEXPAC"<br>  If ObjectExists(acTable, "TIMEARC") Then DoCmd.DeleteObject acTable, "TIMEARC"<br>  <br>modQ1_Exit:<br>    Exit Function<br><br>modQ1_Err:<br>    MsgBox Error$<br>    Resume modQ1_Exit<br><br>End Function

 

[elizabeth]

Sorry, don't know what &quot;the function above&quot; refers to, these threads do not stay in the same order. It's generally better to add follow-up questions to the original post if you want people to be able to reference them.<br><br>I'm not sure what your problem is as you don't write a specific question. If you are trying to reduce the number of compacts, you might re-order your program flow so that you delete all objects no longer needed at the <i>end</i> of the procedure, instead of the beginning.Then you can just compact once at the end.