Solved

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

Posted on 2010-09-06
17
629 Views
Last Modified: 2012-08-13
Not sure what I'm doing wrong on this update query.  

 
UPDATE tblMaster INNER JOIN tblPricing 

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

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

Open in new window


Instead of it running, I get an error message "Operation must use an updateable query."  Any ideas on what I'm doing wrong or a way around this?

In my query, 'tblPricing' is a read-only table, but the 'tblMaster' (the one I'm trying to update) allows updates and edits.  Not sure if this information holds the key in solving my problem, but I at least wanted to present it.

Thanks for any help!
0
Comment
Question by:KP_SoCal
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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];
0
 

Author Comment

by:KP_SoCal
Comment Utility
Ooops, I meant [tblPricing].[CODCTL] instead of [tbl].[CODCTL].  Thanks for the catch!
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" 'tblPricing' is a read-only table"
Read only table ? No such thing.

mx
0
 

Author Comment

by:KP_SoCal
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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?

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
0
 

Author Comment

by:KP_SoCal
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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.

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:KP_SoCal
Comment Utility
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! ;-)
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

0
 

Author Comment

by:KP_SoCal
Comment Utility
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];

Open in new window

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
Why dont you have one query which returns the DlrCost but do the join

Now with this value you can go and update tblMaster

eg

SELECT DISTINCT CODCTL, COSER2, COMDLYR, COACCE FROM tblMaster, tblPricing
WHERE tblMaster.fldSeries = tblPricing.COSER2
AND tblMaster.fldMdlYr = tblPricing.COMDLYR
AND tblMaster.fldPIO = tblPricing.COACCE

Use a recordset or something to obtain this value

Iterate through this recordset and update tblMaster as you go along

UPDATE tblMaster SET DlrCost = rs!CODCTL
WHERE fldSeries = rs!COSER2
etc


Probably be slow anyways unless you got indexes in the right places
0
 

Author Closing Comment

by:KP_SoCal
Comment Utility
I'll give it a whirl, thanks!
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Please post back and confirm that it works ...

mx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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.
0
 

Author Comment

by:KP_SoCal
Comment Utility
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now