Solved

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

Posted on 2010-09-06
17
638 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33614975
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
ID: 33614981
Ooops, I meant [tblPricing].[CODCTL] instead of [tbl].[CODCTL].  Thanks for the catch!
0
 
LVL 75
ID: 33615021
" 'tblPricing' is a read-only table"
Read only table ? No such thing.

mx
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:KP_SoCal
ID: 33615054
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
ID: 33615110
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
ID: 33615179
"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
ID: 33618356
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
ID: 33619554
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
 

Author Comment

by:KP_SoCal
ID: 33620441
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
ID: 33620557
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
ID: 33620770
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
ID: 33620839
"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
ID: 33622649
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
ID: 33622796
I'll give it a whirl, thanks!
0
 
LVL 75
ID: 33622839
Please post back and confirm that it works ...

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33622973
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
ID: 33642609
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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