query def as datasource for a query

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

What is the syntax to use a querydef as a data source for
a second query.

I.e. I have a querydef for which the sql runs a stored
procedure on an Oracle server. How do I use the results of
this qrydef as the source for an append to table query
(the table is in Access.) Being a real newbie I don't know
how to make this work. BTW, I am using DAO 3.6

TIA
 
Create a new query, on the Show Table form click Queries tab and select your
query.
Close the "Show Table", select the required fields, from Query menu select
Make Table.

Alex.
 
Thanks for the reply. I should have been more specific in
my question. I have already tried that but for what I am
doing it won't work. The Oracle SP has a dynamic parameter
so that querydef is construtcted as a temporary querydef
in a VBA module using DAO. I see the querydef in the
immediate window and it is working. Now I want to use that
querydef to pass the resultset returned from Oracle to a
make/append table query in Access. So somehow I need to
set up the Oracle SP querydef as a data source for a TSQL
query inside VBA.

Any suggestions on how to do this?

TIA
 
I'm still not quite sure what you are doing.
What I suggest, statically create a pass-through query calling your stored
procedure with all the parameters hardcoded like
YourStoredProcedure 'It is the Param1', 'It is the Param2'
and name as you want. I will call it qryTest, for example. Then change the
sql property of your qrydef as needed.

Sub MakeMyTable()
With CurrentDb.QueryDefs("qryTest")
'Change the sql to whatever you want
.sql = "YourStoredProcedure 'Another Param1', 'Another Param2'"
End With
CurrentDb.Execute ("SELECT * INTO tblTets FROM qryTest")
End Sub
 
That is very similar to what I wound up doing. What I had
wanted to do was work with a temporary querydef. I could
not make that work as a data source for the second query
so I wound up creating a querydef named "tempQuery", use
that as part of the sql statement of the make table query
and then do a DB.QueryDefs.Delete("tempQuery").

I will have to consider your idea of not deleting the
QueryDef and just modifying the .SQL. Of course then I
will need to add the necessary code to create the QueryDef
if this is the first execution and/or somehow the QueryDef
was deleted.

Anyone want to chime in with their preference and reason?

Thanks again.
 
Back
Top