Microsoft KB Archive/101678

{|
 * width="100%"|

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 :