<div>
You have a reference to [tbl] there, is that intentional?<br />
<br />
UPDATE tblMaster INNER JOIN tblPricing ON (tblMaster.fldPIO=tblPrici<wbr />ng.COACCE)<wbr /> AND (tblMaster.fldMdlYr=tblPri<wbr />cing.COMDL<wbr />YR) AND (tblMaster.fldSeries=tblPr<wbr />icing.COSE<wbr />R2) SET tblMaster.DlrCost = [tblPricing].[CODCTL];<br />

</div>


<div>
Ooops, I meant [tblPricing].[CODCTL] instead of [tbl].[CODCTL]. &nbsp;Thanks for the catch!
</div>


<div>
&quot; 'tblPricing' is a read-only table&quot;<br />
Read only table ? No such thing.<br />
<br />
mx<br />

</div>


<div>
By &quot;read only&quot;, I mean I can't modify it with Access because it is linked from a server. Suppose <br />
<br />
Suppose it wasn't a table, and tblPricing was a &quot;select&quot; query instead. The update query still will not work.<br />
<br />
They structure of my query seems to be sound. That's why I'm really stumped why this won't work.
</div>


<div>
when u said read only table, I half expected this to be a linked table or something. You have just confirmed that.<br />
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.<br />
<br />
You also get this error on queries with multiple tables that do not have primary keys. Do your tables have primary keys defined?<br />
<br />

</div>


<div>
&quot;mean I can't modify it with Access because it is linked from a server.&quot;<br />
You mean you can't change data ? &nbsp;Or modify the table design?<br />
<br />
&quot;I mean I can't modify it with Access because it is linked from a server&quot;<br />
Why? Linking certainly does not make a table Read Only ...<br />
<br />
mx
</div>


<div>
rockiroads, <br />
I do not have access to edit it or change its design. &nbsp;Also, my tables do not have primary keys. &nbsp;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. &nbsp;I was hoping to avoid this to help keep the size of my database small. &nbsp;If this is my only option, I can do that, but I was hoping there was a way I can modify the query. &nbsp;This will help keep the size of my database down. &nbsp;If not then I guess I'm outta luck.
</div>


<div>
Sorry, when I meant edit, I meant in sql server/<br />
<br />
In your query do you want to update all the records? &nbsp;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.<br />
<br />
I believe that update queries with multiple joins require a primary key of some sort, that is my understanding anyways.<br />
Does this have to be in a query? I mean it could possibly be done using vba.<br />
<br />

</div>


<div>
rockiroads,<br />
&quot;In your query do you want to update all the records? &nbsp;So set DlrCost to CODCTL where the 3 conditions match?&quot; &nbsp;--yes, I only want to update records where these three conditions match. &nbsp;<br />
<br />
I also believe your right primary keys being needed. &nbsp;I don't have any primary key for the 'tblPricing'. &nbsp;So doing this in a query is probably not an option for me. &nbsp;I'm totally open to any suggestions on how this could be accomplished in VBA.<br />
<br />
Thanks for your help! ;-)
</div>


<div>
OK ... I created the exact same tables, no primary keys ... added some data, 3 rows ... &nbsp;two identical ... and I am able to run and update two rows in tblPricing with no problem ... FWIW.<br />
<br />
So .... re:<br />
<br />
&quot;from a server&quot;<br />
<br />
Are you *sure* that ... you (or whoever) has FULL permissions on the folder containing the linked table on the server ?<br />
<br />
mx<br />
<br />

</div>


<div>
DatabaseMX, <br />
I have &quot;read-only&quot; access (or whatever you want to call it) to the table listed on the server. &nbsp;I don't need to modify the table that is linked to the server (tblPricing). &nbsp;I need it to determine which records to update in 'tblMaster' table.<br />
<br />
If I created both of the tables inside access and ran the same update query, it would work no problem. &nbsp;So a workaround would be to append 'tblPricing' data into a new table within access such as 'tblPricingAppend' as reflected in SQL below.<br />
<br />
This is not the preferred method for me though. &nbsp;tblPricing contains a lot of records and it significantly increases the size of my database, which I want to avoid. &nbsp;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.<br />
<br />
Thanks<br />
<br />
<br />
<br />
<br />

<pre><code class="code-1 nolinks" id="code-1012719"><span>UPDATE tblMaster INNER JOIN tblPricingAppend </span>
<span></span>
<span>ON (tblMaster.fldSeries = tblPricingAppend.COSER2) </span>
<span>AND (tblMaster.fldMdlYr = tblPricingAppend.COMDLYR) </span>
<span>AND (tblMaster.fldPIO = tblPricingAppend.COACCE) </span>
<span></span>
<span>SET tblMaster.DlrCost = [tblPricingAppend].[CODCTL];</span>
</code></pre>
</div>


<div>
&quot;I have &quot;read-only&quot; access (or whatever you want to call it) to the table listed on the server. &quot;<br />
oK ... so, we know what ... the problem is. &nbsp;Read Only access.<br />
<br />
&quot;which I want to avoid.&quot;<br />
Well, how about you import into a temp table, run the update, delete the temp table ... and then run a Compact &amp;&nbsp;Repair. &nbsp;Basically, not change in size.<br />
<br />
Option ?<br />
<br />
mx
</div>


<div>
I'll give it a whirl, thanks!
</div>


<div>
Please post back and confirm that it works ...<br />
<br />
mx
</div>


<div>
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.<br />

</div>


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


<div>
<div class="content wysiwyg-content">
Not sure what I'm doing wrong on this update query. &nbsp;<br />
<br />
&nbsp;
<pre><code class="code-1 nolinks" id="code-1009444"><span>UPDATE tblMaster INNER JOIN tblPricing </span>
<span></span>
<span>ON (tblMaster.fldSeries = tblPricing.COSER2) </span>
<span>AND (tblMaster.fldMdlYr = tblPricing.COMDLYR) </span>
<span>AND (tblMaster.fldPIO = tblPricing.COACCE) </span>
<span></span>
<span>SET tblMaster.DlrCost = [tbl].[CODCTL];</span>
</code></pre>
<br />
Instead of it running, I get an error message &quot;Operation must use an updateable query.&quot; &nbsp;Any ideas on what I'm doing wrong or a way around this?<br />
<br />
In my query, 'tblPricing' is a read-only table, but the 'tblMaster' (the one I'm trying to update) allows updates and edits. &nbsp;Not sure if this information holds the key in solving my problem, but I at least wanted to present it.<br />
<br />
Thanks for any help!
</div>
</div>


Not sure what I'm doing wrong on this update query.  

 

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!

Access Query Error: &quot;Operation must use an updateable query.&quot;

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Access Query Error: &quot;Operation must use an updateable query.&quot;

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