Access 97 vs. Access 95 prob deleting row from SQL Server

[Scott2S]

I posted this on the SQL forum, but is probably more geared to this forum - so I will try this again:

I have a SQL 6.5 database on NT4.0. All referential integrity rules and "constraints" are coded in at the trigger level.

If an user using Access 95 links to a table of this database and attempts to delete a record that has related records, the delete fails with an error #3197 "Data has changed; operation stopped". I am not thrilled with this error because it doesn't reflect the true cause of the delete fail - the trigger rolling back the transaction.

If an user using Access 97 attempts this exact same process, Access 97 asks if he is sure that he wants to delete the data and then deletes it. At first the really freaked me out, but when I refreshed the table view the supposed deleted record reappeared.

I have a few questions about this. First of all why do I get different results with the version of Access? Second, do I have to use SQL’s referential integrity rules by setting up relations in the database or can I continue to use triggers with some added code in the trigger to give an error condition back to ODBC?

Thanks in advance,
Scott

 

[MarkSweetland]

I'm not sure why Access 95 vs. Access 97 give differing results, but checkout the Microsoft Knowledge Base Article ID: Q152021 at

http://search.support.microsoft.com/kb/c.asp

which discusses deleting records containing NULLs using DAO:

&quot;The SQL Server 6.5 ODBC driver always sets the ANSI_NULLS option to ON on every connection that is to be ANSI compliant. This means that a search condition like 'WHERE <colname> = NULL' is always evaluated to FALSE. In accordance with the ANSI specification, the correct syntax when searching for NULLs is 'WHERE <colname> IS NULL'.

Consider a SQL Server table attached in Access/Visual Basic that has NULL values. An attempt to delete a row that has one or more NULL fields will not succeed, because Access/Visual Basic uses the following syntax:

   Delete from <table-name> Where <colname> = NULL And <colname> ='xxx'...............

Because the above search condition is always evaluated to FALSE, no rows will be affected by the delete and Access/Visual Basic will display the message, &quot;Data has changed. Operation stopped.&quot;


Mark

 

[Scott2S]

Well, the trigger is keeping the row from being deleted because of relational records - this is exactly the way is is supposed to work.

The problem is that when an user tries to delete something that has relational records it doesn't give the user feedback that the delete really failed in Access 97 and in Access 95 it gives the incorrect error of 3197.

 

[MarkSweetland]

Have you tried to raise an error from your trigger to return an ad_hoc message to the application on why the transaction failed?

RAISEERROR ( <strMsg> , <severity>, <state> )

.
.
SELECT @del_error = @@ERROR
IF @del_error <> 0
BEGIN
   RAISEERROR (&quot;A delete error occurred&quot;, 16, 1)
   ROLLBACK TRANSACTION
END

 

[Scott2S]

I don't know. Maybe that is my problem. Here is the trigger and I thought it was right. What do you think?


