The information in this article applies to:
- Microsoft Access version 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
|