×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Compacting multiple times while DB open (Access 97)

Compacting multiple times while DB open (Access 97)

Compacting multiple times while DB open (Access 97)

(OP)
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.
The name of the database is budget.mdb


Private Sub RunUpdate_Click()

    modDeleteTables
    
    modImport

    *Need to compact here

    modQ1
    
    *Need to compact here

End Sub

Function modDeleteTables()

 If ObjectExists(acTable, "PROJECT") Then DoCmd.DeleteObject acTable, "PROJECT"
 If ObjectExists(acTable, "VENDOR") Then DoCmd.DeleteObject acTable, "VENDOR"
 If ObjectExists(acTable, "EMPLOYEE") Then DoCmd.DeleteObject acTable, "EMPLOYEE"
 If ObjectExists(acTable, "Exprate") Then DoCmd.DeleteObject acTable, "Exprate"
 If ObjectExists(acTable, "TblCDPeta") Then DoCmd.DeleteObject acTable, "TblCDPeta"
 If ObjectExists(acTable, "TblJobExpPeta") Then DoCmd.DeleteObject acTable, "TblJobExpPeta"
 If ObjectExists(acTable, "TblLaborSumPeta") Then DoCmd.DeleteObject acTable, "TblLaborSumPeta"
 If ObjectExists(acTable, "TblLbr") Then DoCmd.DeleteObject acTable, "TblLbr"

modDeleteTables_Exit:
    Exit Function

modDeleteTables_Err:
    MsgBox Error$
    Resume modDeleteTables_Exit

End Function

Function modImport()

' Import records from Sema4
    
    DoCmd.TransferDatabase acImport, "FoxPro 2.6", "d:\Access\Temp", _
        acTable, "Exprate.dbf", "Exprate", False
    
modImport_Exit:
    Exit Function

modImport_Err:
    MsgBox Error$
    Resume modImport_Exit

End Function

Function modQ1()

 DoCmd.OpenQuery "QEMPLOYEE", acNormal, acEdit   'Make EMP NEW
  DoCmd.Rename "EMPLOYEE", acTable, "EMPLOYEENEW" 'RENAME EMPLOYEE
  'DoCmd.OpenQuery "QJobExpAppnd", acNormal, acEdit
  
  If ObjectExists(acTable, "JOBEXPAC") Then DoCmd.DeleteObject acTable, "JOBEXPAC"
  If ObjectExists(acTable, "TIMEARC") Then DoCmd.DeleteObject acTable, "TIMEARC"
  
modQ1_Exit:
    Exit Function

modQ1_Err:
    MsgBox Error$
    Resume modQ1_Exit

End Function

RE: Compacting multiple times while DB open (Access 97)

Sorry, don't know what "the function above" 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.

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 end of the procedure, instead of the beginning.Then you can just compact once at the end.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close