Programatic parameter query based on parameter queries

[Cynoclast]

I have a join query I need to run that takes in two parameters (startDate and endDate of Type Date) and it pulls two of its fields from two other parameter queries that use the same parameters. If I execute this as a saved query from the database window it works flawlessly. I'm assuming it passes those parameters on to its component queries since they use the same names.

The problem is I need to run this query programatically (so I can insert the parameters programatically. The problem is that by using the method of inserting the parameters with variables (ie "select * from tblTable where " & x & " = " & y) replaces those prarameters with static values and they're no longer really parameters and so they don't get inhereited by the other queries in the join. I figure I could use DAO's querydef object instead, but I'm trying to avoid DAO as ADO is set to supersede it. Also the other recordsets involved are ADO. Everything is contained in the same .mdb file (or linked tables within it).

I guess the concise question is: How can I build this join parameter query programatically so that the parameter queries in the join can inherit the parameters passed to the join query in ADO? Or do I have to use DAO querydefs?

90% of what I know about VBA/Access is self taught while working on the job, so I might be missing something that would make it easier.

 

[smercer]

Dont try to link the querys together. instead click on the "show table" button on the toolbar and select the queries tab. then choose the query that you want to add into this new query.

then select the fields you want like you would as with a table.

thats it.

Did that help? Please respond

 

[Cynoclast]

I'm doing this programatically. The query works as a saved query, that's not the issue. I think I got it working using ADO command objects (long, involved and messy seeming), but I'm not sure yet.

 

[Cynoclast]

I got it working. Storted parameterized queries in ADO are a bitch.

 

[Pat Hartman]

but I'm trying to avoid DAO as ADO is set to supersede it

- DAO is optimized for Access/Jet tables and will not be superseded by ADO since there are things that ADO just can't do because it is intended to be a generic tool to access any ODBC data source. If you'll notice, DAO is back as the default for A2003. Microsoft made ADO the default for A2K and AXP but never implemented all the DAO functions and never changed their own code to use ADO so DAO is back

 

[Cynoclast]

How lame. Yeah I noticed lots of nice things ADO can't do that DAO can, such as querydefs and parameters are much easier in DAO.

Oh well, I got it working in ADO, I'm not going to go break it now.