CODEWITHSUNDEEP
sqlvbams-access
Peter
Peter

Reputation: 148

Problem with vba update query with inner join

Inner join update query has no errors but doesn't update. I've tried the many solutions offered but still doesn't work! I need to update status field from all records from CN99s to [Report_LEC].[STATUSD]

UPDATE Report_LEC INNER JOIN CN99s ON Report_LEC.network = CN99s.nwk SET 
[Report_LEC].[STATUS] = CN99s.nwk_status;

If I do it manually via code it works!But very slow! 

statstr = rs![nwk_status]
netstr = rs!nwk
CAPSQL = "UPDATE Report_LEC " & "SET status = '" & statstr & "' " & "WHERE NETWORK =" & netstr

DoCmd.RunSQL (CAPSQL)

There are 357216 records in CN99s and 13756 records in Report_LEC.

Thanks

Pete

Upvotes: 0

Views: 37

Answers (1)

Peter
Peter

Reputation: 148

Found the problem which turned out to be my own stupidity! I was hitting datasheet view and seeing the query bar show up and advance, I thought it was actually doing the update, but you have to save a RUN the query for it to work! Hadn't touched access in a few months and got fooled!

Pete

Upvotes: 0

Related Questions