如何在VBA中创建一个查询对象?

28795277

请问如何在VBA中用创建一个查询对象?

[此贴子已经被作者于2006-3-18 13:05:50编辑过]

28795277

感谢！

skylark

Creating a Stored Query


The following listings demonstrate how to create a row returning, non-parameterized query.


DAO


Sub DAOCreateQuery()





    Dim db      As DAO.Database


    Dim qry     As DAO.QueryDef





   'Open the database


    Set db = DBEngine.OpenDatabase("C:\nwind.mdb")


   


    'Create query


    Set qry = db.CreateQueryDef("AllCategories", _


      "SELECT * FROM Categories")


        


    db.Close


   


End Sub





ADOX


Sub ADOCreateQuery()





    Dim cat     As New ADOX.Catalog


    Dim cmd     As New ADODB.Command


   


    ' Open the catalog


    cat.ActiveConnection = "rovider=Microsoft.Jet.OLEDB.4.0;" & _


        "Data Source=C:\nwind.mdb;"





    ' Create the query


    cmd.CommandText = "Select * FROM Categories"


    cat.Views.Append "AllCategories", cmd


   


    Set cat = Nothing


   


End Sub





In this example, because the SQL statement is a non-parameterized, row-returning query, the ADO Command object is appended to the ADOX Views collection. Note, that when using the Jet Provider, it is possible to append a Command object to either the Views or Procedures collection regardless of the type of query that is being created. However, if a query such as the one in this example is appended to the Procedures collection, then the Procedures and Views collections are re