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

VBA; Access: compact and repair split db

I have a split db in Access (application mdb front and data in a separate mdb file).  When user exits the application, I want to "compact and repair" the mdb file housing the data tables.  How can I do that?
1 Solution
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
mx:  When you are 'linked' to tables in a backend mdb, you do not actually have the mdb 'opened' do you?   Are you saying that utility will compact and repair under that condition?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

With a reference to DAO, you can use:

DBEngine.CompactDatabase strExistingFilename, strNewFilename

Otherwise, you can add a reference to Jet Replication Objects and use:
How To Compact Microsoft Access Database Through ADO
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
gRay ... I didn't say anything ... but I suggest everyone read the info on Michael's site ... regarding the subject ... and the various pros and cons of the methods.

ShelnuttAuthor Commented:
After reading several posts, I was able to figure out how to solve my problem.  Here is the code I used:
Dim fs As FileSystemObject
Set fs = CreateObject("Scripting.FileSystemObject")
Dim SourceFile As String
Dim DestinationFile As String
Dim NewFile As String
Hourglass True
SourceFile = "c:\program files\superaudits\superaudits_bes.mdb"
DestinationFile = "c:\superaudits_bes_mdb"
NewFile = "c:\superaudits_besnew.mdb"
fs.CopyFile SourceFile, DestinationFile, True
DBEngine.CompactDatabase DestinationFile, NewFile
fs.CopyFile NewFile, SourceFile, True
fs.DeleteFile DestinationFile
fs.DeleteFile NewFile

None of the posts really resolved the issue.  The closest answer to what I needed was from PaulHews.
Thank you all very much for your time!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Thank you for posting your solution ... we appreciate that.

PAQed with points refunded (125)

EE Admin
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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