locked
How to compact a DB from VBA in anotrher application RRS feed

  • Question

  • I would like to compact a database from VBA of another program (iFix).

    I have this routine that I used to run from Access:

    Public Sub CompactDB(DB As String, code As Integer)
        Dim DBE As DBEngine
        Set DBE = Application.DBEngine
        DBE.CompactDatabase DB, "DBComp.mdb"
        Kill DB
        FileCopy "DBComp.mdb", DB
        Kill "DBComp.mdb"
    End Sub

    But outside Access the statement "Set DBE = Application.DBEngine" does not work.


    What is the correct statement if using it in another program?

    • Moved by Alan_chen Wednesday, February 1, 2012 1:42 AM VBA (From:ADO.NET Entity Framework and LINQ to Entities)
    Monday, January 30, 2012 10:14 PM

Answers

  • Try setting a reference to the Access application like this;

    Public Sub CompactDB(DB As String, code As Integer)
    Dim oApp As Object
    
      Set oApp = CreateObject("Access.Application")
      With oApp
        .DBEngine.CompactDatabase DB, "DBComp.mdb"
        Kill DB
        FileCopy "DBComp.mdb", DB
        Kill "DBComp.mdb"
      End With
    
      Set oApp = Nothing
    End Sub
    

     


    Ed Ferrero
    www.edferrero.com
    • Proposed as answer by Shasur Thursday, February 2, 2012 9:05 AM
    • Marked as answer by danishani Tuesday, February 14, 2012 11:44 PM
    Thursday, February 2, 2012 7:25 AM

All replies