problem with an error checking routine

[JRough]

How can I test this script to make sure it works as expected? It runs
successfully through the script and I get a message that the compact
was successful. However, I don't see it go through the process to
copy the temporary backend file and then rename it and delete the
original db? I have to guarentee myself it is working and you would
think I could see vbscript doing the File manipulations. I think
there is some logic missing and it is not working.
tnx,

Option Explicit
Dim objscript
Dim dbe
set dbe = createobject("dao.dbengine.36")

'path is to System.mdb because it is a split database
dbe.SystemDB="Z:\SwimClub\System.mdw"
dbe.DefaultUser = "Brian"
dbe.DefaultPassword ="Kiyote#3"

On Error GoTo 0
'perform db compact of backend mdb into a temp mdb first
'if there is a problem the origial mdb is preserved
dbe.CompactDatabase"Z:\SwimClub\acsc_be.mdb","Z:\SwimClub
\temp_acsc_be.mdb",,,";pwd=Kiyote#3"


If (Err.Number <> 0) Then
On Error GoTo 0
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while
compactingdatabase:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Call MsgBox(strMsg)
Wscript.Quit
End If
On Error GoTo 0

' 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 "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub
\acsc_be.mdb" & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile "Z:\SwimClub\temp_acsc_be.mdb", "Z:\SwimClub
\acsc_be.mdb", True

' We are finished with TempDB. Kill it.
objScript.DeleteFile "Z:\SwimClub\temp_acsc_be.mdb"
Call MsgBox("compact successful")

 

[Tom van Stiphout]

On Tue, 9 Jun 2009 12:28:58 -0700 (PDT), JRough <[email protected]>
wrote:

I would probably implement this code in VBA so I could use a decent
debugger; then port back to VBScript.

-Tom.
Microsoft Access MVP

 

[JRough]

I would probably implement this code in VBA so I could use a decent
debugger; then port back to VBScript.

-Tom.
Microsoft Access MVP

Does that mean I can just drop this code into Access? Do I set it up
in a separate module?

 

[Tom van Stiphout]

You can put this code in a public function in a standard module, and
execute it from the Immediate window.
Or in the Click event of a button on a form. or pretty much anywhere.

There are some differences between vbscript and vba; one that is
important for your code fragment is that you should declare each
variable with the correct type:
dim objScript as Scripting.FileSystemObject
etc.

-Tom.
Microsoft Access MVP

 

[Janis]

Tom
I ported it over to VBA in Access as suggested.
I discovered one issue t. The program before any dim changes WAS creating
the compacted temp copy? It must have guessed the variable type. It seems
like it was working. The compacted temp file was a hidden file so I didn't
discover it until I searched for it and discovered it was hidden. Then I got
rid of hidden files in the control panel. I don't know how to dim the dbe
object?
I did dim the objScript as Scripting.FileSystemObject as you mentioned and
now I get a runtime error. It says "user defined type not defined" when I
try to compile. In references I have a reference to
ActiveX Data Objects 2.1 Library. NOw what gives?

Here it is again.

---
Option Compare Database
Option Explicit

Public Sub compact()

Dim strMsg
Dim objScript As Scripting.FileSystemObject
Dim dbe As DBEngine
Set dbe = CreateObject("dao.dbengine.36")

'path is to System.mdb because it is a split database
dbe.SystemDB = "Z:\SwimClub\System.mdw"
dbe.DefaultUser = "Brian"
dbe.DefaultPassword = "Kiyote#3"

On Error GoTo 0
'perform db compact of backend mdb into a temp mdb first
'if there is a problem the origial mdb is preserved
dbe.CompactDatabase "Z:\SwimClub\acsc_be.mdb",
"Z:\SwimClub\temp_acsc_be.mdb", , , ";pwd=Kiyote#3"


If (Err.Number <> 0) Then
On Error GoTo 0
' 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
Call MsgBox(strMsg)
WScript.Quit
End If
On Error GoTo 0

' 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 "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb" &
"z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile "Z:\SwimClub\temp_acsc_be.mdb",
"Z:\SwimClub\acsc_be.mdb", True

' We are finished with TempDB. Kill it.
'objscript.DeleteFile "Z:\SwimClub\temp_acsc_be.mdb"
Call MsgBox("compact successful")

End Sub