When is a query updateable?

Topic

New Reply

Hi all!

I have a question regarding the message “Must use an updateable query.”

I built a very simple update query from two tables. The table I am updating is local, the table I’m using to update it is in DB2. Simple join between the two. Select query is successful, no problems. But when I try to run the update query, I keep getting the message “Must use an updateable query.” I have other update queries that are more complex than this, but all were written before we upgraded to Access 2003.

I know I can do this in code, but the db is shared, and other people don’t understand the code I write and get upset with me when I do stuff that way, so I’m trying not to. I made sure the database isn’t corrupt, and after not succeeding when other people were logged in, I waited until everyone was out to run the query, still with no success.

Any ideas?

Sample of the query I’m using:

UPDATE tblMyTable INNER JOIN DB2_TABLE ON tblMyTable.RecordID = DB2_TABLE.RECORDID SET tblMyTable.SpecialCode = Trim([DB2_SPECIAL_CODE]) WHERE (((DB2_TABLE.DB2_SPECIAL_CODE)””));

Reply | Quote

Replies

For two table Inner Joins like your example, there are a couple of possibilities. One is that one of the tables does not have a primary key. The other possibility is that you are trying to update multiple rows in one of the tables, or you are trying to put multiple values into a single record. For more information on the subject check out John Viescas’ Updatability.

Reply | Quote

What a great reference, Wendell! Thanks! I found the following “checklist” on that page:

Query Fields That Cannot Be Updated

Any field that is the result of a calculation
Any field in a total or crosstab query
Any field in a query that includes a total or crosstab query as one of the
AutoNumber fields
A primary key participating in a relationship unless Cascade Update is
Any field in a Unique Values query
Any field in a UNION query

It turned out that my tblMyTable didn’t have a designated Primary key, so I fixed that situation. But the rest doesn’t apply..I’m merely updating new blank fields, 62 of them out of the 700-odd records in the table, and the primary key is definitely unique and is the basis of the join. Nothing I can do if there’s no primary key in the DB2 table, but I hope that there is one (and believe that there is one, too, and it should be the same one as in tblMyTable).

I’m a little unclear about the Cascade Update relationship thing, trying to figure that out now, but I doubt that’s the problem, either. Grrr, how frustrating.

Edit: I gave up and did it in code.

Reply | Quote

It turned out that my tblMyTable didn’t have a designated Primary key, so I fixed that situation. But the rest doesn’t apply..I’m merely updating new blank fields, 62 of them out of the 700-odd records in the table, and the primary key is definitely unique and is the basis of the join. Nothing I can do if there’s no primary key in the DB2 table, but I hope that there is one (and believe that there is one, too, and it should be the same one as in tblMyTable).

I’m a little unclear about the Cascade Update relationship thing, trying to figure that out now, but I doubt that’s the problem, either. Grrr, how frustrating.

Edit: I gave up and did it in code.

I may be wrong but i would think you cannot join an update query across multiple databases especially one via ODBC.
What i would have done is to import the records required into a local access table ensuring that table has a primary key, then run an update queryon 2 access tables.

The Cascade Update is not applicable in this problem you have. What a Cascade Update relationship thing does is to change the values of a field that is defined in the ONE side of a relationship and is also defined in tables linked to that table.
In this case it will change the field in all related tables where it is used and linked to that master table.

nb. I read an interesting article once where you should never need to use the Cascade Update feature, this article explains to use Autonumber fields to join tables..

Reply | Quote

I may be wrong but i would think you cannot join an update query across multiple databases especially one via ODBC.

I just did a test of this and it worked OK for me. I have a database with tables linked via ODBC to a SQL Server db. I ran an update query to updates values in a local table using values from the linked table.

Code:

UPDATE tblPatientsLocal INNER JOIN dbo_Patients ON tblPatientsLocal.patientID = dbo_Patients.patientID SET tblPatientsLocal.surname = [dbo_Patients].[lastname]
WHERE (((dbo_Patients.lastName) Is Not Null))

I suspect that the DB2 table does not have a Primary Key.

I agree that using autonumbers as key fields avoids the need for cascading updates, but I find the odd occasion where they are useful. Usually where you inherit some data. Just yesterday I was working with membership data, where the membership type was a letter code, and I needed to change the codes. Cascading Updates took all the pain out of it.

Reply | Quote

Thanks John, I stand corrected.

Reply | Quote