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?
WicklundaAsked:
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.

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

0
 
WicklundaAuthor Commented:
Exactly what I was looking for!
0
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.