UPDATE Query based on imported data

[jayteebee3]

Hi all, hoping you can help me with the following. (I've just inherited a databse from a predecessor that I need to make a few ammendments too)

I have a table (tDist) that users are updating manually based off an excel file. I've written a query (qImp) to take the relevant data from the file, which works fine. The problem is when I try to UPDATE tDist based off this query.

Structure of the Table and Query:

tDIST
Acc TEXT(10),
Cl TEXT(2),
XD DATE/TIME,
PD DATE/TIME,
Ccy TEXT(3),
Div DOUBLE,
Created On DATE/TIME,
Created By TEXT(100)

qImp
Acc,
Cl,
XD,
PD,
Div

So basically if Acc and Cl are in qImp then update the details XD, PD and Div from tDist. I've got the following but I get an "Operation must use an updateable query" message.

UPDATE tDist AS A 
  INNER JOIN qImp AS Q 
  ON (cdbl(A.SC)=Q.SC) AND (cdbl(A.Acc)=Q.Acc) 

SET A.XD = Q.XD, A.PD = Q.PD, A.Div = Q.Div;

Any help would be greatly appreciated as I would usually use an UPDATE FROM...but access doesn't seem to like that

 

[CJ_London]

I don't think Access likes the inner join and the fact you are using a 'calculated' value for the join.

Try

UPDATE tDist AS A, qImp AS Q 
 SET A.XD = Q.XD, A.PD = Q.PD, A.Div = Q.Div
WHERE  (cdbl(A.SC)=Q.SC) AND (cdbl(A.Acc)=Q.Acc);

 

[Cronk]

I don't see the field SC in the table structure.

Post the SQL for qImp

 

[jayteebee3]

@Cronk, Apologies SC was a type of "CL"

qImp
SELECT DISTINCT S.[Acc], S.[Cl], S.[XD], S.[PD], S.[Div]
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=H:\Dist.xls].[Sheet1$A1L65536] AS S;

@CJ, that's another one I tried, but I still get the same error: "Operation must use an updateable query"

EDIT: I pushed the query into a table and used that in the UPDATE and your Query works CJ. Thanks

 

[CJ_London]

Funny, worked for me!

Have you tried removing the alias's and cdbl? - if necessary, modify your qImp query to get the data into the right format for the table:

 

[jayteebee3]

Without the type conversions, I get type mismatches since Acc and Cl are both stored as Integers in the xl file.

Without the aliases I get the same error message ( "..updateable query")

Modifying the qImp with cStr conversions on Acc and Cl still yields the same error message.

As mentioned above though, I used a SELECT INTO a temporary Table and then used the UPDATE as above from the temporay Table and it seems to work.