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 said:
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 said:
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
..
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"));
:
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
..
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...
