ACC1x: Trapping SQL Server RAISERROR() Function Values

• 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

1. 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' 




2. 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 




3. 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 IsmPdox
Version : 1.1
Platform : WINDOWS
Issue type : kbinfo