Write conflict error after adding trigger to table

[Guest]

Hi everyone,

I have inherited an Access adp that directly connects to SQL Server. My
knowledge is mostly at the SQL Server level and I have worked with older
versions of MDBs - Access 97 and previous. I created delete, insert, and
update triggers on the table an Access form pulls up through a stored
procedure that selects the recordset. When you change to a new record, it
runs the save (I added a check for Me.Dirty, but it still considers it dirty
even if the user didn't make any changes. There is a lookup feature on this
form. Every time I try to change records using the lookup feature (it does
"If Me.Dirty = True Then DoCmd.RunCommand acCmdSaveRecord"). It says the
record has been changed by another user since you stared editing it. etc,
etc. The trigger inserts a row into another database through a view in
that database. The user has access to the view and full rights to the table
with the trigger in it. What is the deal? If I use that user ID through a
linked table in Access, they can change the record. However, if I run the
stored proc in the database window instead of through the form, it still has
the same error, so it is not the fault of the form, but of using the stored
proc as a datasource. Can anyone clue me in? Is this an Access bug, and if
so, how do I get around it? This is very frustrating because the Access app
is preventing me from rolling these triggers to production where other apps
(.NET) that hit the triggers have no problems (but of course, they don't use
that stored proc).

Thanks,
MH

 

[Sylvain Lafontaine]

This error message is usually the result of a missing primary key or a
mixe-up with a timestamp column or even a bit field. By using the Profiler
on SQL-Server, you will see what's Access is attempting to do and make an
appropriate correction.

If you have created your view without the VIEW_METADATA hint, the name of
the backend tables are returned, not the name of the view.

If the problem persists, I think that you should create your view with
VIEW_METADATA and create an appropriate InsteadOf trigger to have this view
updatable. However, I never tried this personally with ADP.

 

[Guest]

Sylvain,

Thanks for the advice. It worked! The VIEW_METADATA, along with
checking if exists before inserting through the trigger fixed this issue.
If the where clause in the insert made it return nothing, it seemed to have
an issue with that, but only through Access. The .NET app didn't seem to
care one way or the other on the check or the VIEW_METADATA. I didn't need
to create a trigger on the view, thank goodness.

 

[Sylvain Lafontaine]

Where clause in view that return nothing after an insert has always been a
problem but, logically, you should use another logic for performing an
insert - like an alternate stored procedure - that must not be read back by
the client performing the insert. (Unless that I have misread what you mean
by that.)

 

[Guest]

The insert is performed on the view after an update is triggered on a table
that is unrelated to the view. The view simply sends a record change for
another app to pick up from another database. The problem I am having now
is that the UPDATE(<column_name>) returns true every time a save occurs in
Access, so my change record goes across even if a change in the data didn't
occur. If I did a join between the inserted and deleted tables and put in
the where clause that inserted.columnA != deleted.columnA, I will probably
run into that issue again when none of the data has changed, since it runs
an insert against the view when the resulting where clause ends up with no
records, therefore the insert should not actually be performed. The Access
form saves to the SQL Server database every time someone views a record.
This Access app is temporary until that app can be rewritten in .NET, but
the triggers are here to stay because they are there for a new application.
I would like to limit the changes that occur to the Acess app if possible.

 

[Sylvain Lafontaine]

If I understand correctly, you are telling that every time a record is
viewed inside the ADP form, ADP (or Access) make an update after that; even
if no editing did take place?

Looks to me as a bug or a design problem in the ADP form or VBA code
itself.

 

[Guest]

I would agree, but all the forms work that way and it would take a lot of
work to rewrite the Access side. The recordsource is a stored proc with a
select statement, so the update goes back through that and the
acCmdSaveRecord occurs every time they change records or leave the form. I
don't have the time to fix it on the Access side and it impacts a wider
customer base than the changes that the trigger is for. If I can somehow
recognize that on the SQL Server side, that would definitely be preferable.

 

[Sylvain Lafontaine]

Maybe by adding a new field to distinguish between ADP and other
applications?

 

[Guest]

All changes, regardless of the source of the change need to move over. The
solution I have adopted for this that is working on one of the tables is to
have an IF EXISTS(select.... where inserted.field1 != deleted.field1... etc.
Since I am doing this in the IF statement, it doesn't run across the
"inserting nothing into the view" problem. Thank you so much for all your
help! I really appreciate it!