Annie,
I needed a similar thing once - return meaningful SQL error message to
Access client.
My kludgey fix was this
* get the list of SQL Server errors and put it in a local Access table.
* use pass-thru queries - execute them from a error-trapped subroutine in
Access
* wrap the base SQL statement with TransactSQL code to trap errors and write
them to an error log table
* link to the error log table (WORK_ERRORS)
* when an error occurs, poll WORK_ERRORS for the Username and query the
error number against the local list of SQL error messages.
It works pretty good, actually, but it's a total kludge involving a SQL
error log table, a SQL rows affected table, and a separate local Access
table.
Public Sub alterODBCrecords(cSQL As String)
'THIS ROUTINE UPDATES THE PASS-THRU QUERY WITH THE SQL STRING PASSED INTO
THE SUB
'THEN IT SENDS THE PASS-THRU QUERY TO THE SERVER FOR EXECUTION
'ALSO TRACK THE NUMBER OF ROWS UPDATED, AND THE ERROR CODE IF ANY
On Error GoTo errAlterODBC
Set qItem = db.QueryDefs("Q_PASS_THRU")
qItem.ReturnsRecords = False
'TRAP ERRORS ON SQL SERVER - WRITE TO LOG TABLE. HANDLES ROWS AFFECTED
BETTER
qItem.SQL = "DECLARE @ERR int SET @ERR = 0 "
qItem.SQL = qItem.SQL & "DECLARE @ROWSAFFECTED int SET @ROWSAFFECTED =
0 "
qItem.SQL = qItem.SQL & cSQL 'THIS cSQL IS THE BASE SQL STATEMENT YOU
WANT TO EXECUTE
qItem.SQL = qItem.SQL & " SELECT @ERR = @@ERROR, @ROWSAFFECTED =
@@ROWCOUNT "
qItem.SQL = qItem.SQL & " UPDATE WORK_ROWS_AFFECTED SET RowsAffected =
@ROWSAFFECTED WHERE AppUserID = '" & getLoggedUser() & "'; "
qItem.SQL = qItem.SQL & " IF @ERR <> 0 BEGIN INSERT INTO WORK_ERRORS
(AppUserID, errorNum) VALUES ('" & getLoggedUser() & "', @ERR); END "
qItem.Close
qItem.Execute
exitAlterODBC:
Exit Sub
errAlterODBC:
globalErr = "pass-thru error"
'RETRIEVE SQL SERVER ERROR - LOGGED IN SQL TABLE VIA ABOVE
Dim odbcErr As Integer, Set rs2 = db.OpenRecordset("SELECT errorNum FROM
WORK_ERRORS WHERE AppUserID = '" & getLoggedUser() & "';")
odbcErr = rs2("errorNum")
rs2.Close
'REFER TO LOCAL TABLE TO GET ODBC ERROR DESCRIPTION
dim odbcErrDescription As Variant
odbcErrDescription = DLookup("ErrorDescription", "Sys_SQLErrorMessages",
"ErrorMsg = " & odbcErr & "")
if isnull(odbcErrDescription) then
odbcErrDescription = "Error " & odbcErr & ": error description not found
else
odbcErrDescription = DLookup("ErrorDescription", "Sys_SQLErrorMessages",
"ErrorMsg = " & odbcErr & "")
End If
MsgBox "SQL Server error " & odbcErr & ": " & odbcErrDescription
Resume exitAlterODBC
End Sub
"annie" <ro**********@hotmail.com> wrote in message
news:89**************************@posting.google.c om...
Hi all,
I have recently ported my Access 2000 app to SQL Server, keeping the
Access client as the front end using linked tables.
I am also using triggers on my SQL tables to trap orphan records and
validate added data.
My question is..
During testing, I created a form in Access to replicate what would
happen if a user tried to add data which did not conform to the rules
of the trigger.
In my error handling, I want to be able to grab the error that SQL
Server returns (not the standard Access error) but I can't seem to
figure out how to do this. The best I get is a message saying "3155
ODBC--insert on a linked table failed".
If I remove the error handling from the AddRecord procedure, I get the
correct error message from SQL Server. Good coding practice is that
you should always add error handling so I am reluctant to remove the
error handling procedure just to achieve my goal.
Does anyone know how to force Access to display the message that SQL
Server returns? It would probably be something like the
ADODB.ErrorValueEnum property.
Many thanks!
Annie
