Compact Current Database with VBA Code Access 2016

[RMittelman]

My Front-end database has several local tables used for collecting data to print reports and do mail-merges. All other tables are linked to my back-end database. As these reports are created, the database gets larger. So if the database gets too large, I want the user to be able to fix this.

The Main method gets the current size of the database on opening. In my Switchboard Activate event, it gets the now current size of the database. If this is larger than the original size (by a pre-determined percentage), it prompts the user and asks if they want to fix this condition. If they click YES, it runs a macro which clears the various work tables.

I want it to also compact/repair the database as if they clicked File>Info>Compact & Repair or Database Tools > Compact and Repair Database button on the ribbon.


Can't seem to figure out this last part. However I write code to compact / repair the database, it tells me I can't do this in code or in a macro.

There are a couple of ideas, but not sure which ones will work.

• I could write a VBScript to rename the database, then automate Access to do this, call that script in a shell command, and immediately quit Access, but the script would have to know when the operation was complete, then run the database again.
• I could try to use the CommandBars objects, but not sure if they will work for this. The few examples I tried would not work.
• I could simply put up a MessageBox and tell the user to click the Compact & Repair button, but that seems amateurish.

Any ideas how I could accomplish this?
Thanks...

UPDATE: I can get this to work by executing

SendKeys "%fic"

But I hesitate to use SendKeys as it causes strange behavior sometimes. In this case it may be ok, because all it does is compact / repair the database then run it again, right?

[ranman256]

I use this to compact db, but not the one I'm in:

Application.CompactRepair vSrcFile, vTargFile

If you have an app with a form that lets the user choose the db to compact,
user picks the file ,
file path is in the text box
click repair button,
code renames the file to [filename]_preRepair
compacts back to the orig name.

Private Sub btnRepair_Click()
Dim vSrc, vTarg, vExt
On Error Resume Next


vTarg = txtFile


vExt = Mid(vTarg, InStrRev(vTarg, "."))
vSrc = vTarg & "_preRepair" & vExt
  'rename the src file
Name vTarg As vSrc


   'compact to the target file
'HrGlass
Application.CompactRepair vSrc, vTarg
'HrGlass False
MsgBox "Done", , "Repair"
End Sub

[RMittelman]

Thanks ranman256. This would work great for compacting my back-end database. Unfortunately, that is not the issue here. Trying to compact the current database. Every code-based solution seems to not be allowed, except for the SendKeys "%fic", which works fine. Thanks again...

[Micron]

Why not switch your strategy to compact on close if the db gets to a certain size? Something like

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 > 8 Then  'edit # to max size in MB's allowed before compacting
        Application.SetOption ("Auto Compact"), 1  'compact app
    Else
        Application.SetOption ("Auto Compact"), 0   'no don’t compact app
    End If

That example uses 8Mb as the target and will take the decision out of their hands. I question your decision to put those tables in the front end though, unless you have network speed issues and you've found that putting them in the fe helps with that. You might find that the need for compacting would drop. I'm also assuming that your fe isn't shared.

[RMittelman]

Thanks for the reply Micron. Appreciate it. Typically, FE database growth is NOT an issue. Normally, they only grow when you are enhancing features, adding new forms, etc. In my case, the FE tables are a big part of the cause.

While I generally agree with you about FE tables, this is an exception IMO. Some of the reports, exports and mail-merges that the customer creates kind of require having data in tables. It wouldn't make sense to put those in the BE database, because then multiple users would conflict with each other when using the application. Each user has their own copy of the FE database, thus their own "work" tables. Therefore, they can each use the application without conflicting with other users, which would happen if the work tables were on the BE (unless of course BE tables were named for each user, which could be a nightmare to maintain). I have the work tables emptied when the user exits the application, so that helps mitigate the problem. Also, setting the auto-compact would not fix the issue, because I need to first clear those tables THEN compact.

Thanks again...

[isladogs]

MS removed the ability to compact the current database using code in version 2010.
Although this was initially frustrating, it was done for good reasons - to prevent corruption of data in use when such code was run.
Therefore although your SendKeys solution may run in whatever version you are using, I wouldn't recommend it!
In addition, from personal experience of doing exactly the same thing, the code won't work in another version of Access

So I would go with Micron's suggestions.
1. Compact on close when the FE exceeds a certain size. However be aware being even this can cause corruption so always make a backup first
2. Reduce the frequency of doing such backups by moving your temp tables to a 'side-end' database ...
OR
3. make the whole process superfluous by setting up a script whereby users receive a new 'clean' copy of the FE from the server each time they open the database

[Micron]

Normally, they only grow when you are enhancing features, adding new forms

or apparently if you repeatedly over write a table with a make table query, or continually empty and append to it.

unless of course BE tables were named for each user

Or your report "Micron Report1" would be identified as belonging to me, and wouldn't be shown to anyone else hence one table for all reports. However, I don't see the point of any of that if you want to flush the table before each session. Unless I'm missing something, I don't see why you can't test the size and flush the table if the size limit is hit, and set the db property - all when they exit.

Without seeing your db, I'm not convinced that the table(s) can't be in the be but perhaps you know best about that.