SQL Update woes in MS Access - Operation must use an updateable query

I have a select query which does some text manipulation to essentially reformat a field so that I can look it up in another table:

If my first table if I have a field like "J1/2" it looks up the ID of a record in a different table with J1 and J2 in the appropriate fields.

This all works well.

Now I want to update the original table so I don't have to do lookups using this string manipulation anymore, but my attempts at update queries end with "Operation must use an updateable query"

Any ideas?

My SELECT statement:

SELECT DISTINCT
t1.DD,
t1.TN,
t1.DD & " J" & MID(t1.TN,2,1) AS CalculatedStart,
t1.DD & " J" & MID(t1.TN,4,1) AS CalculatedEnd,
t2.ID
FROM t1 INNER JOIN t2
ON (t1.DD & " J" & MID(t1.TN,2,1)=t2.StartLink)
AND (t1.DD & " J" & MID(t1.TN,4,1)=t2.EndLink)
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";

Recall - this works fine and I get the necessary t2.ID out the other end.

So I want to do something like:

UPDATE t1 SET t2ID = (
    SELECT Query1.ID
    FROM Query1
    WHERE t1.DD=Query1.DD
    AND t1.TN=Query1.TN
    )
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";

Only this fails. This is within MS Access itself so I can't imagine an actual permissions problem like most of the "Operation must use an updateable query" problems seem to be.

EDIT: Trying to simplify the case that doesn't work.

This UPDATE query is fine:

UPDATE t1
SET t2ID="Unknown"
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";

This one fails (Thanks Goedke - this example obviously fails because the subquery returns more than 1 result. I had oversimplified to try to find my problem)

UPDATE t1
SET t2ID=(SELECT ID FROM t2)
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";

So do I just have my subquery syntax wrong in some way?

EDIT: This SELECT statement is fine too:

SELECT t1.OA, t1.DD, t1.TN, t1.HATRIS,
    query1.DD, query1.TN, query1.ID
FROM t1 INNER JOIN query1
ON t1.DD=query1.DD
AND t1.TN=query1.TN

Furthermore, using count on the select statement above shows that there is exactly 1 ID being returned per (DD,TN) combination

EDIT:

The simplest case I've now got to - using various SELECT statements I now have a table with just 2 columns - the primary key of t1 and the value I want to insert into t1.

I still can't seem to write

UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)

where t1's primary key is f1. Even adding WHERE t1.f1 IN (SELECT f1 FROM t2) doesn't help. (Added to eliminate the possibility that the subquery returns 0 results)

Solution

A subquery of (SELECT ID FROM t2) can't work unless there is only one record in t2. Which ID are you expecting to be used?

The error message that is being reported normally occurs when you have joins and are not including all of the primary keys necessary to update back to tables in a data bound form (for example, your original DISTINCT destroys information about keys, so if it was bound to a form, the form would not be able to save back).

The fact you are using DISTINCT there would make me suspicious that the sub query is returning more than one row in your more complex example. This is probably the most common problem with assigning out of a sub query result: under-constraining the where clause.

Another problem I have seen with assigning out of a subquery is if the syntax of the inner query is incorrect. At least with SQL 2000 and 2005 back ends, the query processor will silently fail and return NULL in such cases. (This is, as far as I can tell, a bug: I see no reason why something that will return an error at the top level would be silently permitted in a subquery... but there it is.)

EDIT: Just to ensure that neither Paul or I wasn't going crazy, I created the following tables:

t1 | ID, FK, Data
t2 | ID2, Data2

I did not put any constraints except a primary key on ID and ID2. All fields were text, which is different from what I normally use for IDs, but should be irrelevant.

t1:

ID  FK  Data
Key1        Data1
Key2        Data2
Key3        Data3

t2:

ID2 Data2
Key1    DataA
Key2    DataB
Key3    DataC

A query of the form:

UPDATE t1 SET t1.FK = (select ID2 from t2 where t2.ID2 = t1.ID);

Failed with the same message Paul got.

select *, (select ID2 from t2 where t2.ID2 = t1.ID) as foreign from t1,

works as expected, so we know the subquery syntax is not to blame.

UPDATE t1 SET t1.FK = 'Key1'

also works as expected, so we don't have a corrupt or non updateable destination.

Note: if I change the database backend from native to SQL 2005, the update works! A bit of googling around, and I find Access MVPs suggesting DLOOKUP to replace a subquery:

http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx

Apparently this is a bug in Access SQL, one that is avoided when using a SQL Express 2000 or higher back end. (The google results for "access update subquery" support this theory).

See here for how to use this workaround: http://www.techonthenet.com/access/functions/domain/dlookup.php