I have read some of your postings related to updating/creating invoices, however my situation seems a little different. All I want to do is update one of the custom fields in the invoice table. This is a static text field but I still get an error that says it can't update due to lock violations. How do you update fields in these types of tables?

or

Update Customer Set CustomFieldContract='1234'
where FullName='Data Access Worldwide'

or

UPDATE Invoice SET CustomFieldContract = '1234-5678' where TxnId='F0A-1197683921'

The syntax of the query is very similar to the syntax you have below

UPDATE Customer INNER JOIN Invoice ON Customer.FullName=Invoice.CustomerRefFullName SET Invoice.CustomFieldAccountManager1 = [customer].[CustomFieldAccountManager1]
WHERE (((Invoice.IsPaid)=No));

When I run it it returns the message that says 1064 records will be updated, do you want to update. I click yes and get the following message:

Microsoft Access didn't update 0 field(s) due to a type conversion failure, 0 record(s) due to key violations, 1064 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

Any thoughts?

Thanks

This can't be done in a single SQL statement. It will require a program loop that reads each invoice line, checks if it's PAID or not and if not, looks up the customer table, and builds the update statement for the CustomFieldAccountManager1 that's executed. There are currently no plans to support INNER JOINs within UPDATES. We support INNER JOINs within SELECTs:

Select * from Invoice INNER JOIN Customer ON Invoice.CustomerRefListID = Customer.ListID
WHERE (((Invoice.CustomFieldContract) Is Null))

or:

SELECT * FROM {OJ Invoice INNER JOIN Customer ON
(Invoice.CustomerRefListID = Customer.ListID)}
WHERE (((Invoice.CustomFieldContract) Is Null))

or even the more traditional:

Select * from Invoice, Customer
Where Invoice.CustomerRefListID = Customer.ListID
and (((Invoice.CustomFieldContract) Is Null))

QODBC doesn't support the use of INNER JOINS or subqueries within updates.

Then I don't get it. How would I do any kind of update. Are you saying that I would have to build a process that would dynamically create the update script and code in the values? That seems extreme when you have over a 1,000 records to update.

What if I was running this on SQL Server?