Access Query Error: "Operation must use an updateable query."

You have a reference to [tbl] there, is that intentional?

UPDATE tblMaster INNER JOIN tblPricing ON (tblMaster.fldPIO=tblPricing.COACCE) AND (tblMaster.fldMdlYr=tblPricing.COMDLYR) AND (tblMaster.fldSeries=tblPricing.COSER2) SET tblMaster.DlrCost = [tblPricing].[CODCTL];

Ooops, I meant [tblPricing].[CODCTL] instead of [tbl].[CODCTL].  Thanks for the catch!

" 'tblPricing' is a read-only table"
Read only table ? No such thing.

mx

By "read only", I mean I can't modify it with Access because it is linked from a server. Suppose

Suppose it wasn't a table, and tblPricing was a "select" query instead. The update query still will not work.

They structure of my query seems to be sound. That's why I'm really stumped why this won't work.

when u said read only table, I half expected this to be a linked table or something. You have just confirmed that.
now with this table, do you have access to edit it? does it have a primary key? you would need that. Workarounds for restrictions like this usually involve temp tables.

You also get this error on queries with multiple tables that do not have primary keys. Do your tables have primary keys defined?

"mean I can't modify it with Access because it is linked from a server."
You mean you can't change data ?  Or modify the table design?

"I mean I can't modify it with Access because it is linked from a server"
Why? Linking certainly does not make a table Read Only ...

mx

rockiroads,
I do not have access to edit it or change its design.  Also, my tables do not have primary keys.  The only way I can get this to work is if I create a separate table from my linked table or select query in my Access database.  I was hoping to avoid this to help keep the size of my database small.  If this is my only option, I can do that, but I was hoping there was a way I can modify the query.  This will help keep the size of my database down.  If not then I guess I'm outta luck.

Sorry, when I meant edit, I meant in sql server/

In your query do you want to update all the records?  So set DlrCost to CODCTL where the 3 conditions match? or are you just expecting one match. If the latter you could try creating those 3 fields to be primary.

I believe that update queries with multiple joins require a primary key of some sort, that is my understanding anyways.
Does this have to be in a query? I mean it could possibly be done using vba.

rockiroads,
"In your query do you want to update all the records?  So set DlrCost to CODCTL where the 3 conditions match?"  --yes, I only want to update records where these three conditions match.  

I also believe your right primary keys being needed.  I don't have any primary key for the 'tblPricing'.  So doing this in a query is probably not an option for me.  I'm totally open to any suggestions on how this could be accomplished in VBA.

Thanks for your help! ;-)

OK ... I created the exact same tables, no primary keys ... added some data, 3 rows ...  two identical ... and I am able to run and update two rows in tblPricing with no problem ... FWIW.

So .... re:

"from a server"

Are you *sure* that ... you (or whoever) has FULL permissions on the folder containing the linked table on the server ?

mx

DatabaseMX,
I have "read-only" access (or whatever you want to call it) to the table listed on the server.  I don't need to modify the table that is linked to the server (tblPricing).  I need it to determine which records to update in 'tblMaster' table.

If I created both of the tables inside access and ran the same update query, it would work no problem.  So a workaround would be to append 'tblPricing' data into a new table within access such as 'tblPricingAppend' as reflected in SQL below.

This is not the preferred method for me though.  tblPricing contains a lot of records and it significantly increases the size of my database, which I want to avoid.  If it is my only option, I'll deal with it, but if there is a way around it--perhaps with VBA, I'm open to any suggestions.

Thanks

UPDATE tblMaster INNER JOIN tblPricingAppend 

ON (tblMaster.fldSeries = tblPricingAppend.COSER2) 
AND (tblMaster.fldMdlYr = tblPricingAppend.COMDLYR) 
AND (tblMaster.fldPIO = tblPricingAppend.COACCE) 

SET tblMaster.DlrCost = [tblPricingAppend].[CODCTL];

Select allOpen in new window

"I have "read-only" access (or whatever you want to call it) to the table listed on the server. "
oK ... so, we know what ... the problem is.  Read Only access.

"which I want to avoid."
Well, how about you import into a temp table, run the update, delete the temp table ... and then run a Compact & Repair.  Basically, not change in size.

Option ?

mx

I'll give it a whirl, thanks!

Please post back and confirm that it works ...

mx

I think you need to try the different options and go for what is best. advantage of vba is no need for extra tables but depending on the number of records, it may impact performance. temptables is easy and no vba required (I think it might be better to use append queries then clear out tables) but db grows a little quicker unless compact/repair done.

Guys, here's what I did to make it simple.  I changed the table on the server to include a field with a primary key.  As long as the table has a primary key, the update query will work as expected.  Sorry for the late response.