SQL Object

ResetConnection

 
Syntax
 
SQL.ResetConnection
 
Description
Resets the current database connection by performing the following steps in sequence:
  1. If a connection is active and open, any pending SQL Server transactions are rolled back by executing IF @@TRANCOUNT > 0 ROLLBACK TRAN. This ensures that a dirty transaction state left behind by a previous error does not affect subsequent operations.
  2. The internal ADO database connection object is destroyed (set to Nothing), releasing all associated resources on both the client and the SQL Server.
  3. A new connection is established using the current connection string.
 
The function returns True when the new connection was established successfully, or False if the reconnection failed (e.g. due to a network error or invalid connection string).
This method is primarily intended for use in error recovery scenarios in long-running scripts, such as EDI processing loops or batch import macros, where a database error may leave the connection in an unusable state. Rather than requiring the entire script to be restarted, a call to SQL.ResetConnection restores a clean connection for subsequent queries.
 
Note: In the Tradium IDE (interactive debugging), error recovery typically works without an explicit reset because the IDE re-initializes certain state between runs. However, when the same script runs in the Tradium e-Comms Server runtime (MacroHandler), error state persists across loop iterations. SQL.ResetConnection was designed to address this asymmetry.
 
Important: SQL.ResetConnection does not affect any open ADODB.Recordset objects that were obtained via SQL.RunRecordset. These must be closed individually by the script before or after calling ResetConnection.
 
See Also
RunQueryExecute, RunQueryCheck, RunRecordset, HandleBasicError
Example
Sub Main
Dim sQuery As String
Dim arrBonnen() As String
Dim i As Integer
 
   '--- Process a batch of delivery notes
   arrBonnen = Split("BON001|BON002|BON003", "|")
 
   For i = 0 To UBound(arrBonnen)
 
      On Error GoTo HandleError
 
      sQuery = "EXEC dbo.sp_VerwerkLevBon @BonNr='" _
             & arrBonnen(i) & "'"
      SQL.RunQueryExecute sQuery
 
      Debug.Print "OK: " & arrBonnen(i)
      GoTo NextBon
 
HandleError:
      '--- Log the error
      Debug.Print "FOUT bij " & arrBonnen(i) _
               & ": " & Err.Description
 
      '--- Reset the connection to clear any dirty
      '    transaction state before the next iteration
      If SQL.ResetConnection Then
         Debug.Print "Connection hersteld."
      Else
         Debug.Print "Herverbinding mislukt!"
         Exit For
      End If
 
      Err.Clear
      Resume NextBon
 
NextBon:
   Next i
 
End Sub