ODBC connection - custom error handling

[mrsbean]

I want to control the error message(s) which are received when/if an error is thrown when trying to connect to an external table in a SQL database. I have some code which refreshes the link to any/all linked tables. It works great. The user could muck it up by accidently typing the SQL server location incorrectly or entering a username password combination which is incorrect or which does not have the rights needed to view the data. In any of those cases, a large error message appears which goes something like (ignore typos as I used OCR to change a screen grab into text):

Connection failed:
SQLState: 01S00
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]lnvalid connection string attribute Connection failed:
SQLState: 01000
SQL Server Error: 53
[Microsoft]IODBC SQL Server Driver ][DBNE TLIB]ConnectionO pen (Connect(fl. Connection failed:
SQLState: 08001’
SQL Server Error 17
[Microsoftl(DDBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

In the function which refreshes the links, I have the following code to handle errors:

ErrorHandlerExit:
    Exit Function
ErrorHandler:
    MsgBox Err.Number & " : " & Err.Description, vbExclamation, "ODBC Error"
    Resume ErrorHandlerExit

I want better control of the error message which appears when it encounters an ODBC error. If this question is too complex to address in this setting, please refer me to a book which teaches how to do it. Thank you.

MrsBean

 

[rjoubert]

If you know the Err.Number of each of the errors you want to handle, you can add an IF...ELSEIF block or SELECT CASE block to your ErrorHandler.

If Err.Number = 123 Then
   MsgBox "ERROR 123 - BLAH BLAH BLAH"
ElseIf Err.Number = 456 Then
   MsgBox "ERROR 456 - BLAH BLAH BLAH"
Else
   MsgBox Err.Number & " - " & Err.Description
End If

OR...

Select Case Err.Number
   Case 123
      MsgBox "ERROR 123 - BLAH BLAH BLAH"
   Case 456
      MsgBox "ERROR 456 - BLAH BLAH BLAH"
   Case Else
      MsgBox Err.Number & " - " & Err.Description
End Select

 

[mrsbean]

The problem is that the error(s) which I provided for you are not VBA errors, and they do not respond to the usual approach for handling VBA errors. No matter what instruction is put into the error handler above, the SQL errors come up first. After the user clicks OK in the box which shows the SQL errors, the VBA error message comes up.

MrsBean

 

[rjoubert]

In that case, you can trap for certain keywords within the Err.Description.

If Instr(1, Err.Description, "Invalid connection string") > 0 Then
   Msgbox("You entered an invalid user id or password.")
ElseIf Instr(1, Err.Description, "SQL Server does not exist") > 0 Then
   Msgbox("BLAH BLAH BLAH")
End If

 

[mrsbean]

No. That doesn't work. This is not a VBA error ... It comes from a problem with the SQL log in/connection string. This type of error appears before the VBA message box. After this error box has appeared, any VBA error handling appears.

MrsBean

 

[abaldwin]

I would first take a look at basic error trapping in vb. The on error goto statement should be before the connection attempt. This will trap the connection error and your error handler will be as suggested above.

Maybe posting your call statement and your current connection code will help the confusion here.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[dabruins]

I'm working on a similiar problem. I was involced in a discussion about this a while back in the linked thread:

http://www.tek-tips.com/viewthread.cfm?qid=1225294&page=1

You have to trap the ODBC errors via the connection to the data provider. In my case its an ADODB connection so I have to look for errors returned in my ADODB.errors collection. The errors contained in the collection are of the type ADODB.error which has a number of different properties such as SQLstate, number and description. You can test for these values in a SELECT CASE statement once you have trapped the ADODB error and respond accordingly. See below.

I'm running into a problem trying to correct my connection parameters passed to the connection object when it fails. I would like to be able to reset the connection and continue on with execution of a recordset opening or adodb.command object. I'm pretty green on this stuff so if you find out anything let me know how it works out.

Err_Handler:
Dim erADO As ADODB.Error
For Each erADO In conn.Errors 'conn is an ADO.connection
SELECT CASE erADO.SQLstate
CASE "S1T00"
'Do whatever to respond to the SQLstate
Resume Next
CASE "01S01"
MsgBox = erADO.Number & " " & erADO.Description
Resume EXIT_SUB
END SELECT
Next


For a list of ODBC SQLstate values see:

http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html

 

[mrsbean]

Now we're getting somewhere dabruins.

Here is the code for the function which refreshes the links in the remote tables:

Function LinkTableDefs()
On Error GoTo ErrorHandler
  
  Dim dbs As dao.Database
  Dim tdf As dao.TableDef
  Dim ThisServer As String
  
  DoCmd.Hourglass (HourglassOn)

  If IsNull(DLookup("ServerLocation", "tblServerLocation", "[Active] = -1")) Then
  Dim strSQL As String
  strSQL = "UPDATE tblServerLocation SET tblServerLocation.Active = -1 " & _
    " WHERE (((tblServerLocation.ServerLocation)='(Local)')) "
    DoCmd.RunSQL strSQL
    
    End If
    

  ThisServer = DLookup("ServerLocation", "tblServerLocation", "[Active] = -1")
  ThisUser = DLookup("sqluser", "tblServerLocation", "[Active] = -1")
  ThisPassword = DLookup("sqlpassword", "tblServerLocation", "[Active] = -1")
  
  Set dbs = CurrentDb()
  
  ' Loop through TableDefs collection, only processing
  ' the table if it already has a Connection property.
  ' (all other tables are local ... not linked)
  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
      tdf.Connect = "DRIVER=SQL Server;SERVER=" & ThisServer & ";UID=" & ThisUser & ";PWD=" & ThisPassword & ";DATABASE=VAMDATA; Persist Security Info=True;Connect Timeout=60"
      tdf.RefreshLink
    End If
  Next
  
  DoCmd.Hourglass (HourglassOff)

ErrorHandlerExit:
    Exit Function
ErrorHandler:
    MsgBox Err.Number & " : " & Err.Description, vbExclamation, "ODBC Error"
 Resume ErrorHandlerExit
  
End Function

Because my connection is not an Ado connection, I'm not sure what to change. Can you help me?

MrsBean

 

[abaldwin]

If you walk through the code (put a break on the first line) and then use F8 to walk through the code.....where does it error at?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[dabruins]

It looks like you are using DAO so you should look into the errors collection of DAO and see if what type of information it returns in an error spawned from a connection to your datasource. I don't think it provides and SQLstate value but it looks to have a Number value.

I think you could get by with:
Err_Handler:
Dim erDAO As DAO.Error
For Each erDAO In conn.Errors 'conn is an DAO.connection
SELECT CASE erDAO.Number
CASE "01S00"
'Do whatever to respond to the error number
Resume Next
CASE "08001"
'Do whatever to respond to the error number
Resume Next
CASE "01000"
'Do whatever to respond to the error number
Resume Next
CASE else
MsgBox = erDAO.Number & " " & erDAO.Description
Resume EXIT_SUB
END SELECT
Next

See the following to get a better understanding of the VBA error-handling and debugging as it pertains to ODBC sources.

http://www.microsoft.com/technet/prodtechnol/office/office2000/solution/part3/ch14.mspx?mfr=true

 

[mrsbean]

Thank you. I know that I'm almost there, but I did not declare a variable named Conn, and I have no experience with setting the connection/database in this way, so I'm stumbling around a bit.

The connection to the tables is via linked tables. The connections are refreshed in the code given. I need to do something differently.

Here is a portion of the code above which has been changed a little bit:

  Dim erDAO As dao.Error
   Dim Conn As dao.Connection
   Dim db As Database
   
    Set db = CurrentDb
    Set Conn = CurrentDb.Connection
    
   
   
   
  For Each erDAO In Conn.Errors 'conn is an DAO.connection
    Select Case erDAO.Number

Errors is not recognized as an available method or data memeber. Do I need to add/change something to the code or do I need to add a reference?

MrsBean

 

[PHV]

Errors is a collection of the DAO.DBEngine object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886