Update Query spikes database

[AlfFromMelmac]

I have an Access database that pulls information from a main SQL server via append query and builds a table I use as a source for tracking purposes. The master key is the visit number since that is unique, but the problem is that the append query only adds new visits and the main database may change schedule date or time, which is not updated in my Access table.

I have made an update query based on the append query (copied it then tweaked it so it went to the same database, had the same relations, etc), set it to pull the same information and replace schedule date and time (on all records, since that should update the date and time on all records and catch any changes at all) in the table built from the append query. The problem is that I don't know if it works because as soon as I run it, it thrashes the main SQL database so hard that the site can no longer use the application and Access pulls the info very, very slowly. I've had to end task after 15 minutes each time, before anything was actually done to my table. The append query only takes a few seconds and doesn't affect the database performance at all. I know I have to be doing something wrong but not sure what...

Thanks for any help!

 

[AlfFromMelmac]

Also, I am getting pop-ups when I try and run the query asking for "enter parameter value" Not sure why or what to do with those...

 

[AlfFromMelmac]

Update: I finally got it to run and stop prompting for the parameter value by adding the destination table as a table in the query setup (rather than just entering it directly). Now it appears to run without thrashing the parent database it's pulling from, though it does take a couple of minutes. When it's done, it says it did not update the records due to lock violations, but it is not updating the master key, so I'm confused.

 

[JerryKlmns]

Alf

You could do an Append & Update query with one SQL statement

UPDATE LocalTable As L RIGHT JOIN SQLTable As S On
         L.VisitNo = S.VisitNo
SET L.VisitNo = S.VisitNo,
    L.Field1 = S.Field1,
    L.Field2 = S.Field2, 
....
    L.Fieldn = S.Fieldn;

 

[AlfFromMelmac]

Thanks a million, it's starting to take shape and make sense at least at the SQL level to me. Can you show me how the above code would change factoring in two different SQLTabels (this is a combination of info pulled from two different tables)? I have a condtional parameter so it only pulls certain records, but I think I have that bit down. It's the rest that I'm getting lost in.

 

[JerryKlmns]

>change factoring in two different SQLTabels (this is a combination of info pulled from two different tables)?

Sorry I don't unnderstand that question.Could you explain it a bit more?

 

[lespaul]

you may fine the SQL Join article below helpful....

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[AlfFromMelmac]

JerryKlmns :
>>change factoring in two different SQLTabels (this is a combination of info pulled from two different tables)?

>Sorry I don't unnderstand that question.Could you explain it a bit more?

The original code has the information pulling from one external SQLtables, but I am pulling from two different tables to build my local table.

I'll check out the link too from lespaul (play guitar??), I'm just VERY much a n00b. Basically, the only reason I'm designing databases is I was the only one who said I'd give it a go... and I'm soooo close!

Thanks!

 

[AlfFromMelmac]

Okay, I'm starting to think the problem is elsewhere in Access and not witht he query. The query seems to be working as it should. Turns out I need both SQLtables to build the local table, but only need to hit one SQLtable to build the update query. The query goes to dbo_ExamRecord as it's supposed to and only pulls the records that have the M resource, using the primary key AccNumber to keep it all together. I've further tweaked the query (in Access, so don't laugh at the SQL end) so that it gathers the information I need, but instead of replacing all entries for ScheduleDate and ScheduleTime, it only replaces those that have changed. In this step, I've been able to confirm that the query is returning the information I need and essentially working:

UPDATE (dbo_ExamRecord INNER JOIN dbo_Registration ON dbo_ExamRecord.PatientID = dbo_Registration.PatientID) 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"));

The query returns the information now in a matter of seconds and does not thrash the performance of the external database, so that problem is fixed. HOWEVER, when the query goes to update the local table Tracking, it says it can't update the ScheduleDate and ScheduleTime values that have changed (...didn't update 92 records due to lock violations.). I'm the only user and the local table is not shared, so I'm wondering where the lock violation is coming in. It feels SOOOOO close! Any ideas?

 

[AlfFromMelmac]

As an update, I cleaned up the query hoping that was the problem - modified it by removing the SQLtable that was not needed and ended up with this... which acts the same way.

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"));

 

[AlfFromMelmac]

I tried the script suggested by Jerry, but Access doesn't like it. I'm guessing it would work outside Access.

 

[JerryKlmns]

Is too spicy or salty?