Operation Must Be an Updateable Query

[supportsvc]

Hello,
This is frustrating

Situation:
1) Linked to SQL Tables
2) One table, AR_OpenInvoice will not work with the Update Query or Pass-through Query
3) AR_OpenInvoice has PrimaryKeys
4) All other tables in the SQL db update using the Update Query in MS Access
5) I don't believe it's a Permissions issue as I can run a basic Update SQL Query via SSMS

Wanting to use the Update Query in MS Access for some things, just makes it a little easier.

Any ideas? I can't find anything else other than
1) permissions (check)
2) pass-through (check) <-- this results in another error message: "The query cannot be used as a row source"
3) PrimaryKeys (check)

The SQL with the pass-through query:

UPDATE dbo_AR_OpenInvoice SET dbo_AR_OpenInvoice.PaymentsToday = 0
WHERE (((dbo_AR_OpenInvoice.PaymentsToday)<>0) AND ((dbo_AR_OpenInvoice.CustomerNo)="0031731") AND ((dbo_AR_OpenInvoice.InvoiceDate) Between #5/1/2018# And #5/22/2018#));

The SQL just Update query:

UPDATE dbo_AR_OpenInvoice SET dbo_AR_OpenInvoice.PaymentsToday = 0
WHERE (((dbo_AR_OpenInvoice.PaymentsToday)<>0) AND ((dbo_AR_OpenInvoice.CustomerNo)="0031731") AND ((dbo_AR_OpenInvoice.InvoiceDate) Between #5/1/2018# And #5/22/2018#));

 

[dhookom]

In order to update the linked table using a standard Access query, the linked table definition would need to understand which field(s) is the primary key field.

The P-T query would need to use the SQL Server syntax. I assume the actual table name is AR_OpenInvoice.

UPDATE AR_OpenInvoice SET AR_OpenInvoice.PaymentsToday = 0
WHERE AR_OpenInvoice.PaymentsToday<>0 AND 
AR_OpenInvoice.CustomerNo='0031731' AND 
AR_OpenInvoice.InvoiceDate Between '5/1/2018' And '5/22/2018'

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

This is how the SQL tables link in MS Access via ODBC connection

dbo_AR_OpenInvoice

In SQL it's dbo.AR_OpenInvoice

 

[dhookom]

Exactly so when you are using a pass-through that connects to the database, you don't need to include the dbo_. Also, the delimiter for both text and dates is the single quote.

Did you try the pass-through with the SQL as I suggested? What were the results?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

Sorry for the delay.
Interestingly it allows and update but not always without the pass-through query

This is the message received when using the pass-through

NOTE: there are records to update so the message does not make sense to me.

 

[dhookom]

There is a "returns records" property of the pass-through. This can be set to "No" for P-T queries that perform action type queries. The message you provided is just information. If this was a SELECT query, you want the Returns Records to be Yes.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016