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

Compact Database

Compact Database

Compact Database

I've been trying to crack this for some time. In Access 97, it's possible to comnpact the current database from the Tools, Database Utilities and Compact menus. However when trying to do the same in code, you get a request for the database to be compacterd from and to as in :-

CommandBars!tools.Controls![Database Utilities].Controls![Compact Database].Execute

Can anyone say why this is so ? I'd assumed that this code does the equivalent of clicking the menu ... am I on the wroing track ?



RE: Compact Database

It would be theoretically impossible--since the code that is running is part of the .mdb being compacted, so there is an open form or module from which the code runs that has to still be there when the code completes.

The code (the call the the application menu item) can't 'jump ship' and be out on it's own in the ether while it's home--the mdb--is being compacted.

When the menu item is run, it is the Access application--not the .mdb which--is running menu item so the .mdb can be closed.

Are you familiar with the dbengine.compactdatabase xx,xx command?  

RE: Compact Database


Thanks for replying. I see your point. But given that it is possible to compact the database, when a form is open, by using the Tools Menu, is it the case that the menu system takes the "current" database, opened form and record pointer, closes the form and the .mdb, compacts it, reopens the database and the form at the last record ? I can understand that (if that's the case) but if the menu options can do this, why can't it be achieved in code without having to specify the db to be compacted and the name of the newly compacted db ?

...or have I missed the point again ?



RE: Compact Database

If a form is open when you choose Compact, it'll close the form automatically (and any other open objects), prompting for unsaved work.  When the db reopens, it just runs any autoexec or startup scripts as it normally would, but it doesn't remember the state you were in (ie, which forms, etc. were open and what record you were on) and recreate that--it just starts like you just opened it from scratch.

But the whole point of the menu method (as opposed to code) is the context of where the compaction code was called.    Basically, the code can't 'kill itself'. So if Cust.mdb code calls Access' compaction code, the Cust.mdb code can't complete if it has to kill itself.  Here's what basically happens:

Let's say you have Cust.mdb.  What Compact--The Access Application code *not* code in Cust.mdb--is actually doing is Closing Cust.mdb, creating a new blank db, naming it (usually db1.mdb, or db2.mdb, etc.) and then exporting all objects from Cust.mdb to db1.mdb, which automatically rebuilds table indexes and consolidates dead space (space taken up by deleted records--where the recs are gone but the space is still used).  Then if this happens with no errors, it (Access code--not Cust.mdb code) deletes Cust.mdb and Renames db1.mdb to Cust.mdb.  

The Delete part is mainly why Cust.mdb code can't do that--the code would be deleting itself, then who's going to rename the .mdb?  So in your code, you need to supply the New name, and then you manually have to close Cust.mdb, then you can delete it and rename whatever new name you gave it--BUT--this is because here we're talking about doing it from the *context* of code in Cust.mdb, where the Menu example the context is Access, the application, not cust.mdb.

I hope I explained that correctly, I know it's a bit confusing but I think the gist is looking at the context of where the code is actually running.

One option is that you can open the .mdb with a shortcut and give it a /Compact switch.

If you must compact from the user interface of the runnin mdb, a kludge would be to put this (Pseudocode) in a New db say, MyCompactor.mdb and have it run on StartUp of Mycompactor.mdb:

Wait 5 'give origdb time to close
dbengine.compactdatabase myOrigdb,Tempdb
kill myOrigdb
rename tempdb origdb
ShellExecute origdb

Then you can, in your db do:
ShellExecute Mycompactor.mdb

This would run on some 'compact' button.  ShellExecute is needed as an API declaration, but it's pretty straighforward.

RE: Compact Database

Uhh.. toss this little puppy on a command button:

Private Sub Command1_Click()
   CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
End Sub

The kicker here is the last line. Note the acCDoDefaultAction.

Most other built-in guys use acSomethingOrOther, with only ONE "C" - I don't know if this was a deliberate thing or an acCident.. but this method works for A2K and above.

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:
More Access stuff at

RE: Compact Database

Another star for you, WildHare.  I managed to accomplish the same result using SendKeys, but didn't like the menus popping up as the code executed.  Your solution is much better.  Thanks!

Ken S.

RE: Compact Database

I like your solution So I gave you a Star

RE: Compact Database

Hi WildHare

I know this thread is kind of old but I was excited to see your solution.  However, I can't get it to work.  I'm using Access 2000.  Any particular reference that I need to check for?  My code is as follows:

Private Sub cmdCompile_Click()
CommandBars ("MenuBar"), _
Controls("Tools"), _
Controls("Database utilities"), _
Controls("Compact and repair database..."), _
End Sub

Thanks WH!


RE: Compact Database

Replace the commas with dots

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

RE: Compact Database

Hi Brian

I use this function to compact my database when it is over a certain size.  I check it every time I exit and compact if necessary.


Public Function AutoCompactCurrentProject()
    Dim fs, f, S, filespec
    Dim strProjectPath As String, strProjectName As String
    strProjectPath = Application.CurrentProject.Path
    strProjectName = Application.CurrentProject.Name
    filespec = strProjectPath & "\" & strProjectName
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    S = CLng(f.Size / 1000000)  'convert size of app from bytes to Mb’s
    If S > 20 Then              'edit the 20 (Mb’s) to the max size you want to allow your app to grow.
        Application.SetOption ("Auto Compact"), 1  'compact app
        Application.SetOption ("Auto Compact"), 0   'no don’t compact app
    End If
End Function

Hope it's of use


RE: Compact Database


Thanks for the quick reply but I'm getting a run-time error with the code:
Private Sub cmdCompile_Click()
CommandBars ("MenuBar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
End Sub

The error is "run-time error 5 invalid procedure call or argument".  Any thoughts on what I'm doing wrong?  Thank you!!


RE: Compact Database

I have just posed a FAQ on this subject - there may be some help for you in there.  See FAQ705-4998 - Compact and Repair database at the start of each day.

Bob Stubbs

RE: Compact Database

I have a VB6 application that I use to compact any version of access using the function below.  I use input boxes for the user to input the version, and the password (in any).
The only part I can't get working is the Security part for databases using Workgroup security.  It does work for all the hundreds of other Access applications on our hospital server.
Based on the version entered I set the JetEngine type as shown in the first code snippet.

    If RS!DBType = "97" Then
        lnVersion = 4
    ElseIf RS!DBType = "2000" Or RS!DBType = "2002" Or RS!DBType = "2003" Then
        lnVersion = 5
    End If
....various other code to pass parameters to function...

Public Function Compact(sSource As String, sDestination As String, Optional sSecurity As String, Optional sUser As String, Optional sPassword As String, Optional lVersion As Long) As Boolean
    Dim sCompactPart1 As String
    Dim sCompactPart2 As String
    Dim oJet As JRO.JetEngine
    'build string for source database
    sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & sSource & ";User Id=" & sUser & ";;Jet OLEDB:Database Password='" & sPassword & "'"
    If sSecurity <> "" Then
        sCompactPart1 = sCompactPart1 & ";Jet.OLEDB:System database=" & sSecurity & ";"
        'sCompactPart1 = sCompactPart1 & ";Jet.OLEDB:System database=" & sSecurity
        'sCompactPart1 = sCompactPart1 & ";Jet OLEDB:System database=" & sSecurity & " "
    End If
    'build string for destination database
    sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & sDestination
    If sPassword <> "None" Then
        sCompactPart2 = sCompactPart2 & ";User Id=" & sUser & ";;Jet OLEDB:Database Password='" & sPassword & "'"
    End If
    If lVersion <> 0 Then
        sCompactPart2 = sCompactPart2 & ";Jet OLEDB:Engine Type=" & lVersion
    End If
    'compact and give original name to compacted database
    Set oJet = New JRO.JetEngine
    oJet.CompactDatabase sCompactPart1, sCompactPart2
    Set oJet = Nothing
    Compact = True
End Function

Hope this is helpful and I hope someone can return code to compact databases protected by workgroup security.

RE: Compact Database

shelby55, pay attention to your menu. I haven't english version, so I can just guess:
Controls("Compact a database..."). _
Another guess is to use Execute method, as in my version (2003) accDoDefaultAction doesn't exists.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

RE: Compact Database

I use Access 2000 and on the Tools menu, Options, General Tab check the box Compact on Close. Whenever you close the database it comapcts.

RE: Compact Database

Hi, have a solution that is quite flexible based on getoption and setoption of the auto compact on close that rss01 uses...
on a switchboard or admin form i put a checkbox 'chkCNR' in the form_open event I add the following...


Private Sub Form_Open(Cancel As Integer)
chkCNR = Application.GetOption("Auto Compact")
end sub
this ties the checkbox to the compact on close you see on the options dialog, then I add the following to the afterupdate event for the check box


Private Sub chkCNR_AfterUpdate()
Application.SetOption "Auto Compact", chkCNR
End Sub
to set compact on close option on or off...
finally I call the following from an autoexec macro or startup form to the auto compact off next time its opened...


Public sub CheckAutoCompact()
If Application.GetOption("Auto Compact") Then Application.SetOption "Auto Compact", False
End sub
you dont have to use a form, you could test a value on a table or test the database size but the principles the same...

RE: Compact Database

Does anyone know if any of these solutions work with Access 97? Or are they all Access 2000...?


RE: Compact Database

What if you want to compact a linked database? Don't the methods outlined above only compact the current mdb (i.e the front-end if you've split the database)?

This is the problem I'm having with my Access 2000 database?

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