Trap SQL Server errors

[JRE]

Is it possible to trap SQL Errors with Access VBA? I'm building an Access
2000 to SQL Server 2000 (via ODBC) application that prompts the user for
their SQL Server password. If the wrong password is entered (and
concatinated into the DNS-less connection string) the authentication will
fail and display a SQL Server generated error. I put in a simple error
trapping routine, but the SQL Server error is displayed before my error
trapping fires.

Thanks for any help you can provide.

Joe

 

[JRE]

I Forgot to mention that I'm using DAO and not ADO.

 

[Joe Fallon]

This is a piece of a regular error handler that calls a function to
enumerate any ODBC errors:

MyErrorHandler:
If fInTrans Then
wrk.Rollback
MsgBox "Error occurred. Transaction rolled back. No records were
added.", vbOKOnly + vbCritical, "Append Data"
EnumerateErrors
......


Public Function EnumerateErrors()
Dim errValue As Error
Dim lngCounter As Long

Select Case Err
'ODBC Error
Case 3146 To 3299
lngCounter = 1
If Err.Number = 3157 Then
MsgBox "Hey." & vbCRLF & "Call your DBA", vbOKOnly + vbCritical,
"MyApp"
End If
For Each errValue In DBEngine.Errors
MsgBox ("Server Error #" & lngCounter & vbCRLF & "Error #" &
errValue.Number & " was generated by " & errValue.Source & vbCRLF &
"Description: " & errValue.Description)
lngCounter = lngCounter + 1
Next errValue

'Not an ODBC error
Case Else
MsgBox ("Error number not in range of 3146 to 3299. Enumerate errors.")

End Select

End Function

 

[JRE]

Thanks Joe,

The code indeed does work, but fires after the SQL Server error is
displayed. I was hoping that the error handler would suppress the SQL Server
error message box.and allow me to display my own.

Regards,
Joe

 

[david epsom dot com dot au]

fail and display a SQL Server generated error. I put in a simple error

????

If I enter an invalid password, the SQL Server Driver presents a log-in
dialog.


When you are using the ODBC API, and you set the prompt argument of the
OpenConnection method to prohibit user completion of missing ODBC connect
arguments, a trappable error is triggered. Otherwise the ODBC driver manager
displays a dialog box to gather missing information from the user.

In Access, there is no way to set or clear this parameter.

(david)