Force Database Compat using VBA 1

[davman2002]

Does anyone know a way or can you point me to an article dicussing how to force Access to compact the database using VBA in a module?

 

[rmcmorris]

Very simple. Add this to your VBA:

RunCommand acCmdCompactDatabase

You will find a zillion helpful features using the RunCommand function.

Regan

 

[PantoKing]

I found the following knocking about here a month or so ago. I can't remeber who had posted it but it's quite good.

Auto Compact your Database when FileSize = x


What this function does is look at the file size of the app it’s being called from. If the file size is smaller than the size specified, it doesn’t compact on close, if it’s larger it will compact on close. I don’t know if any of you have noticed this before, but when I deliver an application, say, when fully compacted the size might be 10 Mb, after using it a few times it might grow to 14 Mb, but there after will only grow possibly 100 Kb after each session. I don’t know the reason for this, the size just seems to bottom out. Anyway when you’ve got this approx size that the file bottom’s out at, in this example 14 Mb, put in a reasonable file size (one that won’t put a drain on system resources) that you want to allow the file size to grow to.

Put the following in a global module:

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

Call the Function from the procedure that closes down your app, before e.g. Docmd.Quit:

AutoCompactCurrentProject


Once it’s installed (assuming the project is completed), you can forget about it. Also there is no need for error handling as all the function does is turn Access’s own in-built Compact on Close feature on or off. Should really give your apps the appearance of being more efficient only compacting, often, after weeks of constant use.

You never have to remember or remind a user to compact your apps again.

Make things as simple as possible — but no simpler.

 

[rmcmorris]

FYI, "Auto Compact" is not a valid option for Access 97 or earlier. I assume it is a new option for Access 2000?

Regan

 

[scking]

"You can't compact the open database while running a macro or Visual Basic code", is a quote from an error kicked out by VBA. You can't really the RunCommand a solution for an open database.

RunCommand acCmdCompactDatabase

Now if you wanted to open a database in code and use the RunCommand it would probably work.

Dim wsp As Workspace
Dim pdbServer As Database
Set wsp = DBEngine.Workspaces(0)
Set pdbServer = wsp.OpenDatabase(pstrSAppName)
pdbServer.RunCommand acCmdCompactDatabase


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[rmcmorris]

I do stand corrected. I apologize for the error. In reviewing my app, I do see now that I use the following code:

Dim ThisDB As Database
Set ThisDB = OpenDatabase("MyDB.mdb&quot
DBEngine.CompactDatabase "MyDB.mdb", "MyDB.mdb"

I run it every time an "Archive Data" button is pressed. The downside is that sometimes the database will automatically re-open after a compact, but sometimes it does not.

Regan

 

[rmcmorris]

Actually, that is not quite right either. I guess I removed that procedure because it does not work if your database is open. (It has been a very long time since I wrote this code...) Anyway, after trying all of the above, I finally resorted to calling up the database window and using a send keys command to compact the database:

DoCmd.SelectObject acTable, , True
MsgBox "Please Wait while database is compacted. If Main Menu does not display" _
+ " after compact, please close Access and re-open", vbInformation, "COMPACT DATA"
'Compact
SendKeys "%{T}"
SendKeys "D"
SendKeys "C"

Regan

 

[scking]

I like rmcmorris's sendkeys solution because the database is compacted using the intrinsic functions for the logged on database. Now I believe that would work but not you've essentially got a shortcut to the menu. If you combined a little functionality something like:

If UserIsMemberOfAdmins(CurrentUser) _
And SignedOnUsers = 1 _
And AppSize < 12 Then
' run the above
End If

Where you need to check to determine if the current user has administrative permissions needed to perform the compaction, and there is only a single user (who is signed on as an administrative user), and size of the file < 12 MBytes. You could also peel off the different layers if they didn't apply. These three functions are not intrinsic and would need to be developed. I have the first function, and I have a structure that could probably return the second function value, and the AppSize function could be modified from SteveCarey's input above.

Therefore if you've got the permission to perform the compaction and you have the database opened with no other members logged on and the size was past where it should be nominally then compact the database.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[BillPower]

Hi SteveCarey,

Thanks for the compliment, you can see the original posting for Auto Compact your Database when FileSize = x at thread181-421003.

 

[oharab]

I have a FAQ (or should that be an FAQ?) on compacting database from code: faq705-1955

Just another idea!

B ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[psemianonymous]

Of course the compact will only work if you are logged on exclusively (i.e. no other users).

Also of interest to you: the Rumba office API:

http://supportweb.netmanage.com/ts_rumba/downloads/develop/ehllapiWhallapi.asp

This allows you to do Visual Basic automation-stuff like logging in and running reports (like you asked for earlier). Check it out.


--
Peter
at work in Huntsville