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

peskywinnets

Registered User
Joined
Feb 4, 2014
Messages
520
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)
Code:
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?
 
Last edited:

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,629
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

Registered User
Joined
Feb 4, 2014
Messages
520
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

error reading drive A:
Joined
May 7, 2009
Messages
8,629
compact and repair maybe.
relink the tables.
remake the query, etc.
 

peskywinnets

Registered User
Joined
Feb 4, 2014
Messages
520
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 :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom