Update Query and record lock violations - microsoft.public.access.queries

I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modify date records that have changed in the remote table to the local table (they change occasionally in the sqltable) via update query. I have the query set to pull date and time from the sqltable (there is a common key to both tables that acts as the master key in the local table) and update the date and time in the local table where the date and time are not equal in both tables. It seems to work - it returns exactly the number of records that have had date and time changed. However, it says it fails to update them due to record lock violations. I'm stumped...

POST your SQL statement. (Menu View: SQL) Also, make sure your local table is not being used elsewhere. (For instance, do you have it open in design view?) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Alf" <Alf@discussions.microsoft.com> wrote in message news:58BCECEB-15F8-4649-8180-3427B45E3398@microsoft.com... >I have a local table constructed from two remote sqltables. I add records > successfully via an append query in Access 2003. I am attempting to > modify > date records that have changed in the remote table to the local table > (they > change occasionally in the sqltable) via update query. I have the query > set > to pull date and time from the sqltable (there is a common key to both > tables > that acts as the master key in the local table) and update the date and > time > in the local table where the date and time are not equal in both tables. > It > seems to work - it returns exactly the number of records that have had > date > and time changed. However, it says it fails to update them due to record > lock violations. I'm stumped...

Here's the code Access 2003 wrote in SQL for my query. Also, the local table is not open. I even rebooted and opened from scratch and ran only the update query, same results. I figure based on how many records were found, I'm very close. Thanks so much for having a look-see! UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = Tracking.AccNumber SET dbo_ExamRecord.ScheduleDate = Tracking.ScheduleDate, dbo_ExamRecord.ScheduleTime = Tracking.ScheduleTime WHERE (((dbo_ExamRecord.ScheduleDate)<>[Tracking].[ScheduleDate]) AND ((dbo_ExamRecord.ScheduleTime)<>[Tracking].[ScheduleTime]) AND ((dbo_ExamRecord.Resource)="M")); "John Spencer" wrote: > POST your SQL statement. (Menu View: SQL) > > Also, make sure your local table is not being used elsewhere. (For > instance, do you have it open in design view?) > > -- > John Spencer > Access MVP 2002-2005, 2007 > Center for Health Program Development and Management > University of Maryland Baltimore County > .. > > "Alf" <Alf@discussions.microsoft.com> wrote in message > news:58BCECEB-15F8-4649-8180-3427B45E3398@microsoft.com... > >I have a local table constructed from two remote sqltables. I add records > > successfully via an append query in Access 2003. I am attempting to > > modify > > date records that have changed in the remote table to the local table > > (they > > change occasionally in the sqltable) via update query. I have the query > > set > > to pull date and time from the sqltable (there is a common key to both > > tables > > that acts as the master key in the local table) and update the date and > > time > > in the local table where the date and time are not equal in both tables. > > It > > seems to work - it returns exactly the number of records that have had > > date > > and time changed. However, it says it fails to update them due to record > > lock violations. I'm stumped... > > >

It appears that you may be trying to update a linked SQL table. If that is the case, then you probably need to have a field of type TIMESTAMP in the SQL table in order to update the table successfully. Is there such a field? Since you are trying to update the SQL Server table, I would guess that the dbo_ExamRecord table is the one causing the error message. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Alf" <Alf@discussions.microsoft.com> wrote in message news:B1289952-8A3E-46C4-8060-6768A4FFA03C@microsoft.com... > Here's the code Access 2003 wrote in SQL for my query. Also, the local > table > is not open. I even rebooted and opened from scratch and ran only the > update > query, same results. I figure based on how many records were found, I'm > very > close. Thanks so much for having a look-see! > > UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = > Tracking.AccNumber SET dbo_ExamRecord.ScheduleDate = > Tracking.ScheduleDate, > dbo_ExamRecord.ScheduleTime = Tracking.ScheduleTime > WHERE (((dbo_ExamRecord.ScheduleDate)<>[Tracking].[ScheduleDate]) AND > ((dbo_ExamRecord.ScheduleTime)<>[Tracking].[ScheduleTime]) AND > ((dbo_ExamRecord.Resource)="M")); > > > "John Spencer" wrote: > >> POST your SQL statement. (Menu View: SQL) >> >> Also, make sure your local table is not being used elsewhere. (For >> instance, do you have it open in design view?) >> >> -- >> John Spencer >> Access MVP 2002-2005, 2007 >> Center for Health Program Development and Management >> University of Maryland Baltimore County >> .. >> >> "Alf" <Alf@discussions.microsoft.com> wrote in message >> news:58BCECEB-15F8-4649-8180-3427B45E3398@microsoft.com... >> >I have a local table constructed from two remote sqltables. I add >> >records >> > successfully via an append query in Access 2003. I am attempting to >> > modify >> > date records that have changed in the remote table to the local table >> > (they >> > change occasionally in the sqltable) via update query. I have the >> > query >> > set >> > to pull date and time from the sqltable (there is a common key to both >> > tables >> > that acts as the master key in the local table) and update the date and >> > time >> > in the local table where the date and time are not equal in both >> > tables. >> > It >> > seems to work - it returns exactly the number of records that have had >> > date >> > and time changed. However, it says it fails to update them due to >> > record >> > lock violations. I'm stumped... >> >> >>

