asked on

Determine whether an ADO Execute method was successful or raised an error in VBScript

Background: Win XP, Access 2003 - due to legacy controls and client not ready to upgrade (go figure)

I have a VB script process which runs every 1/2 hour (from Windows Task Scheduler) to download data from an FTP site and write data to the Access database. Lately, the client has experienced issues with some records not getting written to the Access database properly. Yet I'm getting no indication that the ADO operation failed.

The code that writes the data to the Access database looks something like:

strSQL = INSERT INTO myTable (Field1, Field2, Field3) Values (1, 2, 3)
objConn.Execute strsql

where objConn has previously been defined.

I don't currently have any error handling in this process. Is there a way for me to determine whether the Execute method failed (raised an error), without creating a recordset to check for the value of the key field in this insert process?

ste5an

Error handling in VBScript is limited, cause you have only On Error Resume Next. The general pattern requires clean code and separation of concerns. Thus each block who needs separate error handling must be extracted into its own method, because of the Resume Next and scope of methods. The template method looks like:

Sub Method()

  On Error Resume Next
 
  Err.Clear
  CallSingleMethod
  If Err.Number <> 0 Then
    ' Oops.
  End If

End Sub

Open in new window

You need to clear the error object first, then call the method you want to test for errors. After this you evaluate the error object.

For using this with ADODB connections, you must get the connection to return an error, thus use the dbFailOnError option. E.g.

On Error Resume Next

' Constants and Enumerations.
Dim NO_ERROR
Dim dbFailOnError '  DAO.RecordsetOptionEnum

NO_ERROR = 0
dbFailOnError = 128

' Variables.
Dim Connection ' ADODB.Connection
Dim Sql ' SQL DML statement or batch to execute.

' Initialize Connection object.

Err.Clear
Connection.Execute Sql, dbFailOnError
If Err.Number <> NO_ERROR Then
  ' Oops.
End If

Open in new window

Thus as encapsulated method:

Function ExecuteSql(AConnection, ASql)

  On Error Resume Next

  ' Constants and Enumerations.
  Dim NO_ERROR
  Dim dbFailOnError '  DAO.RecordsetOptionEnum

  NO_ERROR = 0
  dbFailOnError = 128

  ExecuteSql = False
  Err.Clear
  AConnection.Execute ASql, dbFailOnError
  If Err.Number = NO_ERROR Then
    ExecuteSql = True
  Else
    LogError "ExecuteSql", Err.Number, Err.Describtion, ASql ' Your logging method.
  End If

End Function

Open in new window

Depending on the kind of processing, I used sometimes Access itself as processor. Implement the logic in a separate Access database and start it (scheduled) with the /x PublicSubToInvoke parameter. Then you can do it in VBA.

Dale Fye

ASKER

@ste5an,

Working on it, but I'm getting a runtime error in the VBScript file (attached) when attempting to write to the Access file (also attached). You will obviously need to change the path to the Access file.

The error I'm getting is 424 - Object Required

2019-08-23_VBScript_Error_Handling.txt
VBScript_Test.accdb

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

Thanks!

Getting lines 37 and 40 reversed had a lot to do with not being able to diagnose the problem. Which explains why I was getting a "Connection opened successfully" message but the inserts all failed with no object.

On the clients computer (XP and A2003) that connection string works for the code that is already in place, but I need to add some error handling to that code.

The script worked great on my computer (Win7, O2007) using the Ace provider, once I made those changes.