Saving a Query 1

[irishjoe]

Does anyone know if it is possible to create and save a query using VBA?

I would like to be able to create something like the following...

"Select * from tblUsers Where [location]='Australia'"

and save the query as qryAussieUsers

Is this possible?

 

[TheAceMan1]

How are ya irishjoe . . .

have a look at [blue]Query Def[/blue] in VBE help!

See Ya! . . . . . .

Be sure to see thread181-473997

 

[irishjoe]

Hey Ace,

Can you give me a hint on where abouts to look? All I can find is playing with queries thru the access interface. I want to create queries from VBA alone...

Something along these lines.

Set QueryDef = Object.CreateQueryDef("qryName", "sqltext")

I don't really know what to search for so no wonder I'm coming back with squat!

Thanks for the reply.

 

[irishjoe]

Got it! There may and will probably be easier ways of doing it but this is how I did it.

Dim dbsNorthwind As Database
Dim qdfNew As QueryDef
Set dbsNorthwind = CurrentDb()
With dbsNorthwind
   ' Create permanent QueryDef.
   Set qdfNew = .CreateQueryDef("qryName", "SELECT lkupAVCConnection.ID FROM lkupAVCConnection;")
   ' Open Recordset and print report.
   GetrstTemp qdfNew
   .Close
End With

And then the getrstTemp function is from Microsoft I think…

Function GetrstTemp(qdfTemp As QueryDef)
    Dim rsttemp As Recordset
    With qdfTemp
        Debug.Print .Name
        Debug.Print " " & .sql
        Set rsttemp = .OpenRecordset(dbOpenDynaset)
        Do While Not rsttemp.EOF
            Debug.Print rsttemp(0) ', rsttemp(1), rsttemp(2), rsttemp(3), rsttemp(4)
            rsttemp.MoveNext
        Loop
        With rsttemp
             .MoveLast
            Debug.Print "number of records = " & .RecordCount
            .Close
        End With
    End With
 End Function