macro compact and repair

[tuxo99]

Hello,

I am doing a big macro with a lot of querys in an Access Database.

I would like to know if there is a chance to write in the macro to compact and repair the database automatically.

Thank you

 

[alansidman]

Look at this:

MS Access: how to compact current database in VBA - Stack Overflow

 

[Micron]

Compacting a db every time it closes is not a good idea, and should be needed only rarely if the db is split in a front end/back end configuration with all tables in the BE. This includes temporary tables or 'local' tables (say, instead of using huge linked ODBC tables). The only time a be should need compacting is if you did a bunch of redesign work in it. As for the fe, the need is likely greater since that's where form/report/query/macro/module creating and editing takes place, but still should not be done every time it closes. I think the best thing to do is decide on a db file size you can live with and put some code in a standard module that gets called when the fe closes. This code compacts the current db if the file size exceeds 6 Mb. I believe you could adapt it to compact a remote db, but I've never used it for that.

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 > 6 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
End Function

If you're a macro guy, you can call this function from a macro, but I do it by triggering the shutdown code when a hidden (timer) form closes.
That's my take on it, FWIW.