Unable to use update query with data sourced from a linked table? (1 Viewer)

[peskywinnets]

I have a very simple requirement..

I have a linked table & a local table ...they are joined.

I want to take some data from the linked table & update the local table with it, but I get this message...

"Operation Must use an updateable query"

Here's the SQL....

(the linked table is the table called QBSalesReceipt)

UPDATE QBSalesReceipt INNER JOIN Sales ON QBSalesReceipt.doc_num_h = Sales.doc_num_h SET Sales.ExchRate = [QBSalesReceipt]![exchange_rate_h];

What bit of knowledge/nous am I missing?

 

[arnelgp]

i think you have them reversed:

UPDATE Sales INNER JOIN QBSalesReceipt ON Sales.doc_num_h = QBSalesReceipt.doc_num_h SET Sales.ExchRate = [QBSalesReceipt]![exchange_rate_h];

 

[peskywinnets]

Thanks.... just tried, but I think my problem runs deeper.

I had a large database which I wanted to reduce. So I created a new blank database & imported everything into the new database from the large database.

It all worked, except I'm now getting this "Operation Must use an updateable query" when I run (previously working) queries in the new database (i.e. I wasn't getting this error not getting it on the old database).

You SQL works in the old database.

So clearly something is awry with the new database :-(

 

[arnelgp]

compact and repair maybe.
relink the tables.
remake the query, etc.

 

[jdraw]

See if Allen Browne's Read Only article helps identify the issue.

 

[peskywinnets]

Well I got to the bottom of it.

My original database had a few linked tables (to Quickbooks), when I created these linked tables in access, I had to nominate a primary key.

So,when I create a new blank database & import the old database...Access imports the linked tables from the original database, but for some reason it doesn't bring in the primary keys (bug?). There's no way of changing this with a linked table (in othr words you just can't go into an already linked table & set a primary key...I think this part can only be done at the time of setting up the linked table).

The solution is in the new database, you have to create brand new linked tables with a primary key (not a big deal in my case as I've only a couple of linked tables....just a minute or so's work)

Please I got the the bottom of it though...it was driving me potty.

Thanks for your help