How To Update Data

[jereece]

I have an Access file with one data table and several linked (read only) tables from a mainframe database. I run an Append Query to import certain data from the linked tables into my data table. Periodically the data in certain fields in the linked tables will change. I want to refresh my local data to reflect this. So I tried to write an Update Query, but it keeps telling me that "The Operations is not an updatable query". I assume this is because the linked tables are read only even though I am trying to update from the linked tables to my local data table. So I am not trying to modify the read only tables.

Assuming I am correct, what is the best way to accomplish my need to update certain fields in my data table with the information on the linked tables?

Thanks for your help.

Jim

 

[jereece]

A little update on my problem. I created a Make Table query to copy the data I am interested in updating down to my Access file. I then created an Update Query based just on the local table I created to update my main data table. But I still get the error message that the query is not updatable. I am not sure why because all the tables in the query are local tables inside the Access file. Please help.

Thanks,
Jim

 

[jereece]

Does no one have any ideas how to update data in a table from data in another table? I really appreciate any help.

 

[JimmyK]

Can you paste what your query is so people can get an idea of what could possibly be wrong?

 

[jereece]

I am not sure if this is what you need, but here's a link to an image capture of the query.

I basically want to update some fields in tbl_Data with data in tbl_Update Status.

I appreciate the help. Let me know if you need more info.

 

[folkie]

I've been trying all day to do something similar (I was only trying to update 1 field and only had 1 join between the 2 tables). I finally got it to work by removing that join.

However, right before I got it to work, I used something I found here in the forum, "nz([tablename].[fieldname], <default value>)" instead of just "[tablename].[fieldname]" in the criteria field. I saw that i got the default value instead of the value of the field in the table. Then, I thought of removing the join and then I got the value from the table.