ACC2002: Trigger That Contains a User-Defined Error Message Cannot Be Raised (275057)

MORE INFORMATION

Steps to Reproduce Behavior

The following steps assume that you have access to a computer that is running Microsoft SQL Server 7.0 or later.

1. Create a new Access project that is based on the Pubs sample database that ships with SQL Server.
2. Create the following new table in the project:

      Table: Table1
      -----------------------
      Column Name: RecordID
      Data Type: int
      Length: 4
      Allow Nulls: false
   
      Column Name: RecordInfo
      Data Type: Char
      Length: 50
      Allow Nulls: true
   
      Table Properties: Table1
      ------------------------
      PrimaryKey: RecordID
   					

3. Save the table as Table1, and then create a new record as follows:

      Table: Table1
      -----------------------
      Column Name: RecordID
      Column Value: 1
   
      Column Name: RecordInfo
      Column Value: My Info
   					

4. Close the table.
5. Right-click Table1 in the Database window, and then click Triggers on the shortcut menu.
6. When the Triggers for Table: Table1 dialog box appears, click New.
7. In the Table1_Trigger1 : Table1 window, type or paste the following Transact-SQL:

   CREATE TRIGGER Table1_Trigger1
   ON dbo.Table1
   FOR Update
   As
   RAISERROR('You cannot edit this field', 0, -1)
   Rollback Transaction
   					

8. On the File menu, click Save. Click OK to save the trigger with the name of Table1_Trigger1, and then close the Trigger window.
9. Open the Table1 table in Datasheet view, and then edit the record. For example, change "My Info" to "Your Info," and move your mouse pointer to another record.

Note that the trigger is fired and the modification to the record is rolled back as expected, but the error message is not displayed.

This problem also occurs when you use ActiveX Data Objects (ADO), as illustrated in the following example.

1. Complete steps 1 through 8 in the first example, and close the Table1 table if it is still open.
2. ON the Insert menu, click Module, and then type or paste the following code into the module:
   
   Note In the following sample code, you must change user id=<username> and password=<strong password> to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database.

   Option Compare Database
   Option Explicit
   
   Sub subTest()
       Dim con As New ADODB.Connection
       Dim rs As New ADODB.Recordset
       
       con.Provider = "MSDataShape"
       con.Open "Data Provider=sqloledb.1;pwd=<strong password>;uid=<username>;initial " & _
         "catalog=pubs;data source=<ServerName>"
       'In the command line above, replace <ServerName> with the actual name
       'of your SQL Server. 
       'Additionally, if your SQL Server is set up to use NT Integrated 
       'security only, you must remove "pwd=<strong password>;uid=<username>" and add 
       '"Integrated security=sspi" to the connection string.
   
       rs.CursorLocation = adUseClient
       rs.CursorType = adOpenStatic
       rs.LockType = adLockOptimistic
       rs.Open "SHAPE {SELECT * FROM Table1} AS rsLevel0", con
       rs.MoveFirst
       
       rs!RecordInfo = "Your Info"
       rs.Update
   
       rs.Close
       con.Close
       
       Set rs = Nothing
       Set con = Nothing
   
       MsgBox "Done"
   End Sub
   					

3. On the View menu, click Immediate Window, type subTest in the Immediate window, and then press ENTER.
4. Click OK to the message indicating that the code has finished running, and then open the Table1 table in Datasheet view.

Note that the trigger prevented the update as you intended, but you still did not receive the error message from the trigger.