Ad-Hoc Query Builder in Access

[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).

[rangudu_2008]

Jimmy, you're GREAT...

The things that need further fine tuning are as below:
1. We cannot view all the joins that are saved (one each at a time);
2. Only the join part of the query is displayed in the SQL Query textbox (output query that is built) instead of the whole SQL statement; This query needs to be saved back to DB.

Also, the end user should be able to select fields from diff tables (using the first tab) which can be re-ordered and the SQL query should be auto-built in this case as well. This needs modification of my old code. The criteria tab needs to be coded simultaneously when this is done. I need ur help and guidance for accomplishing it.

Only when these are done, the query results can be exported as excel sheets.

Ranga
__________________________________________________________
Aaron,
Please comment on the things that have been accomplished so far...

__________________________________________________________
Aaron,
Please comment on the things that have been accomplished so far... And also share your ideas on the things that need to be done. Guide us.

Ranga

[Oorang]

Hi Ranga,
Here is a very simple example of what I had in mind (see attachment below), obviously you will need to expand upon it considerably to make it production worthy.

Edit: JimmyTheHand PM'd this to me and I thought it needed explanation:

Aaron,

Studying the code you posted in Rangudu_2008's thread (post #44) I noticed an interesting part:

#If Not m_blnErrorHandlersOff_c Then
    On Error GoTo Err_Hnd
#End If

First of all, I couldn't find m_blnErrorHandlersOff_c defined, not even dimensioned, anywhere. What is it? I thought it was a constant, but then its value is known, so why the If..Then.

Secondly, I'm about the # character. I don't remember seeing code lines started this way. What does it mean? Can you explain this to me is a few words, or give a link mayhaps?

Thanks,

Jimmy

First let me explain the meaning of the mysterious "#". "#" as a suffix (ex: Dim x#) is a type declaration character and is the same as "Dim x As Double". (They are bad practice, don't use them) As a prefix (ex: #If foo Then) it is a "conditional compilation directive". To understand whatthat is, you need to know a little about the guts of vba.
Unlike some languages, VBA does not compile in Machine Language for execution. VBA "compiles" into PCode for execution by a virtual machine at runtime. It is the P-Code that gets executed not your nice friendly VBA. So when a procedure is called, your code is compiled into p-code and the p-code is what runs.
A conditional compilation directive is evaluated at compile time and if the condition is True, the code inside the #If block is compiled. If it is False, the code inside the code block is NOT evaluated and not put in the P-Code block.

So why would you do this? Well, speed for one. Say you have a whole bunch of debug.print statements that you only want to run if you have a constant set to True. If you use a normal If block the if condition is evaluated during runtime everytime it is encoutered, thereby slowing your code. If you use an #If block instead, the code that get compiled into P-Code is just the Debug.Print line (not the #If itself, that has already been resolved) or if the condition is false, the entire block dissappears from the PCode.

Ok so now you know all of that. Let me explain what that was doing in my code. I use a code productivity tool called "MZ Tools" it's free, it's awesome and I can't say enough good things about it One of the things it does is allow you to do is set up a standardized error handler that it will add to your code at the click of a button. On of the other things it does is to allow you to a standard module header. My standard Module Header looks like this:

' Module       : {MODULE_NAME}
' Author       : {AUTHOR}
' Date         : {DATE}
' Purpose      : 
' References   :
' Dependencies :

Option Explicit
Option Private Module
Option Compare Binary
Option Base 0

'Setting this to True will turn off all error handling:
#Const m_blnErrorHandlersOff_c = False

 
My Standard Error Handler looks like this:
 'Conditionally Invoke Error Handler:
#If Not m_blnErrorHandlersOff_c Then
    On Error GoTo Err_Hnd
#End If
{PROCEDURE_BODY}
' Exit Procedure 
Exit_Proc:
    'Supress Error Handling to Prevent Error-Loops:
    On Error Resume Next
    'Release Objects:
    'Set Return Value:
Exit {PROCEDURE_TYPE}
' Error Handler 
Err_Hnd:
    '$PROBHIDE RETVAL_DISCARDED
    MsgBox Err.Description, vbSystemModal, "Error: " & Err.Number
    'Return to Exit Procedure:
    Resume Exit_Proc

[/quote]
In the code I created, I used the standard error handler, but not the standard module header. Mystery solved.

Edit 2:
So why did the code still use the error handler? Because if the condition is not found, it is assumed to be false and will therefore use the error handler. The only way to turn them off is to define a compiler constant (as in the standard header) and then set it to true. As a safety precaution, I made the condition that way in case I ever forgot to put in the header, or someone @ work copied it, and forget the header. As you can see it was a pretty good move (Always plan for your own fallibility )

[rangudu_2008]

Aaron,
This is exactly what needs to be done in my coding... Can u guide us how u developed this code and about how u wrote the macros?

The macros are entirely new to me... I've only coded macros in Excel.

Ranga

[Oorang]

I don't understand the question?

[JimmyTheHand]

Aaron,

Thanks for the detailed explanation. This is enough to get started.


Ranga,

There is something I don't understand. You now have quite a few clues and ideas, even full-blown code to work on and build into your own project. Yet, you just keep asking for more help and guidance. And your questions are far too general and hollow. I would expect specific questions on some part of the code or another, showing that you are involved and working on the problem. But I see hardly any sign that you made the effort to even understand what was freely given to you.

It was my understanding that you needed help with coding the JOIN part of the query builder. I provided just that, a working code to build a JOIN sequence of any number of tables. There was nothing special in the code, except maybe the logic behind it. With the coding skills you displayed in some of your attachments, it should be a childsplay to incorporate it into your project.

So why don't you get down to it? What is holding you back?

And what do you mean you've only coded macros in Excel. How could you develop those simple query builders you posted, without coding in Access?

Jimmy

[Oorang]

Jimmy, if my eye (and memory) doth not fail me, the DB he posted was just a modded version of one of Tushar Mehta's.