revise compact and repair  vba from another accdb for vba 2010

Posted on 2012-08-15
Last Modified: 2012-08-15
vba 2003
access 2003

I ave code here that will compact and repair a database via vba code from another database.

Dim gsDBPath As String

    gsDBPath = "C:\Program Files\Crs\CartDatabases\"

    Kill gsDBPath + "SapAlternates2.mdb"
    DBEngine.CompactDatabase gsDBPath + "SapAlternates.mdb", gsDBPath + "SapAlternates2.mdb"
    Kill gsDBPath + "SapAlternates.mdb"
    Name gsDBPath + "SapAlternates2.mdb" As gsDBPath + "SapAlternates.mdb"

Does this code need to be revised to work in vba 2010... I have not used it yet ?

or better yet.

is the better code for doing this in office 2010 ?

Question by:fordraiders
    LVL 65

    Accepted Solution

    Yes, but just because I like making comments like this ... did you try it first?

    I pieced together the below function in my Access 2010, and it worked fine.  

    Public Function fn_compact_repair()
    Dim oApp As Access.Application
    Set oApp = Access.Application
    oApp.DBEngine.CompactDatabase "C:\portfolio\fooey\db1.mdb", "C:\portfolio\fooey\db2.mdb"
    Kill "C:\portfolio\fooey\db1.mdb"
    Name "C:\portfolio\fooey\db2.mdb" As "C:\portfolio\fooey\db1.mdb"
    End Function

    Open in new window

    LVL 3

    Author Closing Comment

    Thanks worked fine.
    LVL 65

    Expert Comment

    by:Jim Horn
    Thanks for the grade.  Good luck with your project.  -Jim

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    761 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

    11 Experts available now in Live!

    Get 1:1 Help Now