Trigger makes update impossible

[Kipp Woodard]

I have an Access 2007 front-end, with a SQL Server 2008 back-end.

I want to update a timestamp on my table when a record is updated using the
Access front-end.

I have this trigger:

==BEGIN CODE =============================

ALTER TRIGGER [dbo].[t_TimeLog_u]
ON [dbo].[TimeLog]
after Update
AS
BEGIN
SET NOCOUNT ON;

Update TimeLog Set
OutLoginID = suser_sname(),
OutTimestamp = getdate()
From inserted
Where inserted.EventID = TimeLog.EventID
END

==END CODE =============================

I get this message when I update a record:

==BEGIN MESSAGE==================================
Write Conflict

This record has been changed by another user since you started editing it.
If you save the record, you wilol overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to make
changes.

Save Record (disabled, can't choose it), Copy to Clipboard, Drop Changes

==END MESSAGE==================================

The end result is I can't have the trigger and update the table too.

Is there a way around this?

 

[Kipp Woodard]

Sorry, it isn't the trigger. I have removed the trigger and I still
have the same issue. I can't delete a record either. I get:


The Microsoft Access database engine stopped the process because you
and another user are attempting to change the same data at the same
time. (Error 3197)

 

[Kipp Woodard]

It was apparently due to having a column defined as "bit". I changed
it to "int" and the issue went away.

Sorry, it isn't the trigger.  I have removed the trigger and I still
have the same issue.  I can't delete a record either.  I get:

The Microsoft Access database engine stopped the process because you
and another user are attempting to change the same data at the same
time. (Error 3197)

I have an Access 2007 front-end, with a SQL Server 2008 back-end.

I want to update a timestamp on my table when a record is updated usingthe
Access front-end.

I have this trigger:

==BEGIN CODE =============================

ALTER TRIGGER [dbo].[t_TimeLog_u]
   ON  [dbo].[TimeLog]
   after Update
AS
BEGIN
        SET NOCOUNT ON;

        Update TimeLog Set
                OutLoginID = suser_sname(),
                OutTimestamp = getdate()
                From inserted
        Where inserted.EventID = TimeLog.EventID
END

==END CODE =============================

I get this message when I update a record:

==BEGIN MESSAGE==================================
Write Conflict

This record has been changed by another user since you started editing it.  
If you save the record, you wilol overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to make
changes.

Save Record (disabled, can't choose it), Copy to Clipboard, Drop Changes

==END MESSAGE==================================

The end result is I can't have the trigger and update the table too.

Is there a way around this?- Hide quoted text -

- Show quoted text -

 

[Sylvain Lafontaine]

Nullable Bit columns (a Bit column that can be set to the Null value) are a
common source of problem. It's better to not have nullable Bit column and
set their default value to either 0 or 1.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


It was apparently due to having a column defined as "bit". I changed
it to "int" and the issue went away.

Sorry, it isn't the trigger. I have removed the trigger and I still
have the same issue. I can't delete a record either. I get:

The Microsoft Access database engine stopped the process because you
and another user are attempting to change the same data at the same
time. (Error 3197)

I have an Access 2007 front-end, with a SQL Server 2008 back-end.

I want to update a timestamp on my table when a record is updated using
the
Access front-end.

I have this trigger:

==BEGIN CODE =============================

ALTER TRIGGER [dbo].[t_TimeLog_u]
ON [dbo].[TimeLog]
after Update
AS
BEGIN
SET NOCOUNT ON;

Update TimeLog Set
OutLoginID = suser_sname(),
OutTimestamp = getdate()
From inserted
Where inserted.EventID = TimeLog.EventID
END

==END CODE =============================

I get this message when I update a record:

==BEGIN MESSAGE==================================
Write Conflict

This record has been changed by another user since you started editing
it.
If you save the record, you wilol overwrite the changes the other user
made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make
changes.

Save Record (disabled, can't choose it), Copy to Clipboard, Drop Changes

==END MESSAGE==================================

The end result is I can't have the trigger and update the table too.

Is there a way around this?- Hide quoted text -

- Show quoted text -

 

[Kipp Woodard]

That makes sense.

The SQL Server import wizard translates Access Yes/No columns as
nullable bits. That is the source of the issue, and so I expect this
happens to everyone that imports Access tables to SQL Server in this
way.

Nullable Bit columns (a Bit column that can be set to the Null value) area
common source of problem.  It's better to not have nullable Bit column and
set their default value to either 0 or 1.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


It was apparently due to having a column defined as "bit".  I changed
it to "int" and the issue went away.

Sorry, it isn't the trigger. I have removed the trigger and I still
have the same issue. I can't delete a record either. I get:

The Microsoft Access database engine stopped the process because you
and another user are attempting to change the same data at the same
time. (Error 3197)

I have an Access 2007 front-end, with a SQL Server 2008 back-end.
I want to update a timestamp on my table when a record is updated using
the
Access front-end.
I have this trigger:
==BEGIN CODE =============================
ALTER TRIGGER [dbo].[t_TimeLog_u]
ON [dbo].[TimeLog]
after Update
AS
BEGIN
SET NOCOUNT ON;
Update TimeLog Set
OutLoginID = suser_sname(),
OutTimestamp = getdate()
From inserted
Where inserted.EventID = TimeLog.EventID
END
==END CODE =============================
I get this message when I update a record:
==BEGIN MESSAGE==================================
Write Conflict
This record has been changed by another user since you started editing
it.
If you save the record, you wilol overwrite the changes the other user
made.
Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decideto
make
changes.
Save Record (disabled, can't choose it), Copy to Clipboard, Drop Changes
==END MESSAGE==================================
The end result is I can't have the trigger and update the table too.
Is there a way around this?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[maria prieto]

cant you just go to the website and updated?

 

[Äã]

ºÏºõ¹þ¹þ¹þ

I want to update a timestamp on my table when a record is updated using
the
Access front-end.

I have this trigger:

==BEGIN CODE =============================

ALTER TRIGGER [dbo].[t_TimeLog_u]
ON [dbo].[TimeLog]
after Update
AS
BEGIN
SET NOCOUNT ON;

Update TimeLog Set
OutLoginID = suser_sname(),
OutTimestamp = getdate()
From inserted
Where inserted.EventID = TimeLog.EventID
END

==END CODE =============================

I get this message when I update a record:

==BEGIN MESSAGE==================================
Write Conflict

This record has been changed by another user since you started editing it.
If you save the record, you wilol overwrite the changes the other user
made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make
changes.

Save Record (disabled, can't choose it), Copy to Clipboard, Drop Changes

==END MESSAGE==================================

The end result is I can't have the trigger and update the table too.

Is there a way around this?

 

[Paul Shapiro]

Try adding Set NoCount On as the first line of the trigger, after the Begin
statement. That stops SQL Server from returning the count of affected
records to Access, which Access sometimes interprets as an "extra" update.

ºÏºõ¹þ¹þ¹þ

I want to update a timestamp on my table when a record is updated using
the
Access front-end.

I have this trigger:

==BEGIN CODE =============================

ALTER TRIGGER [dbo].[t_TimeLog_u]
ON [dbo].[TimeLog]
after Update
AS
BEGIN
SET NOCOUNT ON;

Update TimeLog Set
OutLoginID = suser_sname(),
OutTimestamp = getdate()
From inserted
Where inserted.EventID = TimeLog.EventID
END

==END CODE =============================

I get this message when I update a record:

==BEGIN MESSAGE==================================
Write Conflict

This record has been changed by another user since you started editing it.
If you save the record, you wilol overwrite the changes the other user
made.

Copying the changes to the clipboard will let you look at the values
the
other user entered, and then paste your changes back in if you decide
to
make
changes.

Save Record (disabled, can't choose it), Copy to Clipboard, Drop
Changes

==END MESSAGE==================================

The end result is I can't have the trigger and update the table too.