ACC1x: Trapping SQL Server RAISERROR() Function Values |
Q101678
The information in this article applies to:
- Microsoft Access 1.1
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
Microsoft SQL Server users often create triggers or stored procedures that perform specific functions which are fired during certain events. Often, a custom-generated error value is desired to indicate the status of such events. This article describes a way to trap that value in Microsoft Access.
NOTE: This article assumes that the user is running Microsoft Access version 1.1, because that version corrects a problem to allow the SQL Server RAISERROR() function to return a value to Microsoft Access.
MORE INFORMATION
Create the following stored procedure based on the sample database PUBS in Microsoft SQL Server:
CREATE PROCEDURE TestProc AS RAISERROR 25000 'This is a test error'
Create a trigger on the table from which you want this stored procedure to run:
NOTE: Stored procedures cannot be executed directly from Microsoft Access 1.1, unless they are triggered from a SQL Server trigger or the SQL pass-thru .DLL file (SPT110.DLL) is used. However, when using version 2.0, you can invoke a stored procedure using an SQL pass- through query. For this example, you will use a trigger that fires when a record is updated.
CREATE TRIGGER TestTrig ON Authors FOR UPDATE AS EXECUTE TestProc
Add the following code to an Access Basic module. The subprocedure updates the Authors table (the attached table dbo_authors) in some way, and traps the error value that is passed by the RAISERROR() function:
Sub TrapIt () Dim db As Database, Mydyna As Dynaset Dim Xerr As String, Xval As Integer, Dim Xstart As Integer, Xlen As Integer On Error GoTo ErrorHandler Set db = CurrentDB() Set Mydyna = db.CreateDynaset("select * from dbo_authors;") Mydyna.Edit Mydyna!au_fname = Mydyna!au_fname Mydyna.Update Mydyna.MoveNext Exit Sub ErrorHandler: 'This routine parses the error string returned from ODBC and 'extracts only the error value you assigned with the RAISERROR() 'function in SQL Server. 'traps the error message xerror = Error$ 'finds start of error value Xstart = InStr(1, xerror, "#") + 1 'finds length of error value Xlen = InStr(Xstart, xerror, ")") - Xstart 'extracts error value from string Xval = Mid(xerror, Xstart, Xlen) MsgBox ("You have encountered error #" & CStr(Xval)) Resume Next End Sub
Keywords : kb3rdparty
Issue type : kbinfo
Technology :
Last Reviewed: November 4, 2000 |