Change Query with code

[George Bowyer]

How can I change the SQL of a saved query using VBA?

(Basically, I want a button to reset a query that users might have modified to a "default" setting)

Thanks,

George

 

[the_utmost]

I am doing exactly this right now. What you might want to do is a 'replace' command that replaces the saved values with the ones on your form. Can you give us more info?

 

[George Bowyer]

Ok. Basically I have a contacts (membership) database used by a number of people all of whom have some knowledge of Access but not a lot. This is a club funtion not a commercial one.

The main general-purpose viewing form for members' details usually shows all records, using a query that just sorts the records by first and last name as recordsource.

I have a menu page that allows operators to open the form with three or four much more specific (and more complex) queries as recordsource. These cover the most frequently used sub-groups within the members.

If an operator wants to select more obscure criteria, then they either create a new query and change the form's recordsource property - usually forgetting what changes they made or how to change it back and litter the db with new queries - or they fiddle with the existing record source query, again often forgetting what they did.

So, what I have done is create a new query called qryCustomised and given them a button that opens qryCustomised in design view. Once they have saved that, a second button opens the form with qryCustomised as recordsource.

What I want is to create a third button that changes qryCustomised back to a default setup, ie it cancels out any changes that operators may have saved.

I am trying to do this with code. I have got the SQL statement that I want from the query design grid, but I don't know how to actually stick it into the query.

I can open the query with code, I can run it, I can do all sorts of things with recordsets based on it, but I don't know how to actually change the query's underlying SQL itself. None of my books seem to address that.

 

[Uncle Gizmo]

I think you could do it with a "Querydef" I did it a long time ago and have a book with it in somewhere, you had to do this with older ver of access if you wanted to change a reports record source if you saved *.mdb as *mde. Have a Google, in the mean time I will try and find my code!

 

[George Bowyer]

I have been trying to do it by changing the query's .sql property but I can't seem to get it right...

 

[sportsguy]

On whatever Event is appropriate to change the query

Private Sub EventName()

Dim dbLocal as DAO.Database
Dim qdf As DAO.QueryDef

Set dbLocal = CurrentDb()

strSQL = "SELECT tblName.Field1, tblName.Field1 " & _
    "FROM tblName INNER JOIN tblaName2 ON tblName1.FieldName =  " & _
    "tblName2.FieldName WHERE tblName.Field2=criteria; "

On Error Resume Next

DoCmd.DeleteObject acQuery, "QueryName"
            
Set qdf = dbLocal.CreateQueryDef("QueryName", strSQL)

End Sub

sportsguy

 

[George Bowyer]

Thanks, Sportsguy, that hit the spot.

Somehow it goes against the grain to "delete" something that you actually want to keep, so that solutuion never occurred to me....

George

 

[doulostheou]

There is another alternative that will allow you to edit a querydef without deleting it.

Sub ChangeRecordset(strSQL As String)
    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.QueryDefs("qryInventory")

    qdf.SQL = strSQL
    qdf.Close 'I am not sure if this is necessary or not

    Set qdf = Nothing
End Sub

 

[raskew]

Hi-

IMHO, allowing semi-skilled users direct access to queries and tables is a train-wreck just waiting to happen!

Would suggest that you create a form that shows all of the data, then take a look at Steven Nyberg's form-filtering solution (fFil) at http://www.mile50.com/. It allows the user to filter on any fields without modifying the underlying query. I've used Steven's various renditions of this well-designed freeware for years and it works as advertise.

HTH - Bob