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
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")
Do Until rs.EOF = True
Dim dbRemote As Database
Dim wsRemote As Workspace
Text9 = "Connecting to " & rs!Description
Set wsRemote = DBEngine.Workspaces(0)
Select Case rs!contype
strConnect = "ODBC;"
'Set dbRemote = wsRemote.OpenDatabase(rs!dsn, False, True, strConnect)
Set dbRemote = wsRemote.OpenDatabase(rs!dsn, False, True, rs!Connect)
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 )
Text9 = rs!Description & " Connection Successful"
If Err.Number = 3151 Then
Call fCreateODBC(rs!dsn, rs!Driver, rs!Server, rs!Description)
MsgBox "Error connecting to - " & rs!Server & " with " & rs!Driver & vbCrLf & "Error - " & Err.Number
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"
MsgBox "Error creating ODBC Driver - " & sDriver & " for " & sServer & vbCrLf & Err.Number & " - " & Err.Description