Active X component can't create object Access.9

[Janis]

I got the script to run but now it can't create the object Access.9. I get a
run time error. I am running this db on Access 2003. The user I think is
running it on Access 2000. Is there another object for Access for Access
2003 so I can test it? I tried changing it to Access.10 and it didn't work.

I do have a question. Is it much safer to run the script like this with a
temp backup than just turn on the option in the db to backup every time you
close? For example, if there is possible corruption in the file you should
probably run the script right?
Thanks again,

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 = "Z:\SwimClub\acsc_be.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.10")

' 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 ' ******************

 

[Douglas J. Steele]

The comment in your code says to use Application.9, yet your code is
actually using Application.10

You'll only be able to use Application.10 if the user has Access 2002
installed. (Similarly, you'll only be able to use Application.9 if the user
has Access 2000 installed, Application.8 if the user has Access 97
installed, Application.11 if the user has Access 2003 installed and
Application.12 if the user has Access 2007 installed)

 

[Janis]

Thanks, this script runs flawlessly. It doesn't however say it is done when
its done but it does give errors when it doesn't run. I guess you don't need
a dialog to say it worked.

 

[Douglas J. Steele]

You're already checking the value of Err.Number after the Compact statement.
You can pop up a messsage saying it was successful if it's equal to 0.

What I usually do is rename the existing database, and compact the renamed
file to the "proper" file.