VBS to Compact Access database

[kriemer]

I am trying to run following found code to compact access databases with several problems:

1: I would like the access database to open on the screen to allow me to see something is going on

2: The code as found has different lines depending on Access database type (97, 2000, etc). The code fails here but will run on generic "Set objAccess = CreateObject("Access.Application")". Will this create problems?

Thanks in advance for any help

k

Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

strPathToMDB = "C:\test.mdb"

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")

objAccess.DbEngine.CompactDatabase strPathToMDB ,strTempDB

If Err.Number > 0 Then
strMsg = "The following error was encountered while compacting database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
Set objScript= CreateObject("Scripting.FileSystemObject")

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

objScript.CopyFile strTempDB, strPathToMDB, True

objScript.DeleteFile strTempDB
End If

Set objAccess = Nothing
Set objScript = Nothing

 

[dm4ever]

I posted this on the other site where you asked this question, but just in case someone looks here:

Use ADO to compress it...search for JRO.JetEngine

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[kriemer]

thanks for both