How to change querydef sql programmatically in MS Access

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 upd开发者_开发百科ate this physical query ?

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

doesn't work either.

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.

I know this is an old thread, but found it when I was looking for a solution to a similar problem. So anyone finding this thread for the same reason may find my solution helpful.

In my case the problem was due to using workspace begintrans/commitrans for a range of action queries, plus some select queries displayed in a form for the user to review before continuing (using a subform with query.nameofquery as the sourceobject)- the querydef object was not being updated until the committrans code was run.

Nesting Begintrans did not work for me (tho' I may have missed something) so I had to modify the code to go through all the confirmations first and then start begintrans