Generate SQL FROM clause on-the-fly using code

[Rascelon]

Does anyone have code to generate on the fly a SQL FROM clause based on a list of user chosen tables?

I am creating a database for my boss that has a form that is an attempt to make a more user-friendly version of the Access Query Builder Environment. It is a simple multiple step form to help the user generate a query.
(Select tables and fields -> Adjust Joins if necessary (inner,right,left) -> Where clause -> etc.)
Obviously the functionality of my form is not as robust as the Access QBE but it is better than a form selecting from a preset query.

My problem, however, is figuring out an adequate algorithm for generating the user's FROM clause. My algorithm is adequate for really simple Parent->Child relationships in a hierarchical sequence but it doesn't work when I have say Parent1 -> Child1 and Parent2 -> Child1 (a tertiary relationship) or when one table is has a recursive relationship.

It seems to be a matter of getting the order right but the possiblities are more than I bargained for.

I could post my code but I'm thinking that I will have to start from scratch with a new algorithm.

Any help is greatly appreciated. Thanks.

 

[UnicornRainbow]

Look at thread702-817933

and go to the last post in the thread for the correct link.

 

[Rascelon]

Thanks for the link. The search form works well but unfortunately won't allow more complex queries where data are pulled from more than one table (i.e. when table relationships have to be expressed in the FROM clause).

What I want to do may be too difficult for my skill level but maybe if there were a way to make use of how Jet determines the logic or order of the FROM clause.

Does anybody else have any suggestions?

Vince

 

[UnicornRainbow]

If you create queries with the appropriate joins and without criteria, your boss should be able to get the results you need.

Give the queries recognizable names like
SalesPeople_And_Their_Revenue
SalesPeople_And_Their_Customers
Customers_And_Their_Spend

 

[Rascelon]

That is certainly a solution but not the flexibility that I was looking for. Thanks for responding though.
Vince