• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 883
  • Last Modified:

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?
1 Solution
easiest way is to set the option to compact on close. And simply open and close the database from Excel.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
 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.

WicklundaAuthor Commented:
Exactly what I was looking for!

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now