I want to try to compact a database after I have deleted several tables and while the mdb is still open.

Some background to why I think I need to do this - I will be working with very large datasets and after the user selects the process to perform I am going to be exporting the result to excel and deleting the table(s) of calculations and attempt to compact the database so that the user can select another process without worrying about running out of space in the database.

Any assistance would help.

Dwight
Resource Analyst

Hello Dwight

I don't know if I am exactly right on this but my partner and I have a program running on a server and several client machines using an access database and for us to do a backup and compact, we had to close the connection, do the backup and then reopen the connection.
Maybe this doesn't help at all but this seemed to be our only work around. if there is another way I also would be interested in trying it.

Rick

To compact an Access database you need the Jet Replication Objects. Plenty of examples on Google on how to use. I'm not sure if this works on open databases, it's a long time since I've used them :)

--

Joe

I'm using Access 2002 and tried to program a compact & repair sequence from within the VBA code of the Access database I wanted to compacted. I had planned to use the Application.CompactRepair method, compact to a temporary MDB file, then switch the temporary MDB for the current but closed database if that was successful.

Unfortunately, the database would not shut down because it was the current database and contained the VBA code! (at least, I think that was the problem).

I now include an instruction to the user to use the Compact/Repair command from the Tools...Database Utilities pulldown. Wish I knew how that commmand works successfully! If you want to see my failing code, I can include it in another append.

I also couldn't find a way to compact an Access database while the database was open. However, I do have code that will compact an Access database from a VB application that is using the Access database for data. I don't know if it will help anyone or not. I didn't write this code. It was in an application that I inherited and now have to support.

Private Sub CompressB_Click()
  If MsgBox("Run compress now?", vbYesNo) = vbYes Then
    Screen.MousePointer = vbHourglass
    On Error Resume Next
    Kill gsDBPath + "stoppay2.mdb"
    On Error GoTo 0
    DBEngine.CompactDatabase gsDBPath + "stoppay.mdb", gsDBPath + "stoppay2.mdb"
    Kill gsDBPath + "stoppay.mdb"
    Name gsDBPath + "stoppay2.mdb" As gsDBPath + "stoppay.mdb"
    Screen.MousePointer = vbDefault
    MsgBox "Compress is complete."
  End If
End Sub

This link may help -
http://www.tek-tips.com/gfaqs.cfm/le...d/705/fid/4998