Brandon Garnett
asked on
VBA Code Runs Error Handler with Error Number 0
Hello,
I have a function that gets called to make a DSNLess ODBC Connection to SQL Server to connect my linked Tables. My code runs fine however My Error handler gets ran with Error Number 0. After Research i find out that Error 0 means the code ran successfully so im at a loss as to why it gets thrown into my Error Handler. As you can see in below code, i have an Exit Region with a Exit Function Statement before the Error handler which will prevent it from running on an every time occasion. Any Assistance will be appreciated as i'm not sure what Causes this.
Also Just adding an if statement or select case statement to skip if it is Error 0 is not a preferable answer. I know about these solutions, however, my boss would like to know why its happening in the first place
The Error occurs in the AttachDSNLessTable Function which is called by the DSNLessConnect Function
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
'//Important Notes
'//There are two tables in the front end, these tables contain the server side name and linked name of all SQL Tables linked
'//to database, one is the primary table pointing to the primary server, the other is a secondary server in the event the primary is down
'//the tables links are refreshed every time the FE Loads, if tables are added or removed the appropriate changes need to be noted
'//in the two tables hcc_PrimarySQL and hcc_tblSecondarySQL and the version number updated so clients update when they are opened.
Function AttachDSNLessTable(stLocal
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete
Exit For
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(s
CurrentDb.TableDefs.Append
AttachDSNLessTable = True
AttachDSNLessTable_Exit:
Exit Function
AttachDSNLessTable_Err:
If Err.Number = 0 Then
Resume Next
End If
On Error Resume Next
AttachDSNLessTable = False
DoCmd.Hourglass False
MsgBox "Attempts to connect to SQL Server failed. The system returned the following error: " & Chr(13) & Err.Description & " Error Number: " & Err.Number
Resume AttachDSNLessTable_Exit
End Function
Public Function DSNLessConnect()
On Error GoTo Err_DSNLessConnect
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strLocal As String
Dim strsql As String
Dim strServer As String
Dim strDatabase As String
Set db = CurrentDb
Set rs = db.OpenRecordset("hcc_tblP
rs.MoveFirst
strLocal = rs!LocalTable
strsql = rs!SQLTable
strServer = rs!Server
strDatabase = rs!Database
'Check to see if we are connected to the primary server.
If AttachDSNLessTable(strLoca
'We are connected so we will refreshall the tables
rs.MoveNext
Do Until rs.EOF
If rs!NeedsRefreshed = True Then
strLocal = rs!LocalTable
strsql = rs!SQLTable
strServer = rs!Server
strDatabase = rs!Database
Call AttachDSNLessTable(strLoca
With rs
.Edit
!NeedsRefreshed = False
.Update
.MoveNext
End With
Else
rs.MoveNext
End If
Loop
Else
'// Not okay.We will try the secondary database
Dim rsAlt As DAO.Recordset
Set rsAlt = db.OpenRecordset("hcc_tblS
rsAlt.MoveFirst
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
If AttachDSNLessTable(strLoca
'We have connection to secondary Database
MsgBox "The primary database " & rs!Server & " is not connecting, please contact sys admin." & Chr(13) _
& "Connecting to backup server!", vbOKOnly, "Connection Error"
rsAlt.MoveNext
Do Until rsAlt.EOF
If rsAlt!NeedsRefreshed = True Then
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
Call AttachDSNLessTable(rsAlt!L
With rsAlt
.Edit
!NeedsRefreshed = False
.Update
.MoveNext
End With
Else
rsAlt.MoveNext
End If
Loop
Else
On Error Resume Next
DoCmd.Hourglass False
'Could not connect to either database, catastrophic error, close program after notifying user
MsgBox "No Connection can be made to primary or backup database, please notify sys admin."
DoCmd.Quit
End If
End If
Exit_DSNLessConnect:
On Error Resume Next
db.Close
rs.Close
rsAlt.Close
Set db = Nothing
Set rs = Nothing
Set rsAlt = Nothing
strLocal = ""
strsql = ""
strServer = ""
strDatabase = ""
Exit Function
Err_DSNLessConnect:
DoCmd.Hourglass False
MsgBox "Error Number: " & Err.Number & vbCr & vbCr & "Error Desc: " & Err.Description
Resume Exit_DSNLessConnect
End Function
I have a function that gets called to make a DSNLess ODBC Connection to SQL Server to connect my linked Tables. My code runs fine however My Error handler gets ran with Error Number 0. After Research i find out that Error 0 means the code ran successfully so im at a loss as to why it gets thrown into my Error Handler. As you can see in below code, i have an Exit Region with a Exit Function Statement before the Error handler which will prevent it from running on an every time occasion. Any Assistance will be appreciated as i'm not sure what Causes this.
Also Just adding an if statement or select case statement to skip if it is Error 0 is not a preferable answer. I know about these solutions, however, my boss would like to know why its happening in the first place
The Error occurs in the AttachDSNLessTable Function which is called by the DSNLessConnect Function
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
'//Important Notes
'//There are two tables in the front end, these tables contain the server side name and linked name of all SQL Tables linked
'//to database, one is the primary table pointing to the primary server, the other is a secondary server in the event the primary is down
'//the tables links are refreshed every time the FE Loads, if tables are added or removed the appropriate changes need to be noted
'//in the two tables hcc_PrimarySQL and hcc_tblSecondarySQL and the version number updated so clients update when they are opened.
Function AttachDSNLessTable(stLocal
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete
Exit For
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(s
CurrentDb.TableDefs.Append
AttachDSNLessTable = True
AttachDSNLessTable_Exit:
Exit Function
AttachDSNLessTable_Err:
If Err.Number = 0 Then
Resume Next
End If
On Error Resume Next
AttachDSNLessTable = False
DoCmd.Hourglass False
MsgBox "Attempts to connect to SQL Server failed. The system returned the following error: " & Chr(13) & Err.Description & " Error Number: " & Err.Number
Resume AttachDSNLessTable_Exit
End Function
Public Function DSNLessConnect()
On Error GoTo Err_DSNLessConnect
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strLocal As String
Dim strsql As String
Dim strServer As String
Dim strDatabase As String
Set db = CurrentDb
Set rs = db.OpenRecordset("hcc_tblP
rs.MoveFirst
strLocal = rs!LocalTable
strsql = rs!SQLTable
strServer = rs!Server
strDatabase = rs!Database
'Check to see if we are connected to the primary server.
If AttachDSNLessTable(strLoca
'We are connected so we will refreshall the tables
rs.MoveNext
Do Until rs.EOF
If rs!NeedsRefreshed = True Then
strLocal = rs!LocalTable
strsql = rs!SQLTable
strServer = rs!Server
strDatabase = rs!Database
Call AttachDSNLessTable(strLoca
With rs
.Edit
!NeedsRefreshed = False
.Update
.MoveNext
End With
Else
rs.MoveNext
End If
Loop
Else
'// Not okay.We will try the secondary database
Dim rsAlt As DAO.Recordset
Set rsAlt = db.OpenRecordset("hcc_tblS
rsAlt.MoveFirst
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
If AttachDSNLessTable(strLoca
'We have connection to secondary Database
MsgBox "The primary database " & rs!Server & " is not connecting, please contact sys admin." & Chr(13) _
& "Connecting to backup server!", vbOKOnly, "Connection Error"
rsAlt.MoveNext
Do Until rsAlt.EOF
If rsAlt!NeedsRefreshed = True Then
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
Call AttachDSNLessTable(rsAlt!L
With rsAlt
.Edit
!NeedsRefreshed = False
.Update
.MoveNext
End With
Else
rsAlt.MoveNext
End If
Loop
Else
On Error Resume Next
DoCmd.Hourglass False
'Could not connect to either database, catastrophic error, close program after notifying user
MsgBox "No Connection can be made to primary or backup database, please notify sys admin."
DoCmd.Quit
End If
End If
Exit_DSNLessConnect:
On Error Resume Next
db.Close
rs.Close
rsAlt.Close
Set db = Nothing
Set rs = Nothing
Set rsAlt = Nothing
strLocal = ""
strsql = ""
strServer = ""
strDatabase = ""
Exit Function
Err_DSNLessConnect:
DoCmd.Hourglass False
MsgBox "Error Number: " & Err.Number & vbCr & vbCr & "Error Desc: " & Err.Description
Resume Exit_DSNLessConnect
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi fyed,
thanks for the additional clarification...;-)
I also forgot to mention explicitly that of course the "If Err.Number = 0" is not needed as this will never be true. So there is no "Resume" necessary as there was no error.
Cheers,
Christian
thanks for the additional clarification...;-)
I also forgot to mention explicitly that of course the "If Err.Number = 0" is not needed as this will never be true. So there is no "Resume" necessary as there was no error.
Cheers,
Christian
ASKER
Thanks Everybody. I knew from some where i read on the internet in my searches that it was not good to have an "On Error.." statement in the Error Handler but rather a "Resume.." statement, however I kept overlooking that one apparently. Thanks much for your help
To clarify, you have "on error resume next" at the beginning of your error handler. While that is useful in your the Exit portion of your code, it is resetting Err.Number to zero right at the beginning of your error handler. Remove that line or remark it out and you should be OK.
AttachDSNLessTable_Err:
If Err.Number = 0 Then
Resume Next
End If
On Error Resume Next