Ad-Hoc Query Builder in Access

[rangudu_2008]

Aaron,
I want a clear explanation about what u are suggesting me to do...

Ranga

[JimmyTheHand]

Ranga, try this one.

It's how I envisioned this Join-builder thing. I had to change the user interface a little bit to suit my plans, and also added a few tables for testing purposes.
The table called "TableJoins" is necessary for the form to work, it contains the saved joins the SQL is being built from. The other tables are just decor.

On the form, the new listbox contains the saved joins (the same as table "TableJoins"). I programmed buttons Save Join, Remove Join and Build SQL. This latter creates and displays the FROM clause of the SQL string.
Also, upon loading the form, the comboboxes corresponding to left and right tables are prepared.

HTH

Jimmy

[stanl]

My .02

I've had to build QBE's for Access, Oracle, SQL Server - and your best friend is the ADO Openschema() method. With that you can build tables of tables (w/columns and keys) then build your QBE around the relationships of these pseudo tables (and since the Openschema() columns are generic, so is your QBE)

Stan

[tigerwt]

Hello,

I have found this topic occasionally while googling.

Our company uses the product similar to the one you are trying to create.
It is called EasyQuery and it works exactly the same way you propose: it hides all database related information (such as table joins) from the end user and provide friendly interface for query building.
I can not place a link to its home page since it is my first post but you can easily find it through Google.

So I think you can take a look at it and maybe get some ideas from there for your own solution.
By the way, they have ready-to-use ActiveX edition (named Active Query) but I do not know if it works with Access (we use .NET edition of this product).