OH! That would explain it... however, I am not trying to update the SQL table, I'm trying to update the local table Tracking! I don't know why Access tried to do it that way (though I'm sure it's me somehow). The table to update on the design view is definitely Tracking. Do I just change dbo_Update to Tracking after the UPDATE statement, or do I need to reverse all those in the SQL view? THANK YOU! "John Spencer" wrote: > It appears that you may be trying to update a linked SQL table. If that is > the case, then you probably need to have a field of type TIMESTAMP in the > SQL table in order to update the table successfully. Is there such a field? > > Since you are trying to update the SQL Server table, I would guess that the > dbo_ExamRecord table is the one causing the error message. > > -- > John Spencer > Access MVP 2002-2005, 2007 > Center for Health Program Development and Management > University of Maryland Baltimore County > .. > > "Alf" <Alf@discussions.microsoft.com> wrote in message > news:B1289952-8A3E-46C4-8060-6768A4FFA03C@microsoft.com... > > Here's the code Access 2003 wrote in SQL for my query. Also, the local > > table > > is not open. I even rebooted and opened from scratch and ran only the > > update > > query, same results. I figure based on how many records were found, I'm > > very > > close. Thanks so much for having a look-see! > > > > UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = > > Tracking.AccNumber SET dbo_ExamRecord.ScheduleDate = > > Tracking.ScheduleDate, > > dbo_ExamRecord.ScheduleTime = Tracking.ScheduleTime > > WHERE (((dbo_ExamRecord.ScheduleDate)<>[Tracking].[ScheduleDate]) AND > > ((dbo_ExamRecord.ScheduleTime)<>[Tracking].[ScheduleTime]) AND > > ((dbo_ExamRecord.Resource)="M")); > > > > > > "John Spencer" wrote: > > > >> POST your SQL statement. (Menu View: SQL) > >> > >> Also, make sure your local table is not being used elsewhere. (For > >> instance, do you have it open in design view?) > >> > >> -- > >> John Spencer > >> Access MVP 2002-2005, 2007 > >> Center for Health Program Development and Management > >> University of Maryland Baltimore County > >> .. > >> > >> "Alf" <Alf@discussions.microsoft.com> wrote in message > >> news:58BCECEB-15F8-4649-8180-3427B45E3398@microsoft.com... > >> >I have a local table constructed from two remote sqltables. I add > >> >records > >> > successfully via an append query in Access 2003. I am attempting to > >> > modify > >> > date records that have changed in the remote table to the local table > >> > (they > >> > change occasionally in the sqltable) via update query. I have the > >> > query > >> > set > >> > to pull date and time from the sqltable (there is a common key to both > >> > tables > >> > that acts as the master key in the local table) and update the date and > >> > time > >> > in the local table where the date and time are not equal in both > >> > tables. > >> > It > >> > seems to work - it returns exactly the number of records that have had > >> > date > >> > and time changed. However, it says it fails to update them due to > >> > record > >> > lock violations. I'm stumped... > >> > >> > >> > > >

UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = Tracking.AccNumber SET Tracking.ScheduleDate=dbo_ExamRecord.ScheduleDate , Tracking.ScheduleTime=dbo_ExamRecord.ScheduleTime WHERE dbo_ExamRecord.ScheduleDate<>[Tracking].[ScheduleDate] AND dbo_ExamRecord.ScheduleTime<>[Tracking].[ScheduleTime] AND dbo_ExamRecord.Resource="M" One problem with the above is that if fields in either table are null you won't get updates. Also, you won't update if only the time or date is changed. You can change the criteria to something more complex to handle all those situations. UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = Tracking.AccNumber SET Tracking.ScheduleDate=dbo_ExamRecord.ScheduleDate , Tracking.ScheduleTime=dbo_ExamRecord.ScheduleTime WHERE (dbo_ExamRecord.ScheduleDate<>[Tracking].[ScheduleDate] OR dbo_ExamRecord.ScheduleDate is Null and [Tracking].[ScheduleDate] is Not Null OR dbo_ExamRecord.ScheduleDate is Not Null and [Tracking].[ScheduleDate] Is Null OR dbo_ExamRecord.ScheduleTime<>[Tracking].[ScheduleTime] OR dbo_ExamRecord.ScheduleTimeis Null and [Tracking].[ScheduleTime] is Not Null OR dbo_ExamRecord.ScheduleTimeis Not Null and [Tracking].[ScheduleTime] Is Null) AND dbo_ExamRecord.Resource="M" You can simplify that by using NZ on both sides of the comparisons although that will slow things down. WHERE (Nz(dbo_ExamRecord.ScheduleDate,#1/1/1899#)<>[NZ(Tracking].[ScheduleDate],#1/1/1899#) OR Nz(dbo_ExamRecord.ScheduleTime,#00:00:00#)<>Nz[(Tracking].[ScheduleTime],#00:00:00#) ) AND dbo_ExamRecord.Resource="M" -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Alf" <Alf@discussions.microsoft.com> wrote in message news:D00E86D1-58A5-42F2-8BD8-359535BA5F71@microsoft.com... > OH! That would explain it... however, I am not trying to update the SQL > table, I'm trying to update the local table Tracking! I don't know why > Access tried to do it that way (though I'm sure it's me somehow). The > table > to update on the design view is definitely Tracking. Do I just change > dbo_Update to Tracking after the UPDATE statement, or do I need to reverse > all those in the SQL view? THANK YOU! > > "John Spencer" wrote: > >> It appears that you may be trying to update a linked SQL table. If that >> is >> the case, then you probably need to have a field of type TIMESTAMP in the >> SQL table in order to update the table successfully. Is there such a >> field? >> >> Since you are trying to update the SQL Server table, I would guess that >> the >> dbo_ExamRecord table is the one causing the error message. >> >> -- >> John Spencer >> Access MVP 2002-2005, 2007 >> Center for Health Program Development and Management >> University of Maryland Baltimore County >> .. >> >> "Alf" <Alf@discussions.microsoft.com> wrote in message >> news:B1289952-8A3E-46C4-8060-6768A4FFA03C@microsoft.com... >> > Here's the code Access 2003 wrote in SQL for my query. Also, the local >> > table >> > is not open. I even rebooted and opened from scratch and ran only the >> > update >> > query, same results. I figure based on how many records were found, >> > I'm >> > very >> > close. Thanks so much for having a look-see! >> > >> > UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = >> > Tracking.AccNumber SET dbo_ExamRecord.ScheduleDate = >> > Tracking.ScheduleDate, >> > dbo_ExamRecord.ScheduleTime = Tracking.ScheduleTime >> > WHERE (((dbo_ExamRecord.ScheduleDate)<>[Tracking].[ScheduleDate]) AND >> > ((dbo_ExamRecord.ScheduleTime)<>[Tracking].[ScheduleTime]) AND >> > ((dbo_ExamRecord.Resource)="M")); >> > >> > >> > "John Spencer" wrote: >> > >> >> POST your SQL statement. (Menu View: SQL) >> >> >> >> Also, make sure your local table is not being used elsewhere. (For >> >> instance, do you have it open in design view?) >> >> >> >> -- >> >> John Spencer >> >> Access MVP 2002-2005, 2007 >> >> Center for Health Program Development and Management >> >> University of Maryland Baltimore County >> >> .. >> >> >> >> "Alf" <Alf@discussions.microsoft.com> wrote in message >> >> news:58BCECEB-15F8-4649-8180-3427B45E3398@microsoft.com... >> >> >I have a local table constructed from two remote sqltables. I add >> >> >records >> >> > successfully via an append query in Access 2003. I am attempting to >> >> > modify >> >> > date records that have changed in the remote table to the local >> >> > table >> >> > (they >> >> > change occasionally in the sqltable) via update query. I have the >> >> > query >> >> > set >> >> > to pull date and time from the sqltable (there is a common key to >> >> > both >> >> > tables >> >> > that acts as the master key in the local table) and update the date >> >> > and >> >> > time >> >> > in the local table where the date and time are not equal in both >> >> > tables. >> >> > It >> >> > seems to work - it returns exactly the number of records that have >> >> > had >> >> > date >> >> > and time changed. However, it says it fails to update them due to >> >> > record >> >> > lock violations. I'm stumped... >> >> >> >> >> >> >> >> >>

Thanks! I was able to make that one work!! "John Spencer" wrote: > UPDATE dbo_ExamRecord INNER JOIN Tracking > ON dbo_ExamRecord.AccNumber = Tracking.AccNumber > SET Tracking.ScheduleDate=dbo_ExamRecord.ScheduleDate , > Tracking.ScheduleTime=dbo_ExamRecord.ScheduleTime > WHERE dbo_ExamRecord.ScheduleDate<>[Tracking].[ScheduleDate] AND > dbo_ExamRecord.ScheduleTime<>[Tracking].[ScheduleTime] AND > dbo_ExamRecord.Resource="M" > > One problem with the above is that if fields in either table are null you > won't get updates. Also, you won't update if only the time or date is > changed. > You can change the criteria to something more complex to handle all those > situations. > > UPDATE dbo_ExamRecord INNER JOIN Tracking > ON dbo_ExamRecord.AccNumber = Tracking.AccNumber > SET Tracking.ScheduleDate=dbo_ExamRecord.ScheduleDate , > Tracking.ScheduleTime=dbo_ExamRecord.ScheduleTime > > WHERE (dbo_ExamRecord.ScheduleDate<>[Tracking].[ScheduleDate] > OR dbo_ExamRecord.ScheduleDate is Null and [Tracking].[ScheduleDate] is Not > Null > OR dbo_ExamRecord.ScheduleDate is Not Null and [Tracking].[ScheduleDate] Is > Null > OR dbo_ExamRecord.ScheduleTime<>[Tracking].[ScheduleTime] > OR dbo_ExamRecord.ScheduleTimeis Null and [Tracking].[ScheduleTime] is Not > Null > OR dbo_ExamRecord.ScheduleTimeis Not Null and [Tracking].[ScheduleTime] Is > Null) > AND dbo_ExamRecord.Resource="M" > > You can simplify that by using NZ on both sides of the comparisons although > that will slow things down. > > WHERE > (Nz(dbo_ExamRecord.ScheduleDate,#1/1/1899#)<>[NZ(Tracking].[ScheduleDate],#1/1/1899#) > OR > Nz(dbo_ExamRecord.ScheduleTime,#00:00:00#)<>Nz[(Tracking].[ScheduleTime],#00:00:00#) > ) > AND dbo_ExamRecord.Resource="M" > > -- > John Spencer > Access MVP 2002-2005, 2007 > Center for Health Program Development and Management > University of Maryland Baltimore County > .. > > "Alf" <Alf@discussions.microsoft.com> wrote in message > news:D00E86D1-58A5-42F2-8BD8-359535BA5F71@microsoft.com... > > OH! That would explain it... however, I am not trying to update the SQL > > table, I'm trying to update the local table Tracking! I don't know why > > Access tried to do it that way (though I'm sure it's me somehow). The > > table > > to update on the design view is definitely Tracking. Do I just change > > dbo_Update to Tracking after the UPDATE statement, or do I need to reverse > > all those in the SQL view? THANK YOU! > > > > "John Spencer" wrote: > > > >> It appears that you may be trying to update a linked SQL table. If that > >> is > >> the case, then you probably need to have a field of type TIMESTAMP in the > >> SQL table in order to update the table successfully. Is there such a > >> field? > >> > >> Since you are trying to update the SQL Server table, I would guess that > >> the > >> dbo_ExamRecord table is the one causing the error message. > >> > >> -- > >> John Spencer > >> Access MVP 2002-2005, 2007 > >> Center for Health Program Development and Management > >> University of Maryland Baltimore County > >> .. > >> > >> "Alf" <Alf@discussions.microsoft.com> wrote in message > >> news:B1289952-8A3E-46C4-8060-6768A4FFA03C@microsoft.com... > >> > Here's the code Access 2003 wrote in SQL for my query. Also, the local > >> > table > >> > is not open. I even rebooted and opened from scratch and ran only the > >> > update > >> > query, same results. I figure based on how many records were found, > >> > I'm > >> > very > >> > close. Thanks so much for having a look-see! > >> > > >> > UPDATE dbo_ExamRecord INNER JOIN Tracking ON dbo_ExamRecord.AccNumber = > >> > Tracking.AccNumber SET dbo_ExamRecord.ScheduleDate = > >> > Tracking.ScheduleDate, > >> > dbo_ExamRecord.ScheduleTime = Tracking.ScheduleTime > >> > WHERE (((dbo_ExamRecord.ScheduleDate)<>[Tracking].[ScheduleDate]) AND > >> > ((dbo_ExamRecord.ScheduleTime)<>[Tracking].[ScheduleTime]) AND > >> > ((dbo_ExamRecord.Resource)="M")); > >> > > >> > > >> > "John Spencer" wrote: > >> > > >> >> POST your SQL statement. (Menu View: SQL) > >> >> > >> >> Also, make sure your local table is not being used elsewhere. (For > >> >> instance, do you have it open in design view?) > >> >> > >> >> -- > >> >> John Spencer > >> >> Access MVP 2002-2005, 2007 > >> >> Center for Health Program Development and Management > >> >> University of Maryland Baltimore County > >> >> .. > >> >> > >> >> "Alf" <Alf@discussions.microsoft.com> wrote in message > >> >> news:58BCECEB-15F8-4649-8180-3427B45E3398@microsoft.com... > >> >> >I have a local table constructed from two remote sqltables. I add > >> >> >records > >> >> > successfully via an append query in Access 2003. I am attempting to > >> >> > modify > >> >> > date records that have changed in the remote table to the local > >> >> > table > >> >> > (they > >> >> > change occasionally in the sqltable) via update query. I have the > >> >> > query > >> >> > set > >> >> > to pull date and time from the sqltable (there is a common key to > >> >> > both > >> >> > tables > >> >> > that acts as the master key in the local table) and update the date > >> >> > and > >> >> > time > >> >> > in the local table where the date and time are not equal in both > >> >> > tables. > >> >> > It > >> >> > seems to work - it returns exactly the number of records that have > >> >> > had > >> >> > date > >> >> > and time changed. However, it says it fails to update them due to > >> >> > record > >> >> > lock violations. I'm stumped... > >> >> > >> >> > >> >> > >> > >> > >> > > >

Similiar Articles:

Update Query and record lock violations - microsoft.public.access ...
I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modif...

Update and Append query - microsoft.public.access.queries ...
... and Append query - microsoft.public.access.queries ... append query for dates and hour - microsoft.public.access.queries ... Update Query and record lock violations ...

Update-DateField to 2nd Thursday - microsoft.public.access.queries ...
Action Query (Append) didn't add records due to key violations ... I set the default value in sqlserver for the date field to ... Update Query and record lock violations ...

Pass-Through Query as Record Source - microsoft.public.access ...
Hi! This might be an innocent question but I have to know. Below are the 2 questions: 1. If you are using a pass-through query as the record source...

append query for dates and hour - microsoft.public.access.queries ...
Update and Append query - microsoft.public.access.queries ... append query for dates and hour - microsoft.public.access.queries ... Update Query and record lock violations ...

Getting Could Not Lock File error message - microsoft.public ...
I have a single user who is getting 'Could Not Lock ... who is getting 'Could Not Lock ... Getting an update query to ... Could not synchronise record error. - microsoft.public ...

Action Query (Append) didn't add records due to key violations ...
Access front-end and SQL Server back-end table lock - microsoft ... Action Query (Append) didn't add records due to key violations ... Hi all, I am using MS Access 2007 as ...

SQL Append Query - microsoft.public.access.queries
Now, I want to add code to append any records found in the update table ... Action Query (Append) didn't add records due to key violations ... Action Query (Append) didn't ...

"Could not update; currently locked." - microsoft.public ...
... to run the query/report without locking ... problem of record locking.I ... cause could not update again I must say; ther is a difference when other user lock the record ...

slow append query from a pass through query - microsoft.public ...
... Interbase database When I try to update the Access 2007 table using update query ... Action Query (Append) didn't add records due to key violations ... slow append query from ...

Update Query and record lock violations DataBase
I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modify d

Update Query Errors with Lock violation - how to troubleshoot ...
Update Query Errors with Lock violation - how to ... would only update records when the contents weren't already the desired value. Search for [msaccess mysql lock violation ...

Microsoft Access tips: Why can't I append some records?
Why can't I append some records? When you execute an append query, you may see a dialog giving ... Lock violations. Lock violations occur when the data you are trying to ...

Update query stopped by lock violations on MySQL table - Microsoft ...
... to update any of the records and I receive the following error: "Microsoft Office Access didn't update . . . xxx record(s) due to lock violations . . . "The same query ...

7/23/2012 8:23:53 AM