VBA; Access: compact and repair split db

Posted on 2007-07-31
Last Modified: 2012-05-05
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?
Question by:Shelnutt
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    LVL 44

    Expert Comment

    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?
    LVL 38

    Expert Comment

    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
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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.


    Author Comment

    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!
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Thank you for posting your solution ... we appreciate that.

    LVL 1

    Accepted Solution

    PAQed with points refunded (125)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Title # Comments Views Activity
    MsgBox 2 28
    Printing Problem 13 21
    Subform Link field 13 20
    Excel To Access Database Transfer 14 23
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now