Compact the database via VBS (1 Viewer)

[Number11]

Does any one have a working method to compact access 365 database from VBS so i can automate it to compact after importing data pls?

 

[theDBguy]

You could try using the /compact command line switch when you execute the MSACCESS.EXE app in your VBS. I don't have a sample handy.

 

[bastanu]

FBA MS Access back-end compacter - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,

 

[NauticalGent]

I cant remember where I found this, but this works great...

' ***************** BEGIN CODE HERE ' *****************

 

    Dim objScript

    Dim objAccess

    Dim strPathToMDB

    Dim strTempDB

    Dim strMsg

 

' ///////////// NOTE: User must edit variables in this section /////

'

' The following line of code is the only variable that need be edited

' You must provide a path to the Access MDB which will be compacted

'

    strPathToMDB = "C:\Your Full Path\YourDB.accdb"

'

' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file

 

    strTempDB = "C:\YourFullPath\YourDB_Comp.accdb"

 

    Set objAccess = CreateObject("Access.Application")

 

' Perform the DB Compact into the temp mdb file

' (If there is a problem, then the original mdb is preserved)

 

    objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB

 

    If Err.Number > 0 Then ' There was an error. Inform the user and halt execution

        strMsg = "The following error was encountered while compacting database:"

        strMsg = strMsg & vbCrLf & vbCrLf & Err.Description

    Else ' Create File System Object to handle file manipulations

        Set objScript = CreateObject("Scripting.FileSystemObject")

    ' Back up the original file as Filename.mdbz. In case of undetermined

    ' error, it can be recovered by simply removing the terminating "z".

        objScript.CopyFile strPathToMDB, strPathToMDB & "z", True

    ' Copy the compacted mdb by into the original file name

        objScript.CopyFile strTempDB, strPathToMDB, True

    ' We are finished with TempDB. Kill it.

        objScript.DeleteFile strTempDB

    End If

 

' Always remember to clean up after yourself

Set objAccess = Nothing

Set objScript = Nothing

'

MSGbox "Done!"

' ****************** END CODE HERE ' ******************

 

[NauticalGent]

Found it!

Compacting a protected MS Access database using VB Script - VBScript - Tek-Tips

Hi, I use a VB script to routinely compact the tables of my MS Access database. It was working well when I had no protection on that database. My problem starte

www.tek-tips.com

 

[bastanu]

While the script does include backing up the original file I think it is missing a (very) important step; copying the original file first to the temp location (which should always be on a local drive) and actually compacting that not the original. Errors during compacting a large back-end located on a network drive can render it corrupted beyond repair.

Cheers,

 

[NauticalGent]

Good point. I might make that change if I ever decide to use this...