Wrox Programmer Forums
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 10th, 2003, 05:54 AM
Friend of Wrox
Join Date: Jul 2003
Posts: 142
Thanks: 0
Thanked 2 Times in 2 Posts
Default On Error Resume Next?

I've had a problem with the following code, which is that sometimes a ADO connection will fail to open, and so an error is generated.

I added in an error handler for this, which basically closed all connections and recordsets and exited, because the subroutine was executed by a timer control every minute and it didn't matter if we missed one poll. However, it generated an error when closing the recordsets/connections. This in itself is not a surprise, because they won't be open - the strange thing is that it will not resume next, or indeed go to any error handler. It just continues to stubbornly raise errors.

I know there is a way around this (test for object state being open before closing), but I was curious if anyone else had seen this and if they managed to overcome it.

Public Sub Process(sourceQuery As String, destQuery As String, SyncType As String)

    Dim conSource As New ADODB.Connection, conDest As New ADODB.Connection
    Dim rsSource As New ADODB.Recordset, rsDest As New ADODB.Recordset
    Dim retries As Integer

    On Error GoTo ExitSub ' version 1.1.0; sometimes errors generated when opening
                         ' connections; just exit and try later if this happens...

    conSource.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                    "Data Source = " & serverName & ";Initial Catalog=" & dbName
    conDest.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;" & _
                    "Dbq=" & dbaseFilesPath

    ' get source data, static as it may be updated while we are working...
    rsSource.Open sourceQuery, conSource, adOpenStatic, adLockOptimistic

    ' get dest data...
    On Error GoTo Flaky_Dbase_Driver
    rsDest.Open destQuery, conDest, adOpenDynamic, adLockOptimistic
    On Error GoTo 0

    UpdateTable rsSource, rsDest, SyncType

    UpdateLastSyncDate SyncType

' flow into error handler to close recordsets and connections...
ExitSub: ' version 1.1.0

    ' version 1.2.0 - added in if obj.state = open then obj.close
    On Error Resume Next
    Set rsSource = Nothing
    Set rsDest = Nothing
    Set conDest = Nothing
    Set conSource = Nothing

    Exit Sub


    ' try removing the flicks to make the query work...
    destQuery = Replace(destQuery, "`", "")
    ' keep trying...
    On Error Resume Next
    ' try opening a few times as it can be a bit obstreporous...
    For retries = 1 To 10
        rsDest.Open destQuery, conDest, adOpenDynamic, adLockOptimistic
        If Not Err.Number <> 0 Then ' were any errors generated?
         ' no, recordset open and we can return to main execution flow...
         Exit For
        End If

    Resume Next

End Sub
Old July 10th, 2003, 06:31 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post

...the strange thing is that it will not resume next, or indeed go to any error handler. It just continues to stubbornly raise errors.
This is because your error handler is already actively handling an error. If error handling code generates errors itself (as yours does) the error is immediately raised up to the calling procedure - putting On Error Resume Next in there won't help you.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
Resume Uploading And Downloading sumith ASP.NET 1.0 and 1.1 Professional 0 May 24th, 2007 01:40 AM
the e of resume humayunlalzad BOOK: Beginning XML 3rd Edition 1 November 2nd, 2006 08:07 AM
what is "on error resume next" silver_cuts Classic ASP Basics 2 June 29th, 2004 04:38 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.