• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

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

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
KP_SoCal
Asked:
KP_SoCal
  • 7
  • 5
  • 4
  • +1
1 Solution
 
cyberkiwiCommented:
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
 
KP_SoCalAuthor Commented:
Ooops, I meant [tblPricing].[CODCTL] instead of [tbl].[CODCTL].  Thanks for the catch!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" 'tblPricing' is a read-only table"
Read only table ? No such thing.

mx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
KP_SoCalAuthor Commented:
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
 
rockiroadsCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
KP_SoCalAuthor Commented:
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
 
rockiroadsCommented:
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
 
KP_SoCalAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
KP_SoCalAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
rockiroadsCommented:
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
 
KP_SoCalAuthor Commented:
I'll give it a whirl, thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Please post back and confirm that it works ...

mx
0
 
rockiroadsCommented:
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
 
KP_SoCalAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now