ACC1x: SQL Delete Triggers Function Incorrectly

• Microsoft Access version 1.0

SYMPTOMS

Microsoft Access appears to delete a record in an attached SQL table, but the record is not actually deleted. The record will reappear when you requery the table.

CAUSE

The Microsoft SQL Server driver that comes with Microsoft Access version 1.0 incorrectly indicates that the result of the SQL delete action is successful.

RESOLUTION

To correct this problem, you must DROP the current trigger and CREATE a new delete trigger on the SQL Server that uses the RAISERROR command instead of the PRINT command. This method causes Microsoft Access version 1.0 to not delete the record and display the following error messages:

   ODBC - delete failed.

   [Microsoft][ODBC SQL Server Driver][SQL Server] <MESSAGE> (#-31073)

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 1.0. This problem was corrected in Microsoft Access version 1.1.

MORE INFORMATION

If you try to delete a record from an attached SQL table that has a delete trigger that performs a ROLLBACK and a PRINT action on the SQL Server, Microsoft Access version 1.0 appears to delete the record and then displays a message box asking you to verify your deletion. The record is actually not deleted and reappears when you requery the table by pressing SHIFT+F9, choosing Show All Records from the Records Menu, or closing and reopening the table.

In Microsoft Access version 1.1, the RAISERROR method works the same way that it does in version 1.0. The PRINT method works similarly to the way it does in version 1.0.

In version 1.0, the PRINT method appears to let you delete the record without a warning, then later the record reappears. In version 1.1, the PRINT method still does not display the message specified by the PRINT command; however, it does not remove the record, and it does display the following error message:

   ODBC - call failed.

Steps to Reproduce Problem

1. Create a trigger on a SQL Server table that uses the SQL commands ROLLBACK and PRINT.

2. Attach the SQL table that the delete trigger was created for by choosing Attach from the File menu.

3. Once the table is attached, open it in Datasheet view.

4. Select a record by clicking the gray record selector to the left of the record or by choosing Select Record from the Edit menu.

5. Delete the record by pressing the DEL key or by choosing Delete from the Edit menu.

6. Choose Yes in the Confirmation dialog box that is displayed.

REFERENCES

"Microsoft SQL Server Language Reference," version 4.2, pages 88-93, 137, 203-204, and 214-216