Solved

Updat query error due to lock violations

Posted on 2009-05-20
9
638 Views
Last Modified: 2012-05-07
i have a query that should update TABLE District 3 total list1 with data from
QUERY Calculate next call date.

The query will run, then give me an error that none of the updates were made due to lock violations. Can anyone help me with this?

Thanks!! Beth
UPDATE [District 3 total list1] INNER JOIN [Calculate next call date] ON [District 3 total list1].[Facility ID] = [Calculate next call date].[Facility ID] SET [District 3 total list1].NEXTCALL = [Calculate next call date].[Next Call Date], [District 3 total list1].[CC Band] = [Calculate next call date].[New CC Band];

Open in new window

0
Comment
Question by:ba_trainer
  • 5
  • 4
9 Comments
 

Author Comment

by:ba_trainer
ID: 24432299
The Calculate next call date uses fields from District 3 total list1 for its calculations. Could this be the problem? How would I avoid the table locking?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24432322
check if you have the same Data Type for the fields updated

--------------------vvvvvvv------------------------------vvvvvvvvvv
District 3 total list1].NEXTCALL = [Calculate next call date].[Next Call Date],
[District 3 total list1].[CC Band] = [Calculate next call date].[New CC Band]
0
 

Author Comment

by:ba_trainer
ID: 24432429
I did check data types first, they seem fine.

The error is" Microsoft Office didn't update 0 file(s) due to a type conversion failure, 0 record(s) due to key violations, 4517 record(s) due to lock volations, and 0 record(s) due to validation rule violations".

Does that help?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24432461
is [Calculate next call date] a query?
post the SQL here
0
 

Author Comment

by:ba_trainer
ID: 24432743
Calculate next call date query:
SELECT [District 3 total list1].[Facility ID], [LASTCALLED]+(367/[CC Band]) AS [New Call Date]
FROM [District 3 total list1];

Update last call in the current call cycle query:
UPDATE [District 3 total list1] INNER JOIN [Calculate next call date] ON [District 3 total list1].[Facility ID] = [Calculate next call date].[Facility ID] SET [District 3 total list1].NEXTCALL = [Calculate next call date].[New Call Date];
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24433332
normally, you don't store calculated values to the table, where you can always extract the values using a query.

0
 

Author Comment

by:ba_trainer
ID: 24433461
I need to update the table with values that are calculated in the query. I don't know any other way to do it. Can you suggest something?

I need to get the values FROM the table, do the calculations, then UPDATE the table with the new calculations.?

Thanks!!!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24433512
try this update query

UPDATE [District 3 total list1]
SET NEXTCALL = [LASTCALLED]+(367/[CC Band])
0
 

Author Closing Comment

by:ba_trainer
ID: 31583535
Wonderful!! Thanks a million!!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question