cannot update '(expression)'; field not updateable

[oliviar]

Hi guys,
This has me stumped. I can't use an update query I made from a linked table, to a normal access table.

It brings up this error.

BUT, if I copy the linked table into access, then it will work.

Whats going on?

 

[Joe4]

It is kind of a nuisance, but when performing an Update Query, all objects involved in that query must be updateable (even if the un-updateable ones are not the ones you are trying to update!).

Therefore, if you are using a linked Excel table, aggregate query, or some other un-updatebable object in your Update Query, it will not work.

There are a few workarounds, like importing the linked table directly, as you have seen. Or, you can create a Temp Table from your linked table, and use that in your Update Query instead.

 

[boblarson]

Linked tables, if to other Access databases or to databases like SQL Server, Oracle, MySQL can be updated from Access. As Joe said, linked Excel files cannot. This is due to a lawsuit that Micrsoft lost several years ago.

 

[oliviar]

How could MS lose a lawsuit about a relationship between two of its OWN programs? And why not just license the technology if thats what really happened?

 

[Joe4]

How could MS lose a lawsuit about a relationship between two of its OWN programs? And why not just license the technology if thats what really happened?

The methodology they were using to perform the updates across applications was not their own, they were using something that someone else created. They were sued and lost.

I don't know why they cannot create something themselves to do it.

 

[Norie]

Don't think it was a lawsuit between Access and Excel.

If I recall Microsoft had included something in Office that allowed this type of link, but it wasn't really their's to include.

PS Who would win between Access and Excel?

 

[oliviar]

http://patft.uspto.gov/netacgi/nph-...50&s1=5537590.PN.&OS=PN/5537590&RS=PN/5537590

Found it!

This is the annoying debate that stops us having updatable excel from access. The fight was over FOUR cents a license. >:/

Patent expires this time of year in two years! Then back to updating from access!

But seriously... 4 cents!!!

 

[boblarson]

How could MS lose a lawsuit about a relationship between two of its OWN programs? And why not just license the technology if thats what really happened?

As Norie said, it was some method of linking that was included in Office which allowed updates to occur both directions and the lawsuit was that Microsoft was using technology that they didn't create and that was not licensed by the people who did. So they were forced to remove that functionality and they apparently have not been able to come up with another method to do so, or they just have not wanted to. Hard to say which.

 

[Norie]

Must be a lot 4 cents.

 

[oliviar]

It really ruins a lot of very useful functionality that access COULD have. Seems so dumb not to pay a few cents a license for it.