Layered Queries and Visual Basic

[Susan Owen]

Hi! I'm using layered queries in an application. The 2nd query uses the 1st query as its data source, and there is yet a 3rd query that uses the 2nd query as a datasource. So this thing is stacked 3 layers high!

1st query - queries a table
2nd query - queries the 1st query
3rd query - queries the 2nd query

Hope this is clear.

Now, I'd like to do this all in Visual Basic using SQL. I'm not sure whether to use record sets or query defs or both. I haven't had any success in getting them to layer. Does anybody have any experience or ideas here? I've searched help and feel like I've tried everything. Thanks!

 

[RichMorrison]

Susan,

My first thought is why do you want to change layered queries to SQL/VB code ? If it works why fix it ?

Second, you can refer to an existing query from a SQL string in a module. So you could build a SQL string that contains "SELECT stuff from query_2" etc.

Finally, if you are committed to converting to SQL/VB then you have to figure out how to flatten 3 queries into 1 in order to produce one SQL string.

HTH,
RichM

 

[Susan Owen]

Why flattening the query doesn't seem to be an option

Hi Rich!

These three queries, especially the first one, are extrememely long. And as far as I can tell, they cannot be shortened.

The second query is a parameter query for a date. It requires one date, whereas in my modification I have many dates. I could read the desired date to a hidden field in the form and handle it that way (that is my alternative) but I'd like to be able to use my own parameters in SQL.

So I can build my recordset on the 1st query that is fine. But then I have the trouble with the 3nd query, because I can't seem to attach to the 2nd one, which I need to have in visual basic because of complex parameters.

Is this making any sense?

 

[RichMorrison]

Susan,

Yes it makes sense although I don't know the full details of your process reaquirements.

By "flatten" I did not mean "shorten". I meant that query1 and query2 would be combined into one query.

Whatever parameters are in the first 2 queries would need to be combined into a merged single query. Depending on the complexity of your parameters this could be tricky.

An alternate approach is "sub queries". Try Access help or a good book on Access or SQL for information on that. This is also a complex technique.

So I guess I wind up where I started, if the layered queries work then leave them alone.

HTH,
RichM

 

[Susan Owen]

Thanks Rich! I'll look for sub-queries

I could use the queries, but the problem is that it will be a little "ugly", using a hidden "dummy field" on the form. And I'm constantly running into limitations with this SQL query think in visual basic (limit 10 lines, for example) and there also seems to be a size limit with a make table SQL. Not sure. I want to learn more about it and yes, I'll check out access help. It sometimes just takes the correct term to find things, and I was just out of ideas. Thanks for your input. I'll let you know if I figure out anything substantial here.