Update Query's SQL in VB

[flemmo]

I have a form with a button. When I click on the button I want to update the Sql in that query to something else - in a similar way to updating a form's recordsource I guess?

Thanks

 

[pbaldy]

Search on QueryDef which should turn up the appropriate code.

 

[flemmo]

will do. Thanks for the quick reply.

 

[pbaldy]

No problemo; post back if you get stuck.

 

[flemmo]

Guess what? I'm stuck lol!

As an example I want to update the Query "MyQuery" with "SELECT * from CUSTOMERS". How would I do this? I was playing around with the following but it doesn't work so...

Dim NewSQL As QueryDef
Set NewSQL = CreateQueryDef("SELECT * from CUSTOMERS",MyQuery)

Thanks

 

[flemmo]

Think I've cracked it?

 Dim db As Database
 Dim qdf As QueryDef
 Dim strSQL As String
 Set db = CurrentDb
    
 strSQL = "SELECT * FROM CUSTOMERS;"
 db.QueryDefs("MyQuery").SQL = strSQL

 

[pbaldy]

Try this to change the SQL of an existing query:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("MyQuery")

    qdf.SQL = "SELECT * from CUSTOMERS"

    qdf.close
    Set db = Nothing
    Set qdf = Nothing

 

[pbaldy]

Looks like we were posting at the same time, and offhand it looks like yours would also work.

 

[flemmo]

thanks for your help