I know there are no querydef or tabledef objects in ADODB. In the past,
I've used DAO to create Oracle pass through queries like the following
so that I can use these queries as list or combo box row sources. In
the following example, a DSN connect string is used in DAO.

The result is a new pass through query being displayed in the queries
tab of the current Access db.

How do I do something similar in ADODB? I can figure out how to open
recordsets, and use the execute statement to run action queries. but
can't figure the following out.

Any pointers would be much appreciated, thanks in adavance.

function fCreateTradeList(cConnect as string, strPtqName as string)

'creates pass through query using
'strPtqName name of query to be created
'cConnect constant representing appropriate Oracle connect string

dim dbs as DAO.database
dim qdf as DAO.querydef
dim strSql as string

strSql = <construct an Oracle SELECT statement>

set dbs = Access.currentdb

set qdf1 = dbs.Createquerydef(strPtqName)

with qdf1

.connect = cConnect
.SQL = strsql
.returnsrecords = True

end with

qdf.close
set qdf = Nothing
dbs.close
Set dbs = Nothing

Exit Function
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

"Tim Marshall" <TIMMY!@antarctic.flowerpots> wrote


When you say 'do something similar' does that mean you want to stick with
creating stored queries in an access database, or could you move to creating
stored procedures on the server? Although you can use ADOX to create
queries, these will not be visible from the database window (see
http://msdn.microsoft.com/library/te...adocreateq.htm for the gory
details) and the article recomends uing DAO instead. Was there any specific
benefit you were looking for, when dumping the DAO code?

Eric Schittlipz wrote:

Stored queries is what I'm looking for. I need to be able to use them
to populate list boxes and combo boxes and datasheets... I don't think I
can do this with, say, an Oracle saved view (query)?

Thanks, I'll check this out.

I'd actually prefer to stay with DAO as I'm very comfortable with it.
Mainly I'm looking to be able to have DSNless connections to my Oracle
database to make distribution of my Access apps (various front ends for
the Oracle database) a little easier.

BTW, you may recall helping me with a screen refreshing problem I was
experiencing in September (in Google it was http://tinyurl.com/4whyw),
when I first started experiemnting with ADO. It was interesting that
using the same approach, the MS telephone support people experienced the
same problem. I was told that for Jet (I was using Jet for that issue,
not Oracle), DAO is better to use than ADO as DAO is native to Jet.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

"Tim Marshall" <TIMMY!@antarctic.flowerpots> wrote


While I don't have much experience with Oracle, I'm pretty sure you can.
Certainly with SQL Server, if you want to optimize perfomance and retain
better control over what users do with the data, then you move away from
pass-through queries and transfer them to stored procedures. Stored
procedures are stored on the server and so it 'knows what to expect' and 'is
ready to run the code' - ie there is some execution plan stored in advance.





I've not gone through the code but I've seen it mentioned in the group, I
don't know if that might help
http://members.rogers.com/douglas.j....LessLinks.html


I remember. I still can't see why the code wouldn't work, but I guess
you've found a solution.

Cheers.