On error goTo 0

[Janis]

I'm not expert at error trapping but On error goto 0 turns off error trapping
which is what I want in this script since if there is an error it quits the
script before compacting the db. My question is why are there 3 On Error
Goto 0 statements? Why is 2nd On Error statement repeated ? Is it just to
make sure it is turned off in the If clause? The 3rd On Error statement
seems like a mistake but I'm not sure.
TIA,

Option Explicit
Public Sub CompactBackEnd()

Dim strMsg
'Dim objScript As Object
'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 original mdb is saved
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)
DoCmd.Quit
End If
On Error GoTo 0

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
' deletes previous .mdbz file
If Dir("Z:\SwimClub\acsc_be.mdb" & "z") <> "" Then Kill
"Z:\SwimClub\acsc_be.mdb" & "z"
FileCopy "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb" & "z"

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

' We are finished with TempDB. Kill it.
If Dir("Z:\SwimClub\temp_acsc_be.mdb") <> "" Then Kill
"Z:\SwimClub\temp_acsc_be.mdb"

Call MsgBox("compact successful")

End Sub

 

[Dirk Goldgar]

I'm not expert at error trapping but On error goto 0 turns off error
trapping
which is what I want in this script since if there is an error it quits
the
script before compacting the db. My question is why are there 3 On Error
Goto 0 statements? Why is 2nd On Error statement repeated ? Is it just to
make sure it is turned off in the If clause? The 3rd On Error statement
seems like a mistake but I'm not sure.
TIA,

Option Explicit
Public Sub CompactBackEnd()

Dim strMsg
'Dim objScript As Object
'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 original mdb is saved
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)
DoCmd.Quit
End If
On Error GoTo 0

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
' deletes previous .mdbz file
If Dir("Z:\SwimClub\acsc_be.mdb" & "z") <> "" Then Kill
"Z:\SwimClub\acsc_be.mdb" & "z"
FileCopy "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb" & "z"

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

' We are finished with TempDB. Kill it.
If Dir("Z:\SwimClub\temp_acsc_be.mdb") <> "" Then Kill
"Z:\SwimClub\temp_acsc_be.mdb"

Call MsgBox("compact successful")

End Sub

That code is not going to work as intended. First, "On Error GoTo 0" does
*not* turn off error-handling, it just tells Access to use its built-in
error handling, and not your own. It seems likely that what was intended
for the first one of those statements was "On Error Resume Next", which
turns on in-line error-handling (the closest you can really come to "turning
off" error-handling.

So it makes sense to have "On Error Resume Next" precede a statement that
checks whether there was an error, as in this modified code:

On Error Resume Next

'perform db compact of backend mdb into a temp mdb first
'if there is a problem the original mdb is saved
dbe.CompactDatabase "Z:\SwimClub\acsc_be.mdb", _
"Z:\SwimClub\temp_acsc_be.mdb", , , ";pwd=Kiyote#3"

If (Err.Number <> 0) Then
' ...
End If

And it makes sense to restore default error-handling later, as with these
lines:

On Error GoTo 0

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
' deletes previous .mdbz file

That is saying, "From this point on, if there's an error, go ahead and
display the built-in error dialog."

But this code that comes before that is still wrong:

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)
DoCmd.Quit
End If

It's wrong because executing "On Error GoTo 0" within that block will clear
the current error, so the description of the error, pulled from
Err.Description, will not be available. That statement should not be
executed until after the error message is constructed:

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
On Error GoTo 0
Call MsgBox(strMsg)
DoCmd.Quit
End If

 

[Janis]

Below is how I think you said to change it. The On Error goTo 0 is needed in
the if/then block, after the error message is saved. I understand that. But
it is also repeated after the if/then block? The on error goto 0 in the
If/Then block doesn't persist through the rest of the code. I guess that
makes sense. It is only if there is an error. I think I answered my own
question.
thanks very much. since this involves accounting records I am being careful.


'turn off in-line error handling so we can capture an error message in a
variable
On Error Resume Next
'perform db compact of backend mdb into a temp mdb first
'if there is a problem the original mdb is saved
dbe.CompactDatabase "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\temp_acsc_be.mdb"
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
'after an error message is captured turn on regular error handling
On Error GoTo 0
Call MsgBox(strMsg)
DoCmd.Quit
End If