Update doesn't work in VBA but does in a Query

[pterodroma]

I am using VBA in Access 2003 to perform updates to a collection of tables.

All but one of my updates succeed, however this particular update does not fail, but does not update any rows.

db.Execute "Update tempflightheight, flightdetails Set tempflightheight.flight_id = flightdetails.flight_id where tempflightheight.flight_id = flightdetails.tempflight_id;", dbFailOnError

When I extract the update statement and run it as a Query in Access it succeeds so the logic of the query appeato be correct.

I have tried changing the syntax of the join to be 'inner join', using 'Docmd.runsql' rather than 'db.Execute' but with similar results

It seems to be an issue with the where clause as this statement successfully updates rows:

db.Execute "Update tempflightheight, flightdetails Set tempflightheight.flight_id = 2000", dbFailOnError

but this doesn't:

db.Execute "Update tempflightheight, flightdetails Set tempflightheight.flight_id = 2000 where tempflightheight.flight_id = flightdetails.tempflight_id;", dbFailOnError

Any help or ideas much apprieciated.

Thanks

 

[James Dudden]

Where is flightdetails.tempflight_id coming from in the Where clause?

If it's a control then the code should be something like this:

db.Execute "Update tempflightheight, flightdetails Set tempflightheight.flight_id = 2000 where tempflightheight.flight_id = " & me!controlname & ";", dbFailOnError

 

[pterodroma]

Hi James, thanks for yours.

There are no forms or controls involved here. Just pure data manipulation in tables.

 

[pterodroma]

I've finally found a workaround to what I suspect is a bug, but have limited experience with Access & VBA so can't be sure.

I created an intermediate view (query) with VBA to store the data that wont update, then carried on from there.

If anyone thinks this is not a bug and has a better solution, please let me know!

 

[spikepl]

Your SQL is a bit weird.

See an example here for SQL for updating data using joined tables http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63131