F. Scott Barker's Microsoft® Access 2000 Power Programming

WORKING WITH QUERIES USING ADO

After working with recordsets, the next important task that will probably be necessary is to be able to manipulate queries from within VBA using ADO. To get started, see how to create a straightforward query and then open a recordset off it.

Creating a New Query

When creating and working with queries, you will use the ADOX Command object's CommandText property off the Catalog object:

Sub CreateASimpleQuery()

        Dim catCurr     As New ADOX.Catalog
        Dim cmdCurr     As New ADODB.Command

        catCurr.ActiveConnection = CurrentProject.Connection
        cmdCurr.CommandText = "Select * FROM tblMovieTitles"

        catCurr.Procedures.Append "qryMovieTitles", cmdCurr

        Set catCurr = Nothing

End Sub

Notice that the command is then added to the Procedures ...