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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
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
    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
    '    
    ' ******************  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