Compress Access DB via Excel

[kmiles]

I have not had any problems accessing my Access data from Excel using ADO, however I am not sure how to run Access Utility commands such as Compress and Repair database from Excel with vba.

Any help is appreciated.

 

[Ian Mac]

Can I ask why you need to do it from Excel?

a) it's not hard to do by going to Access
b) only an administrator should be doing it

Cheers,

 

[kmiles]

I need to do it from Excel because I'm already running an automated process from a workbook using vba to pull the data from Access and analyze it.

I'm also writing to the database from excel vba. This tends to swell the size of the DB significantly, so I want to run the compress utility once this is done.

This entire process is automated with a workbook_open event scheduled once every 24 hours so there's no user intervention.

 

[Ian Mac]

OK,

As your using ADO, effectively a client to the server database, I wouldn't have thought you would get any noticable bloat except the new records you added.
Unless you adding data to it and deleting it again, I can't se why it grows.

That said, the following (not completely tested) code should get you started:

Sub compactAccess()

Dim fs              As Object
Dim strOldPath      As String
Dim strNewPath      As String
Dim blnSuccess      As Boolean

Set fs = CreateObject("Scripting.FileSystemObject")

'Change path and file to suit
strOldPath = "R:\YourFolderPath\YourDatabaseName.mdb"
strNewPath = "R:\YourFolderPath\YourDatabaseName_compacted.mdb"

blnSuccess = Access.Application.CompactRepair(strOldPath, _
            strNewPath, True)

fs.CopyFile strNewPath, strOldPath, True
fs.DeleteFile strNewPath

If blnSuccess Then
    MsgBox "Compacted Successfully"
    Else
    MsgBox "The operation did not work"
End If

End Sub

Edit: Make sure you add Access to your References in VBA

 

[kmiles]

Thanks Ian, that's exactly what I was looking for.

I was able to get the following to work by pasting into a batch file and running with the shell function, but I prefer to learn the "right" way.

"Programs Source\MSAccess.EXE" "Data Source\DB.MDB" /compact "Dest\DB_c.MDB"

copy "Dest\DB_c.MDB" "Dest\DB.MDB"

del "Dest\DB_c.MDB"