Compact MDB from Excal VBA

I have an Excel VBA routine that updates an Access MDB. How do I perform a compact/repair after the update (from Excel using VBA) so I don't leave the file all bloaty?
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
 There are quite a few ways to do that.  Here's one routine to compact using JRO (Jet Replication Objects), which is part of ADO:

Public Sub CompactDB()
  'Microsoft Jet and Replication objects
  Dim objJE As New JRO.JetEngine
  Dim strSource As String
  Dim strTarget As String

  strSource = " "
  strTarget = " "
  objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTarget & ";Jet OLEDB:Engine Type=4;"

End Sub

Engine types are: Access 97 = 4, Access 2000 and up = 5

  There is also a method via DAO (Data Access Objects) and also by starting up Access itself as an automation object and calling an undocumented SYSCMD() function.

easiest way is to set the option to compact on close. And simply open and close the database from Excel.

WicklundaAuthor Commented:
Exactly what I was looking for!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.