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(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
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 stLocalTableName
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(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
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_tblPrimarySQL")
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(strLocal, strsql, strServer, strDatabase, "", "") Then
'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(strLocal, strsql, strServer, strDatabase, "", "")
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_tblSecondarySQL")
rsAlt.MoveFirst
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
If AttachDSNLessTable(strLocal, strsql, strServer, strDatabase, "", "") Then
'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!LocalTable, rsAlt!SQLTable, rsAlt!Server, rsAlt!Database, "", "")
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
Our community of experts have been thoroughly vetted for their expertise and industry experience.