QueryDefs - DAO vs ADO

[Guest]

Hi,

Is there an ADO equivalent to changing the SQL of a QueryDef using DAO (see
DAO code example below)?

Thank you in advance for any help. In my research, I keep finding ADO code
references to forms and reports but not to tables and queries.

Larry

Sub SampleCode()

Dim dbs As Database
Dim qdf As QueryDef
Dim sOldSQL As String
Dim sNewSQL as String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySampleQuery")

sOldSQL = qdf.SQL
sNewSQL = "SELECT * from tblSampleTable"
qdf.SQL = sNewSQL

dbs.Close
Set qdf = Nothing
Set dbs = Nothing

End Sub

 

[Douglas J. Steele]

Why? As in, what's wrong with using DAO?

 

[Allen Browne]

DAO is the native Access library (the "A" in DAO *is* Access). Access itself
uses it for creating and executing queries, and it is the most suitable
library for working with Access (JET) tables and queries. That's probably
the reason you found most of the examples were DAO ones.

It is possible to use ADOX if you have a reason to. Queries will be listed
in either the Views or Procedures collection (action queries and parameter
queries are Procedures), and you can set the CommandText of the Command in
the Catalog.

This example uses ADOX to create a new procedure:

Sub CreateProcedureAdox()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim strSql As String

'Initialize.
cat.ActiveConnection = CurrentProject.Connection

''Assign the SQL statement to the CommandText property.
strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & _
"DELETE FROM tblAdoxBooking " & _
"WHERE BookingDate Between StartDate And EndDate;"
cmd.CommandText = strSql

'Append the Command to the Procedures collection of the catalog.
cat.Procedures.Append "qryAdoxDeleteBooking", cmd

'Clean up.
Set cmd = Nothing
Set cat = Nothing
Debug.Print "Procedure created."
End Sub

 

[Guest]

Thanks for the feedback it was very helpful. The last time I programmed an
Access application was using Access '97 and it felt like since then there has
been a push towards ADO versus DAO. I thought maybe DAO was outdated but now
realize it has it's relevancy when working directly with MS Access data
elements.

 

[Tim Ferguson]

The last time I
programmed an Access application was using Access '97 and it felt like
since then there has been a push towards ADO versus DAO. I thought
maybe DAO was outdated but now realize it has it's relevancy when
working directly with MS Access data elements.

With the arrival of dot-Net, both technologies are talking to their
priests. ADO-dot-Net has nothing whatsoever to do with ADO Classic, and
knowing the ADO model will not help in the new managed-code land.

DAO is probably less dead than ADO because of its huge involvement with
and optimisation for the Jet database, which will live on[*]. You may
find that the ADP crowd say different, but I can't really tell what ADO
is good for anymore!

[*] In the new version of Access, the jet database engine development
will be taken over by the same team as the GUI. Watch out for
autocomplete and autocorrect at the DB Engine level!! I think I will be
off permanently to VB dot net and MySQL.

Best wishes


Tim F