Compact Database 8

[brianjohnson]

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 ?

Regards

Brian

 

[jsteph]

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?
--jsteph

 

[brianjohnson]

jsteph

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 ?

Regards

Brian

 

[jsteph]

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
quit

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

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

 

[WildHare]

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

Private Sub Command1_Click()
CommandBars("Menu Bar&quot. _
Controls("Tools&quot. _
Controls("Database utilities&quot. _
Controls("Compact and repair database...&quot. _
accDoDefaultAction
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:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[Eupher]

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.

 

[mikeyb540]

I like your solution So I gave you a Star

 

[shelby55]

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..."), _
accDoDefaultAction
End Sub


Thanks WH!

Shelby

 

[PHV]

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

 

[fabby1]

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
    Else
        Application.SetOption ("Auto Compact"), 0   'no don’t compact app
    End If
End Function

Hope it's of use

Phil

 

[shelby55]

Hi WH

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..."). _
accDoDefaultAction
End Sub

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

Shelby

 

[BobStubbs]

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

 

[susanhawk]

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 OLEDBatabase Password='" & sPassword & "'"

'THIS IS WHERE I WOULD LOVE SOME HELP!
If sSecurity <> "" Then
'TRIED ALL THREE BELOW AND NONE WORK
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 OLEDBatabase 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.

 

[PHV]

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

 

[rss01]

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.

 

[jksmi]

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...

 

[tcstom]

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

Tom

 

[TheQuestioner]

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?

 

[TheQuestioner]

Managed to solve my problem in another thread (thread705-853257).