CREATE TRIGGER tblTrailer_DTrig ON tblTrailer FOR DELETE AS
/*
* PREVENT DELETES IF DEPENDENT RECORDS IN 'tblBOL' - TrailerNmbr OR
PupNmbr
*/
IF ( SELECT COUNT(*) FROM deleted, tblBOL
WHERE (
deleted.CarrierNmbr = tblBOL.CarrierNmbr
AND (
(deleted.TrailerNmbr = tblBOL.TrailerNmbr
AND deleted.IsPup = 0)
OR (deleted.TrailerNmbr =
tblBOL.PupNmbr AND deleted.IsPup = 1)
)
)
) > 0
BEGIN
RAISERROR ('TRIGGER - Cannot delete or change record. Since
related records exist in table tblBOL.', 1, 1)
ROLLBACK TRANSACTION
END

 

[MarkSweetland]

Looks ok to me; but you mentioned in your first message that all referential integrity was enforced at the trigger level. Have you double checked the tables to see if someone may have added Primary and Foreign Keys? If constraints exist on the trigger table, they are checked prior to trigger execution. If either PRIMARY KEY or FOREIGN KEY constraints are violated, the trigger is not executed (fired) and may be causing the cryptic message as well as not allowing the delete.


Mark

 

[Scott2S]

First of all, thank you for all your information so far!

I know for sure that no constraints exist in the database structure. I spoke with the db developer and snooped around in SQL enterprise manger. The developer decider to perform all constraint type checking at the trigger level (I know it takes a little more of SQL's processing power) but he felt it gave more flexibility because you can modify and drop triggers easier than you can a contraint.

There are primary keys on most tables and no foreign keys because no relations are setup in SQL - they are done with triggers also (i.e. cascade deletes, etc.)


How about the raise error of the trigger?

RAISERROR ('TRIGGER - Cannot delete or change record. Since
related records exist in table tblBOL.', 1, 1)

How do I know what severity and state should be?

When this error is raised, how can Access get the message?

 

[MarkSweetland]

Severity 0 through 18 are safe to user for any user. Levels 19 through 25 are for those in the sysadmin fixed user role and levels 20 through 25 are considered fatal by the server. State can be any number 1 to 127 (its arbitrary).

I would focus on the Access error trapping methods that you have employed at the application level. This is where you parse the errors returned by the ODBC driver and SQL Server. The dbEngine Errors collection can contain more than one Error Object that specify errors that occur at different levels. If you only look at the first error in the collection, or employ no error trapping, you may be leaving your Server generated error untouched, and displaying only the first level ODBC error through the VBA Err object.

    Dim errX as Error

    For Each errX In DBEngine.Errors
        Debug.Print errX.Description
        Debug.Print errX.Source
        Debug.Print errX.Number
    Next errX

Mark

 

[Scott2S]

OK, I will add that error trap to the forms.

In my example from my first post, the delete was performed directly on a linked table by openening the table direct from the table table on the database window of Access. Because of this, should I assume the descrepancies between 95 & 97 on the default error messages are from Jet versions? I understand what you are saying about the error trapping but I still don't get it why a raw table delete like that between 95 & 97 are so different.

 

[Scott2S]

I have since found that the trigger error does get raised from triggers that do not have any AND or OR statements. I know this sounds dorky, but the trigger does indeed work - you can't delete the record in ISQL, Access 95 or Access 97, you just dont' get told in your access table view that the record couldn't get deleted.

Why would one trigger get the proper error raised and not another? I am using the same error level number for each.

Thanks in advance,
Scott

 

[MarkSweetland]

The short answer is ... I don't have a clue. It may have something to do with the COUNT aggregate in the SELECT. Have you tried to reword the SELECT at all?
i.e.;

if exists (
    select tblBOL.CarrierNmbr 
    from tblBOL left outer join deleted on 
        (deleted.CarrierNmbr = tblBol.CarrierNmbr)
    AND ((deleted.TrailerNmbr = tblBol.TrailerNmbr AND deleted.IsPup = 0) OR (deleted.TrailerNmbr = tblBol.PupNmbr AND deleted.IsPup = 1)))
BEGIN 
   RAISERROR ("Cannot Delete etc...", 1, 2)
END

Mark

 

[Scott2S]

Mark,
Thanks again for all your input.

Unfortunately this trigger change didn't help. To make sure the problem is clear now (since I have been dragging this out sooo long): The triggers work great inside a ISQL window and inside access - the bottom line is they prevent the record from being deleted if relational records exits.

The problem is that when using access 95 or 97, I don't always get the error message raised from the trigger (some triggers raise it and others dont) and in the case of using access 97 - it even gives the user the ilusion that the deleted the record until they refresh the table.

There is some disconnect happening in Access with SQL. Access hands off this delete command and thinks that it was commited in SQL but it wasn't.

What should be my next step?

PS - I have converted this SQL 6.5 database to 7.0 and I am going to try and delete this same record to see if the trigger works in 7.0 properly with access.

 

[MarkSweetland]

After further review...

I checked in the Microsoft Knowledgebase and found a problem close to what you are experiencing. It discusses the &quot;Record is Deleted&quot; error and may be related to the way Access handles the ODBC connection to the linked Tables (I'm assuming you have a System DSN on the Client here..) The article below discusses the ODBC and Jet cursors. It recommends displaying read-only data and performing updates and deletes manually. The long way I'm sure, but I don't use ODBC with Access anymore if I can help it and use ADO.

Have you tried the delete record using ADO?

http://support.microsoft.com/support/kb/articles/Q172/3/39.asp

Mark