Access Error Handling Issues

I’m having issue with my error handling routine
The users click a button to connect to two external data sources via ODBC connections
The data to login or create the ODBC links are stored in a table
If the ODBC driver has not been set up the connection attempt returns an error 3151 – the error handler then runs  a function to create the connection and then goes back to retry the login
It should do this for each connection – right now there are only two connections

What happens:
If both connections need to have the ODBC driver created the second one fails – the error handler kicks to the first one over to the function – it creates the driver & goes back to the login to prompt for user credentials – but when it tries the 2nd connection the 3151 error never gets passed to the error handler, instead it pops up on the screen as if there was no error handler.
If either of the connections already exists the other connection will be added and if both connections exist it will prompt for both logins and the connections work.

So I don’t think it’s the ODBC code causing the issue but why does my error handle die?


Private Sub cmdLogin_Click()
    On Error GoTo LoginError
    Dim db As Database
    Dim rs As Recordset

    Dim strConnect As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ODBCLinks")
    rs.MoveFirst

    Do Until rs.EOF = True
        Dim dbRemote As Database
        Dim wsRemote  As Workspace
    
        Text9 = "Connecting to " & rs!Description
TryAgain:

        Set wsRemote = DBEngine.Workspaces(0)
        Select Case rs!contype
            Case 1
                strConnect = "ODBC;"
                'Set dbRemote = wsRemote.OpenDatabase(rs!dsn, False, True, strConnect)
                Set dbRemote = wsRemote.OpenDatabase(rs!dsn, False, True, rs!Connect)
            Case 2
                strConnect = "ODBC;DSN=" & rs!dsn & ";Database=' ';"              ' & dbDriverPrompt
                Set dbRemote = wsRemote.OpenDatabase("", False, True, strConnect)
              ' set dbremote = wsremote.OpenDatabase (name as String,options,readonly,connect as Database )
        End Select

        Text9 = rs!Description & " Connection Successful"
        rs.MoveNext
    Loop
    Exit Sub
    
LoginError:
   
    If Err.Number = 3151 Then
        Call fCreateODBC(rs!dsn, rs!Driver, rs!Server, rs!Description)
        GoTo TryAgain
    Else
         MsgBox "Error connecting to - " & rs!Server & " with " & rs!Driver & vbCrLf & "Error - " & Err.Number
    End If
    
End Sub

Private Function fCreateODBC(sDSN As String, sDriver As String, sServer As String, sDesc As String)
On Error GoTo CreateError
    Text9 = " Setting up new driver"
    DBEngine.RegisterDatabase sDSN, sDriver, True, "Description=" & sDesc & Chr(13) & "Server=" & sServer & Chr(13) & "Database=" & sServer
    MsgBox sDSN & " connection created sucessfully"
    GoTo ExitFunction
    
CreateError:
    MsgBox "Error creating ODBC Driver - " & sDriver & " for " & sServer & vbCrLf & Err.Number & " - " & Err.Description
ExitFunction:

End Function

Open in new window

CountryGirlMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

try clearing the error


LoginError:
   
    If Err.Number = 3151 Then

        err.clear     ' add this line

        Call fCreateODBC(rs!dsn, rs!Driver, rs!Server, rs!Description)
        GoTo TryAgain
    Else
         MsgBox "Error connecting to - " & rs!Server & " with " & rs!Driver & vbCrLf & "Error - " & Err.Number
    End If
   
CountryGirlMDAuthor Commented:
didn't help --  I had tried that earlier - but I had it between the call & goto lines - tried it in your postition same results

CountryGirlMDAuthor Commented:
Found the issue - I need to use Resume instead of GoTo
The Resume 'closes" the first error - see the MS excerpt below

LoginError:
    If Err.Number = 3151 Then
        Call fCreateODBC(rs!dsn, rs!Driver, rs!Server, rs!Description)
        Resume TryAgain
    Else
         MsgBox "Error connecting to - " & rs!Server & " with " & rs!Driver & vbCrLf & "Error - " & Err.Number
    End If

Microsoft Help - On Error Statement
"If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CountryGirlMDAuthor Commented:
found the answer myself
LambertHeenanCommented:
Indeed CountryGirl "Resume" not "Goto" is you solution. Good that you got ther without our help :-)

I have a golden rule in VB/VBA, I only ever use Goto to set up an  error handler, because I'm forced to. Otherwize the keyword Goto does not appear in any of my code.

Lambert
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.