using saved queries with ADO (Access 2000 SR1)

Topic

New Reply

Hi

I’m just getting to grips with syntax changes from DAO to ADO. In my old Access 97 code (DAO) I have used several techniques like this:

set dbs=currentDb
dbs.Execute “name of saved query”
————————————————-
or:
set qdf = dbs.QueryDefs(“name of saved query”)
qdf.Execute
————————————————-
or:
set rst=dbs.OpenRecordset(“name of saved query”)

—————————————————

Do these have equivalents in ADO – i.e. can I open or execute saved query names – or do I have to resort to getting the SQL property of the query and executing that (or opening an ADO recordset based on this SQL)?

Thanks

Jeremy

Reply | Quote

Replies

Hi,

Here’s what I have done so far.

Sub DeleteDAO()
Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute “Delete * From Test;”
dbs.Close
End Sub

Sub DeleteADO()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute “Delete * From Test;”
cnn.Close
End Sub

Find more info about moving from DAO to ADO at http://msdn.microsoft.com/library/techart/daotoadoupdate.htm.

HTH.
Tim K.

Reply | Quote

Saved queries are already optimized, so they’re the preferred method where possible. As with DAO, there are multiple ways of running a query from ADO, either a saved query or using a SQL string. Look up the Execute method in Access 2000 help by typing “Execute a query” into the Answer Wizard tab (without the quotes) and you’ll see a list of topics, including the Execute method for both the Command and Connection objects and the Open method for a recordset. All of these may be used with either a SQL string or a saved query.

Reply | Quote