QueryDef equivalent in ADO?

[sjh]

Hi,
I would like to execute a stored query in my Access Database. How can I do this using ADO? Similar to using QueryDef in DAO?


Thank you!
SJH

 

[RoyVidar]

Don't know how to use DAO, but here's one method using ADO (when you say "execute", I'm assuming action queries, to return a recordset, declare a recordset variable, and use: set rs = .execute, in stead of just exeuting the command):

[tt]dim cmd as adodb.command
set cmd=new adodb.command
with cmd
.activeconnection=<your connection>
.properties("Jet OLEDB:Stored Query")=true
.commandtext="NameOfYourStoredQuery"
.execute
end cmd[/tt]

Should it contain parameters (reference to open form?);

[tt]dim cmd as adodb.command
dim prm as adodb.parameter
set cmd=new adodb.command
with cmd
.activeconnection=<your connection>
.properties("Jet OLEDB:Stored Query")=true
.commandtext="NameOfYourStoredQuery"
for each prm in .parameters
prm.value=eval(prm.name)
next prm
.execute
end cmd[/tt]

Think you'd need to "declare" the parameters in the query (Query menu, Parameters).

Roy-Vidar

 

[sjh]

Thank you so much for your response!

One small question. After the Execute statement, how can I read the returned recordset?

Thanks!
SJH

 

[EBGreen]

This should work:

Dim Dim myRs As New ADODB.Recordset
myRs.Open "qry_Test", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Where qry_Test is an existing query in the DB.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]