Microsoft KB Archive/101678

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 :