Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Oct 3, 2000

Compact an Access Database with VBS Script - Page 2

By Danny Lesandrini

VBS Script to Compact Access Database

<-- Back To Article
    ' *****************  BEGIN CODE HERE  ' *****************
    Dim objScript
    Dim objAccess
    Dim strPathToMDB
    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:\EMSEnt.mdb"
    ' ////////////////////////////////////////////////////////////////

    ' Set a name and path for a temporary mdb file
     strTempDB = "C:\Comp0001.mdb"

    ' Create Access 97 Application Object
    Set objAccess = CreateObject("Access.Application.8")

    ' For Access 2000, use Application.9
    'Set objAccess = CreateObject("Access.Application.9")

    ' 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
        ' 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
    ' ******************  END CODE HERE  ' ******************

Back To Article

See All Articles by Columnist Danny Lesandrini

MS Access Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM