>> sql trigger

[Jonathan]

Hi using Access 2003 and SQL 2005.
In sql a record update will run a trigger. However in an Access UI when a
user updates a field (of an existing record) with the trigger active and the
record saves, the value is cleared from the field. When the trigger is
disabled in sql, the value is retained/saved.

The question is therefore, should sql triggers work for an Access UI? If
'yes', any ideas/suggestions for me to investigate would be appreciated

Many thanks,
Jonathan

 

[Tom van Stiphout]

On Tue, 14 Apr 2009 20:26:01 -0700, Jonathan

I'm not sure I'm following 100%.
SQL Server (I am assuming that's your back-end; "SQL" is a language)
has a Triggers feature, including an Update Trigger feature, and they
fire regardless of which way the record was updated. There is no code
in SQL Server that says "if initiated from MsAccess, do this, else do
that".

Debugging SQL Server processes works much better with its Profiler
application running.

-Tom.
Microsoft Access MVP

 

[David H]

What exactly does the trigger do?

Also, you can add/edit data directly in SQLServer by opening it from within
Access via the Tables tab. Does the same thing happen when you update a
record there?

 

[Jonathan]

On Tue, 14 Apr 2009 20:26:01 -0700, Jonathan

I'm not sure I'm following 100%.
SQL Server (I am assuming that's your back-end; "SQL" is a language)
has a Triggers feature, including an Update Trigger feature, and they
fire regardless of which way the record was updated. There is no code
in SQL Server that says "if initiated from MsAccess, do this, else do
that".

Debugging SQL Server processes works much better with its Profiler
application running.

-Tom.
Microsoft Access MVP

Interestingly, the trigger is not registering in the profiler, even though
it is doing its thing. I cannot see anything in the configuration of the
profiler that is explicitly for triggers.

Many thanks,
Jonathan

 

[Jonathan]

What exactly does the trigger do?

Also, you can add/edit data directly in SQLServer by opening it from within
Access via the Tables tab. Does the same thing happen when you update a
record there?

Good suggestion to test directly in the table. It looks like the trigger is
working (just realised that a trigger appears as a stored procedure in the
profiler), so this would suggest that the problem has something to do with
the form; which is good to know as this narrows the field somewhat...

Many thanks,
Jonathan

 

[Armen Stein]

Good suggestion to test directly in the table. It looks like the trigger is
working (just realised that a trigger appears as a stored procedure in the
profiler), so this would suggest that the problem has something to do with
the form; which is good to know as this narrows the field somewhat...

You can also make updates to the SQL Server table by opening it in
Management Studio, not using Access at all. Although as David pointed
out, the trigger will fire regardless of where the update is done.

If the trigger alters a value in the same record being updated, then
your Access form would reflect that change after it is requeried or
refreshed.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jonathan]

Thanks, I think I have enough to go on... for now...

Jonathan

 

[David H]

Try doing an explict Refresh via the Forms AfterInsert event.

 

[Jonathan]

Hi David,

The problem seems to be related to the fact that in Access I have a form
bound to a table. The trigger makes some changes to related records and then
tries to update the status of the current record. But can't because Access
has it locked. The solution is to have an unbound form.

Many thanks,
Jonathan

 

[Armen Stein]

The problem seems to be related to the fact that in Access I have a form
bound to a table. The trigger makes some changes to related records and then
tries to update the status of the current record. But can't because Access
has it locked. The solution is to have an unbound form.

If you use Optimistic locking ("no locks") in Access, then a
background process *should* be able to update the record after Access
has committed the change. But then an attempted update to that same
record in Access may report that the "record has been changed by
another user".

Either way (fixing the bound form or switching to an unbound form)
will take some effort. At least you know what's causing the problem
now.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com