How to change querydef sql programmatically in MS Access

Question

I have done

Dim qd as querydef
set qd = Querydefs("MyQuery")
qd.sql = "..."

In debug qd.sql has been updated but the physical MyQuery still contains the old sql.

How to update this physical query ?

The source code given by microsoft here http://msdn.microsoft.com/en-us/library/bb177500.aspx

doesn't work either.

Answer 1

You should not use SET except with objects. You need an object for QueryDefs, so:

Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("MyQuery")
qd.SQL = "SELECT Category FROM Categories"

Ensure that the SQL works, or it will error out.