ADO vs DAO when creating queries in code 
Author Message
 ADO vs DAO when creating queries in code

I'm converting applications from Access 97 to Access 2000
and don't want to use DAO at all.  Some of the VBA code
includes creating queries on-the-fly and storing them in
the Querydefs collection.  My problem is that there seems
to be no Querydefs collection in ADO, and views can only
be created on non-native databases like SQL Server.  Do I
really have to include the DAO libraries and use DAO
commands to create and store queries using VBA code?  

TIA, Isabel



Mon, 08 Nov 2004 23:50:59 GMT  
 ADO vs DAO when creating queries in code
Hi,

You cannot NOT use DAO if you use form and dot-mdb. Forms are still DAO based (the RecordsetClone
uses FindFirst, not Find).

You CAN create VIEW  and even STORED PROCEDURE with Jet 4.0 and ADO, but the later  is like a
parameter query in DAO, not a "batch" of SQL statements. In Access 2000, a created VIEW won't be
displayed in the Query tab (but it is visible in Access 2002), but that does not mean it is not
there. To create a query that the user can easily see, use DAO.

Hoping it may help,
Vanderghast, Access MVP


Quote:
> I'm converting applications from Access 97 to Access 2000
> and don't want to use DAO at all.  Some of the VBA code
> includes creating queries on-the-fly and storing them in
> the Querydefs collection.  My problem is that there seems
> to be no Querydefs collection in ADO, and views can only
> be created on non-native databases like SQL Server.  Do I
> really have to include the DAO libraries and use DAO
> commands to create and store queries using VBA code?

> TIA, Isabel



Tue, 09 Nov 2004 19:21:43 GMT  
 ADO vs DAO when creating queries in code

Quote:
> I'm converting applications from Access 97 to Access 2000
> and don't want to use DAO at all.  Some of the VBA code
> includes creating queries on-the-fly and storing them in
> the Querydefs collection.  My problem is that there seems
> to be no Querydefs collection in ADO, and views can only
> be created on non-native databases like SQL Server.  Do I
> really have to include the DAO libraries and use DAO
> commands to create and store queries using VBA code?

If you must use ADO for this you could do something like

Sub ADOModifyQuery()

    Dim cat As New ADOX.Catalog
    Dim cmd As ADODB.Command

    'Open the catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\nwind.mdb;"

    'Get the query
    Set cmd = cat.Procedures("Employees by Region").Command

    'Update the SQL
    cmd.CommandText = "Parameters [prmRegion] Text(255);" & _
        "Select * from Employees where Region = [prmRegion] ORDER BY City"

    'Save the updated query
    Set cat.Procedures("Employees by Region").Command = cmd
    Set cat = Nothing
End Sub



Tue, 09 Nov 2004 21:10:37 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ADO Recordsets and Forms (ADO vs DAO)

2. Using ADO Recordset with Forms (ADO vs DAO)

3. ADO vs DAO for Access Queries via ASP

4. ADO vs DAO for Access Queries via ASP

5. ADO vs DAO vs ADODB

6. speed of DAO code vs VBA code

7. DAO ADO Access vs Excel

8. ADO vs DAO

9. Basic Question DAO vs ADO

10. DAO vs ADO


 
Powered by phpBB® Forum Software © phpBB Group