Compacting with code multiple times

janerussel · Jul 14, 2000

I have a long procedure that needs to be compacted multiple times.  I have used the function in FAQ's using the keycode.  If I prune down my code the compact works the first time, but with this long procedure it seems to skip right over the call to the function.  I tried using the function in a separate module and then putting it in the same module.  Anyone know what's going on here.  The code is long but here it is. ======= Option Compare Database Option Explicit Private Sub RunUpdate_Click()  DoCmd.Hourglass True  DoCmd.SetWarnings False  If ObjectExists(acTable, "PROJECT&quot

Then DoCmd.DeleteObject acTable, "PROJECT"  If ObjectExists(acTable, "VENDOR&quot

Then DoCmd.DeleteObject acTable, "VENDOR"  If ObjectExists(acTable, "EMPLOYEE&quot

Then DoCmd.DeleteObject acTable, "EMPLOYEE"  If ObjectExists(acTable, "Exprate&quot

Then DoCmd.DeleteObject acTable, "Exprate"  If ObjectExists(acTable, "TblCDPeta&quot

Then DoCmd.DeleteObject acTable, "TblCDPeta"  If ObjectExists(acTable, "TblJobExpPeta&quot

Then DoCmd.DeleteObject acTable, "TblJobExpPeta"  If ObjectExists(acTable, "TblLaborSumPeta&quot

Then DoCmd.DeleteObject acTable, "TblLaborSumPeta"  If ObjectExists(acTable, "TblLbr&quot

Then DoCmd.DeleteObject acTable, "TblLbr"  If ObjectExists(acTable, "TblLbr10&quot

Then DoCmd.DeleteObject acTable, "TblLbr10"  If ObjectExists(acTable, "TblLbr20&quot

Then DoCmd.DeleteObject acTable, "TblLbr20"  If ObjectExists(acTable, "TblLbr30&quot

Then DoCmd.DeleteObject acTable, "TblLbr30"  If ObjectExists(acTable, "TblLbr35&quot

Then DoCmd.DeleteObject acTable, "TblLbr35"  If ObjectExists(acTable, "TblLbr40&quot

Then DoCmd.DeleteObject acTable, "TblLbr40"  If ObjectExists(acTable, "TblLbr50&quot

Then DoCmd.DeleteObject acTable, "TblLbr50"  If ObjectExists(acTable, "TblLbr60&quot

Then DoCmd.DeleteObject acTable, "TblLbr60"  If ObjectExists(acTable, "TblLbrArc&quot

Then DoCmd.DeleteObject acTable, "TblLbrArc" If ObjectExists(acTable, "PROJECT&quot

Then DoCmd.DeleteObject acTable, "PROJECT"  If ObjectExists(acTable, "VENDOR&quot

Then DoCmd.DeleteObject acTable, "VENDOR"  If ObjectExists(acTable, "EMPLOYEE&quot

Then DoCmd.DeleteObject acTable, "EMPLOYEE"  ' Import records from Sema4                              DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "project.dbf", "PROJECT", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "EMPLOYEE.dbf", "EMPLOYEE", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "time.dbf", "TIME", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "timearc.dbf", "TIMEARC", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "jobexp.dbf", "JOBEXP", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "jobexpac.dbf", "JOBEXPAC", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "Vendor.dbf", "Vendor", False     DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _         acTable, "Exprate.dbf", "Exprate", False                                             Beep   MsgBox "Sema4 import is COMPLETE", vbOKOnly, "BUDGET UPDATE VALIDATION"      'Call the function to update Budget   compact_database      DoCmd.OpenQuery "QEMPLOYEE", acNormal, acEdit   'Make EMP NEW   DoCmd.Rename "EMPLOYEE", acTable, "EMPLOYEENEW" 'RENAME EMPLOYEE   DoCmd.OpenQuery "QJobExpAppnd", acNormal, acEdit   DoCmd.OpenQuery "QTimeAppnd", acNormal, acEdit   DoCmd.Rename "JOBEXPNEW", acTable, "JOBEXP"   DoCmd.Rename "TIMENEW", acTable, "TIME"      If ObjectExists(acTable, "JOBEXPAC&quot

Then DoCmd.DeleteObject acTable, "JOBEXPAC"   If ObjectExists(acTable, "TIMEARC&quot

Then DoCmd.DeleteObject acTable, "TIMEARC"      'Call the function to update Budget    compact_database     ' QJOBEXP-A     DoCmd.OpenQuery "QJobExp-N-A", acNormal, acEdit     DoCmd.DeleteObject acTable, "JOBEXPNEW"     ' QJOBEXP-B     DoCmd.OpenQuery "QJobExp-NewLnk-B", acNormal, acEdit     ' QJOBEXP-C     DoCmd.OpenQuery "QExprate-N-C", acNormal, acEdit     ' QJOBEXP-D     DoCmd.OpenQuery "QExprate-NewLnk-D", acNormal, acEdit     ' QJOBEXP-N     DoCmd.OpenQuery "QJobExp-Exprate-N", acNormal, acEdit     ' QJOBEXP-N1     DoCmd.OpenQuery "QJobexp-Exprate-N1", acNormal, acEdit     ' QJOBEXP-N2     DoCmd.OpenQuery "QJobExp-Exprate-N2", acNormal, acEdit     ' QJOBEXP-N3     DoCmd.OpenQuery "QJobExp-Exprate-N3", acNormal, acEdit     ' QJOBEXP-N4     DoCmd.OpenQuery "QJobExp-Exprate-N4", acNormal, acEdit     ' QJOBEXP-N5     DoCmd.OpenQuery "QJobExp-Exprate-N5", acNormal, acEdit     ' QJOBEXP-N6     DoCmd.OpenQuery "QJobExp-Exprate-N6", acNormal, acEdit     ' MakeCashDisbTable     DoCmd.OpenQuery "QDetailCDPeta", acNormal, acEdit     ' MakeJobExpTable     DoCmd.OpenQuery "QDetailJobExpPeta", acNormal, acEdit     ' MakePJTable     DoCmd.OpenQuery "QDetailPJPeta", acNormal, acEdit     ' MakeTblPetaLaborTable     DoCmd.OpenQuery "QLaborPeta", acNormal, acEdit     ' MakeTblArcLbrHrsEmp Table     DoCmd.OpenQuery "QArlLbrHrsEmp", acNormal, acEdit     ' MakeTblLbrHrsDptPhase Table     DoCmd.OpenQuery "QLbrHrsPhs", acNormal, acEdit     ' MakeTbTbllLbrCnsHrsPhs     DoCmd.OpenQuery "QLbrCnsHrsPhs", acNormal, acEdit     ' MakeTblCDPeta     DoCmd.OpenQuery "QCDPeta", acNormal, acEdit     ' MakeTblJobExpPeta     DoCmd.OpenQuery "QJobExpPeta", acNormal, acEdit     ' MakeTbLaborSumPeta     DoCmd.OpenQuery "QLaborSumPeta", acNormal, acEdit     ' MakeTbLaborHrsSum     DoCmd.OpenQuery "QLbrSumHrs", acNormal, acEdit     ' MakeTblLbr     DoCmd.OpenQuery "QLbr", acNormal, acEdit     ' MakeTblLbr10     DoCmd.OpenQuery "QLbr10", acNormal, acEdit     ' MakeTblLbr20     DoCmd.OpenQuery "QLbr20", acNormal, acEdit     ' MakeTblLbr30     DoCmd.OpenQuery "QLbr30", acNormal, acEdit     ' MakeTblLbr35     DoCmd.OpenQuery "QLbr35", acNormal, acEdit     ' MakeTblLbr40     DoCmd.OpenQuery "QLbr40", acNormal, acEdit     ' MakeTblLbr50     DoCmd.OpenQuery "QLbr50", acNormal, acEdit     ' MakeTblLbr60     DoCmd.OpenQuery "QLbr60", acNormal, acEdit     ' MakeTblLbrArc     DoCmd.OpenQuery "QLbrArc", acNormal, acEdit     ' WriteOffArcTotal     DoCmd.OpenQuery "QWOArcTot", acNormal, acEdit     ' WriteOffCnsTotal     DoCmd.OpenQuery "QWOCnsTot", acNormal, acEdit     ' WriteOffTots     DoCmd.OpenQuery "QWOTots", acNormal, acEdit     ' MakeTblSASReimb     DoCmd.OpenQuery "QSASReimb", acNormal, acEdit     ' MakeTblSASTime     DoCmd.OpenQuery "QSASTime", acNormal, acEdit   'Call the function to update Budget    compact_database     ' Empty Job budget Table     DoCmd.OpenQuery "EmptyBudg", acNormal, acEdit     ' Update Job Budget Table with new Data #1<     DoCmd.OpenQuery "TotBudg<", acNormal, acEdit     ' Update Job Budget Table with new Data #2     DoCmd.OpenQuery "TotBudg<2", acNormal, acEdit     ' Update Job Budget Table with new Data #3     DoCmd.OpenQuery "TotBudg<3", acNormal, acEdit     ' Update Job Budget Table with new Data #4     DoCmd.OpenQuery "TotBudg<4", acNormal, acEdit     ' Update Job Budget Table with new Data #5     DoCmd.OpenQuery "TotBudg<5", acNormal, acEdit     ' Update Job Budget Table with new Data #6     DoCmd.OpenQuery "TotBudg<6", acNormal, acEdit     ' Update Job Budget Table with new Data #1>     DoCmd.OpenQuery "TotBudg>", acNormal, acEdit     ' Update Job Budget Table with new Data #2     DoCmd.OpenQuery "TotBudg>2", acNormal, acEdit     ' Update Job Budget Table with new Data #3     DoCmd.OpenQuery "TotBudg>3", acNormal, acEdit     ' Update Job Budget Table with new Data #4     DoCmd.OpenQuery "TotBudg>4", acNormal, acEdit     ' Update Job Budget Table with new Data #5     DoCmd.OpenQuery "TotBudg>5", acNormal, acEdit     ' Update Job Budget Table with new Data #6     DoCmd.OpenQuery "TotBudg>6", acNormal, acEdit   'Call the function to update Budget   compact_database     DoCmd.OpenQuery "QLbrOfficeAz", acNormal, acEdit     DoCmd.OpenQuery "QLbrOfficeConc", acNormal, acEdit     DoCmd.OpenQuery "QLbrOfficeLa", acNormal, acEdit     DoCmd.OpenQuery "QLbrOfficeSac", acNormal, acEdit     DoCmd.OpenQuery "QLbrOfficeWa", acNormal, acEdit     DoCmd.OpenQuery "QLbrOfficeCnslt", acNormal, acEdit     DoCmd.OpenQuery "QReimbOfficeAz", acNormal, acEdit     DoCmd.OpenQuery "QReimbOfficeConc", acNormal, acEdit     DoCmd.OpenQuery "QReimbOfficeLa", acNormal, acEdit     DoCmd.OpenQuery "QReimbOfficeSac", acNormal, acEdit     DoCmd.OpenQuery "QReimbOfficeWA", acNormal, acEdit          'export remote office data out for dispersal          DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\Arizona", acTable, "TblLaborAz", "TblLaborAz", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\EB", acTable, "TblLaborEb", "TblLaborEb", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\LA", acTable, "TblLaborLA", "TblLaborLA", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\SAC", acTable, "TblLaborSAC", "TblLaborSAC", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\WA", acTable, "TblLaborWA", "TblLaborWA", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\Budget", acTable, "TblLbrCn", "TblLbrCn", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\Arizona", acTable, "TblReimbAZ", "TblReimbAZ", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\EB", acTable, "TblReimbEB", "TblReimbEB", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\LA", acTable, "TblReimbLA", "TblReimbLA", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\SAC", acTable, "TblReimbSAC", "TblReimbSAC", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\WA", acTable, "TblReimbWA", "TblReimbWA", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\Budget", acTable, "PROJECT", "PROJECT", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\Budget", acTable, "EMPLOYEE", "EMPLOYEE", False     DoCmd.TransferDatabase acExport, "dBase IV", "d:\Access\Budget", acTable, "VENDOR", "VENDOR", False     DoCmd.DeleteObject acTable, "TblLaborAz"     DoCmd.DeleteObject acTable, "TblLaborEb"     DoCmd.DeleteObject acTable, "TblLaborLa"     DoCmd.DeleteObject acTable, "TblLaborSac"     DoCmd.DeleteObject acTable, "TblLaborWa"     DoCmd.DeleteObject acTable, "TblLbrCn"     DoCmd.DeleteObject acTable, "TblReimbAz"     DoCmd.DeleteObject acTable, "TblReimbEB"     DoCmd.DeleteObject acTable, "TblReimbLA"     DoCmd.DeleteObject acTable, "TblReimbSac"     DoCmd.DeleteObject acTable, "TblReimbWa"          Beep     MsgBox "The budget update is COMPLETE", vbOKOnly, "BUDGET UPDATE VALIDATION"     compact_database End Sub Function compact_database()     SendKeys ("%(W1)&quot

SendKeys ("%(TDC)&quot

End Function

DougP · Jul 15, 2000

That's one major function you have there. Way too big to be in a button on a form. Can you move that function to a module. It can be moved if nothing is refering to "Me!" which is the current form. Those references would have to be changed to  Forms![formname].... when you move it. Then see if it compacts it. It appears from glancing at it that it can be moved. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

PaulF · Jul 15, 2000

My question is:  Doesn't Access close the open database, compact to a new database, delete the old and then rename the new back to the old, then reopen the new database to the form specified in the startup.  So if this is the case how do you ever get pass the first compact in your procedure? Could you place this into a Standard Code Module, break it into several functions (split after each compact).  Create a table to store which function  is next to be performed, then update the table as part of the code (just before doing the compact).  When the database opens it calls a  function (use an Autoexec Macro)  that checks this table to see if the process has been completed, if not then it calls the function name stored in the table.  When the process has been completed you indicate that in the table (i.e. by placing DONE as the function name), then the function will open the normal Opening form.  You would still start the process by a Command Button  which calls the first function, but the table, split functions, and Autoexec Macro would control the rest of it. PaulF

janerussel · Jul 18, 2000

Yes, you are right.  Access closes the open database, compacts to a new one, deletes the old and renames the new back to the old.  I think that's the problem.   I have tried the keycode function I described in my original message.  Oddly enough, that works the first time, but not after that. I tried the code       If Dir("Budget.mdb&quot

<> "" Then Kill "Budget.mdb"       BEngine.CompactDatabase "Budget.mdb", "Budget.mdb"       MsgBox "Process Complete" I get an error "Access denied" I need to compact the database four times because it exceeds the maximum size during this long series of events.  So I need to call a procedure. I have split the code up into separate functions as described above, but I can't figure out how to compact the database in code without closing it. Does anyone know, and if they do could you type out the code.  It's easier to understand when I

PaulF · Jul 18, 2000

You must close the database in order to compact it.  But if you re-read my post, the method you can follow is there. Create five functions, a new table and a autoexec macro.  The first calls the other four based on an entry in the new table.  The second function runs to the point where you need to compact the first time, the third until the second compact, etc.. Prior to the compact command, add a line to open the table and add the next function name to the field in the first record.  Then when you get to the last function and just before the last compact set the value in the table to = Done.  In the first function (which is called from the autoexec macro),  open the new table, capture the function name to be run, then run the function.  If the function name is Done, then Exit the function and open your normal form that you want to have displayed.  To start this use a command button on one of your forms.  This button can either call the second function or it can open the table and enter the second function's name, and then call the first function. Does this make sense? PaulF

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Compacting with code multiple times

janerussel

MIS

DougP

MIS

PaulF

Technical User

janerussel

MIS

PaulF

Technical User

Similar threads

Part and Inventory Search

Sponsor