Ad-Hoc Query Builder in Access

[rangudu_2008]

I'm trying to code a macro in Access where i can build any type of query to query the database, whcih can be saved and executed when required. Also i wanted to export the data to an Excel sheet.

I started coding this macro and ended up sucessfully to a point where can build only select queries and export them to Excel. I used Access 2003.

Can anyone help me with the coding where in i can build and execute join queries too? I want to change this interface completely and should also be able to choose multiple tables to build join queries by choosing selected fields of a table (like a wizard interface), use comparison operators like NOT, AND, OR, sort fields, group by, order by functions like in the screenshots attached.

I am entirely new to coding macros in Access. I know VB coding and can do it Excel to some extent.

PLEASE :help

[matthewspatrick]

rangudu_2008,

With respect, what you are asking for is considerably difficult. Given that Access already provides a reasonably user friendly query designer, what is the value added for your desired approach?

[BlueTick]

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH%20Query%20By%20Form'

Duane Hookom created something similar...


The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (datasource) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use.

[rangudu_2008]

I need to build one for doing some MIS work..

BlueTick,
As i said i'm new to coding macros in Access... I don't understand how to use it or what the Query Builder does...

Patrick,
Glad to have a reply back... I had been racking my brains on how to start coding on a ad-hoc query builder in Access.

I want this code for a MIS work that aids something that is process-critical at my work. Its a part of automation (that is managed through some excel macros right now), and as per a discussion, they feel it would be safe if the entire thing is coded in Access.

I can have the front-end designed in Excel. That would REALLY help me in this rework that is required on this project, since a major part of my front end would go unchanged. But the main thing is that it should be really stable and safe. Even if the macros do mess up while in execution (which should never happen), the data should be safe in the database & the code that's running (say executing some query) should do a rollback.

Is it possible to have the front-end in Excel?

This whole automation should also be easy to use and migratable in case of process changes (i mean changes to the database as well as front end design should not need a rework on the code, atleast it should be minimal).

Ranga

Patrick,

It would be really a great help if i am able to successfully finish this work. I had already coded a part of this work which works fine. But i cannot share it in this forum coz of confidentiality.

Can a macro be coded in such a way that it can do a rollback? If it can be done, i would like have that code so that i can add it to the coding i've done and implement the same for this MIS work as well.

Ranga

[CreganTur]

Can a macro be coded in such a way that it can do a rollback?

That's an easy one. It's going to involve 2 major steps.

A) Create a backup of your table

Go to the Queries tab of your database (in Access)
Click New
Select the table you want to back up
Select the asterisk (*)[this selects everything in the table]
Change the Query Type to Make Table- a window will appear
enter the name of the new backup table you want to create
Click the red exclamation point (!) to run the query and create your backup.2) Create rollback query

Go to the Queries tab of your database (in Access)
Click New
Select the backup table you want to roll back from
Select the asterisk (*)[this selects everything in the table]
Change the Query Type to Make Table- a window will appear
enter the name of the existing table you want to roll back
Click the red exclamation point (!) to run the query and roll back the table to the values of the backup.If you want to use VBA to kick off these queries you would use
DoCmd.RunQuery "QueryName"
Where "QueryName" is the name of the query you want to run

[rangudu_2008]

Dear All,
Thank you all very much for the replies i get...

I've included a sample database in which i've included the coding of the QB (that i sent first) where i can build only select queries & export the results to an Excel sheet.

I want to fine tune this code to build and execute join queries as well, infact i wanted it in such a way that any type of query can be built.. I also wanted to have an option to save queries that are built to the DB & executed as and when required just by choosing the query name at runtime.

I like the idea of the backup table concept (but not in the same DB), can anyone include the sample code (in Excel) that can execute a backup query from a sheet and save data in some other database in case some error occurs while the macro is in execution?

I had already coded for a part of this MIS work in Excel which works fine. I used up the code available in these links (with few modifications):

http://www.erlandsendata.no/english/index.php?d=envbadacexportado
http://www.erlandsendata.no/english/index.php?d=envbadacimportado

I want to change the query builder interface like in the screenshots since i want to make the QB interface a lot more easier and user-friendly...

It wud be of GREAT help to me if all this work is done in Excel (with connectivity to multiple databases in Access through ADO) as the amount of rework (in designing) to be done will be reduced.

I'm a bit comfortable with Excel macros than Access, so i wud like all this coding to be migrated to Excel and i had lots of difficulty in developing it..

Can anyone help me in converting this QB interface and codings like those in the screenshots and include all the new features that are required? (all to be done in Excel)

PLEASE :help

[rangudu_2008]

I had been racking my brains on coding for an ad-hoc query builder in Excel that will be used for building SQL queries in Access. :banghead: :banghead:

It is required for a MIS process at my work which aids something that is process-critical.

I've included a sample database in Access in which i've included the coding of the QB which i started developing and finished it with lots of difficulty.
It can build only select queries & export the results to an Excel sheet.

I'm a bit comfortable with Excel macros than Access, so i wud like all this coding to be migrated to Excel in which i want to choose Access databases and build queries.

I want this code to be modified in such a way that it can build and execute join queries as well. In fact i want it in such a way that any type of query can be built.
I also wanted to have an option to save queries that are built to the DB & executed as and when required just by choosing the query name at runtime.

I want this query builder interface to be migrated in Excel like it is in the screenshots, since i want to make the QB interface a lot more easier to handle and user-friendly.

Can anyone help me in converting this QB interface and coding like those in the screenshots and include all the new features that are required?

[OBP]

By having the front end in Excel you are Voiding and Negating all of the major advantages of Using Access. You do not need a fancy query builder for 99% of the things that the users would need because they can all be created in Access and selected from Menus. If you really want an Excel based Query Builder you would be better off posting on the Excel Forum.

[rangudu_2008]

OBP,
I just wanted to code the query builder in Excel coz i was a bit more comfortable with Excel macros than Access... If it can be coded in Access its fine for me as long its commented & well explained..

Can u help me out and guide me how to design the forms and other things as in the screenshots and code for the same in Access?

[OBP]

rangudu, you are way ahead of me with the code that you have already created in the QB database. :bug: I am much more Access based than VBA.
I would probably have approached it slightly differently to you by using Table and Query Defs. But in my experience of VBA generated SQL having so many Syntax problems, especially in Where statements I think you have a major task on your hands.
I could help you design the Forms etc but not the VBA, as I said you are far better at that than me. :bow:

Looking further at your code I see that you are using Table Defs, I am very impressed with what you have so far and your "English".

_______________________________________________________

rangudu, I am extemely impressed by your VBA, especially this -
strWhere = strWhere & strJoinType & Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("txtVal" & i) & "")

which I have never seen before and appears to overcome the problems of the Where syntax problems that I mentioned in the previous post. It works with Dates, Check Boxes as well as text. :dunno
However I do have a few concerns about the actual Database design, especially the Tables and Lack of Key Fields and Relationships.
Is this an actual Database or just something to use as a demo for the SQL Build Form (as it says in it's name)?

[rangudu_2008]

OBP,
It is not the actual database that i'm using. I just attached the QB form with it and posted it here.

I got the idea for that form design from a reference link while i was browsing the net. I designed it simply (with the help of an online demo) just to learn Access VBA and developed that code. I had lost that link reference which i had. Safely, i had this code in one of my CDs. I want to use it for the MIS work that i'm entrusted to.

Now, it seems i've to start from scratch again for my MIS work.

I had lots of difficulty in coding for this query builder. I'm trying to modify it in such a way that join queries too can be generated.

I'll be using that code in my query builder form which i want to design as in the screenshots, inorder to build Access queries.

I've got the general idea about the query builder form design which i can design easily in an Excel Userform, but i need ur help to do the same in Access.

Can u plz help me with the design of the forms in Access as in the screenshots and post it here?

[OBP]

What is MIS?
I am working on 2 other databases at the moment, so I do not have a lot of time to devote to this, but I will give it a try if you are not in too much of a hurry.
The forms, based on your examples should not be too difficult, do you have them in Excel?

[rangudu_2008]

MIS is the short form of Management Information System.

It is a project that i have to develop inorder to generate some reports based on the info stored in the database.

The query builder that i wanna build is one of the modules in this project. I've just started with this part of my work coz it is one of the core parts of the backend that i need to code for. Once this is complete, i just have to design & code my front end as per the needs of the users of this database and just need to integrate the QB code with it.

As for designing the forms, i want to design them as in the screenshots.. I'm not in a hurry to finish it.. Once the design is complete, the thing that is left is to just migrating the code that is already there appropriately to that new forms..

[OBP]

Are those Screenshots of actual Forms in Excel or just something that you have drawn?

[rangudu_2008]

Those were the screenshots of the form design (an online demo) which i got while browsing. I had lost the reference link. Its just like the one which i want to design for this query builder.

Anybody there to help me with the design?

OBP, where r u?

:help

[OBP]

Rangudu, I am sorry I have been busy on 4 other databases. I will try and make a start on yours tomorrow. :blush

[rangudu_2008]

Good to hear from u OBP... U can call me Ranga.. What's ur real name?

Is it possible to modify the existing code to build join queries as well? If it can be done, it wud really be great...

Ranga

[Oorang]

A word of caution, apiSortStringArray does odd things in AC2003. It will still execute but it does not actually sort things in order.

[rangudu_2008]

Dear Aaron,
Thanks for ur reply. Its glad to hear from u.
This code is a bit old which i coded some time back with much difficulty when i started learning Access VBA and i'm planning to upgrade it.. I wanna redesign the forms as in the screenshots as i require it for some official use.

Can u help me with the design and adding more code later as i've described in detail in my previous posts to this thread?

Ranga
__________________________________________________________
Aaron,
I'm planning to build an interface (that wud the first form) like in the Ad Hoc Query Input screen. I should be able to select criteria like in Criteria Selection and should also be able to build join queries like in the join queries. The finally built query should be displayed like in Auto Built Query which can be saved and executed on demand.

I know this is bit hard to understand what i've described above, but it can be easily understood by reading this while u view the screenshots side-by-side.

I also understand that my code will undergo a lot of modifications and new additions.

Looking forward to a helping hand,

Ranga
__________________________________________________________
This query builder that i'm trying to build should be able to import data from tables of similar structure from multiple Access database files.

If u don't understand what is said above, here's an example:

Say there are 3 different Access databases all with similar structure and no. of tables named A1.mdb, A2.mdb, A3.mdb.

My query builder has to pick up data from these database tables and consolidate it to a master database each day. That data that is consolidated and stored in the master database should not have any duplicates.

Looking fwd to a helping hand,
:help :help

[JimmyTheHand]

Ranga,

You said you wanted to improve the code, so I have a suggestion. There is that large and complicated GetOpenFileName function you use. I would use something like this, instead:

Dim EX As Object, FN As Variant
Set EX = CreateObject("Excel.Application")
FN = EX.GetOpenFileName
If TypeName(FN) = "boolean" Then
MsgBox "cancelled by user"
Else
MsgBox FN
End If
I suggest using the same method with GetSaveAsFileName, as well. I don't know if it's faster or more efficient, but surely it's easier to do.

Concerning your expressed question, I can't say anything yet. I'm still cathing up with the logic of the code. Maybe later I can suggest something here, too, but don't take that as a promise.

BTW, there are some amazing things in your code, which I have never seen before. Thanks for sharing it. :thumb

Jimmy

[rangudu_2008]

Hey OBP,
Kindly have a look at the flash demo which i used to design a form for the query builder which i'm trying to build... I've so far been able to build only simple SQL queries with WHERE clause in my code as you know... I wanna add more functionality thru code as in this demo (to build join queries)...

Please please :help :help :help :help

I'm wondering whether filters can be applied to tables thru coding? How can it be done? :think:

[OBP]

Ranga, due to being so busy with my prior commitments to give your thread the attention it deserves I have aske Jimmy the Hand to have a look at it.
He has developed something similar to you and he is really great at SQL, so he should be able to sort out your union queries, so I would respond to his post if I was you.
He can learn some new things from your code I did and hopefully help you with the SQL side.

[Oorang]

Jimmy, while that is indeed fewer lines of code. I think you will find it substantially slower than using a normal file dialog, as it is actually starting excel behind the scenes to use it's dialog.

[rangudu_2008]

Hey Jimmy,
OBP says that u've developed something similar to my query builder.. Can u post it here?
I've also done a new form design using that flash demo i've posted here.. I wanna add filtering option & implement aggregate functions as well...

Ranga


__________________________________________________________
Is anybody out there to help me with the coding for the new design i've made?

:help :help :help

[JimmyTheHand]

Hi Ranga

The design OBP mentioned I did was not exactly like this. It was building queries, all right, but on a very limited scale. More like changing field selections and "WHERE" clauses, based on a table or existing query. Never a "JOIN" in them.
I can upload it if you insist, but it won't be much of a help here.

Now, I don't think I'm any better at Access or coding than you are, so don't hold your breath. But maybe we can work out something together.

I understand the current step of your project is building the "FROM" clause of the SQL. The first question to ask is whether you want to do nested joins. (I guess you do, but I prefer to ask, anyway.)

The 2nd question is whether you want to limit the number of possible joins.

Jimmy

[JimmyTheHand]

An algotrithm should be found to check circular references in the saved joins.
As I see it now, the join structure of a database must be strictly linear, which means that, from table1 to table2 there can only be maximum one route along join lines. (I'm not familiar with technical terms, but I hope it's clear what I want to say.) In other words, starting from table1, and going along join lines, you must not be able to get back to table1 (except by turning back, of course).

Saving the joins ('Save Join' button on the form) should be done in a way that supports this algorithm. I have been thinking about the algorithm, but no results yet. I suspect it's related to graph theory, but I'm no matemathician. Do you know about graph theory?

UPDATE
Found a thesis in garph theory about acyclic graphs. My understanding was that the joins don't have circular reference if
[number of joins] + 1 = [number of tables]
(Naturally, if two tables are joined by more than one pair of fields, that still counts as one join.)

I hope I didn't misinterpreted it. I would be glad if someone could confirm this... Because this condition seems quite easy to check...

[rangudu_2008]

Jimmy,
I want to do nested joins, but i suppose it would not be more than 3 tables at a time (at the max. it wud be 2).

I have these questions for u... I want u to answer them one by one...

1. What do u mean by limiting the no. of joins? Can u explain it in detail?
2. If the join structure of a database is linear, how can any saved join be used? I meant to ask, how can a join be built using a join that is temporarily saved?
3. I get ur idea in using graph theory for that algo, but if joins can be saved (temporarily) to the database, how can they be used to build the required query?

Do u get any ideas from that flash demo that i attached earlier?

Ranga

[JimmyTheHand]

1. What do u mean by limiting the no. of joins? Can u explain it in detail?
First I tell what I mean by JOIN. For me a JOIN is a connection between any two tables. If I connect 3 tables, that is 2 joins: One between tables A and B, and one between A and C. (Or B and C).

By limiting the number of joins I mean restricting these connections (essentially restricting the number connected tables) to a certain value. E.g. not allowing the user to join more than 4 tables.


2. If the join structure of a database is linear, how can any saved join be used? I meant to ask, how can a join be built using a join that is temporarily saved?

I'm not sure I understand the question. I'm trying my best to answer.

By "linear" I mean that between the tables there is no loop. (Or cycle or circular reference or whatever it is called. I'm sorry, I don't know the right words, even in my own language.)
By this definition, the letter 'X' is linear. The letter 'A' is not. You start walking at the very top of 'A' , and can get back to the same spot without ever turning back. It means there is a loop in the letter 'A'. And that loop is the upper triangle. In letter 'X' there's no loop. A query builder must check whether or not the joins defined by the user make a loop.

Now. In Access query builder the user has a nice graphical interface where they can display all the tables and joins they want. But with your form, the user can select and display only one join, and two tables at a time. I tried to imagine how multiple joins can be created this way, and came up with the idea that these join definitions should be temporarily saved, one by one, in a properly designed array variable, or collection, or something. This way the user defines all the joins they want, without any actual change made to the database. When they have finished, they click on a [Build Query] button. And then the code iterates through the saved join definitions, and builds the "FROM" clause of the SQL.

How did you plan to do it?


3. I get ur idea in using graph theory for that algo, but if joins can be saved (temporarily) to the database, how can they be used to build the required query?
I think this was covered in Answer #2. But to make it more clear, I didn't propose to save the joins to the batabase, even temporarily. I proposed to save their definitions to an array or collection. (By the way, have you seen the update to my previous post? )

I feel this idea getting crystallized. How is it with you?
I think the next step (or substep) would be discovering the best way to (temporarily) store these join definitions.


Do u get any ideas from that flash demo that i attached earlier?

Yes, I did. One was the criteria builder that even OBP marked as excellent. Another example was using Acwzmain to obtain a unique query name. There were some others, but right now I can't recall them. Why do you ask?

Jimmy

[OBP]

Hello, guys, I have been doing a bit of work on "Joins" in VBA and I have come up with some things that might help you.
Assuming that you want to use the established Relationships for your Joins you can use the System Table "MSysRelationships" to find the "names" of the Relationships and which Tables and Fields they are joined by. This data can be included in your SQL String along with the Join type.
The syntax for the Join part of the string appears to be quite straightforward as shown by this excerpt from Access 2000 VBA for beginners.

If chkIngredientID Then
sFROM = sFROM & " INNER JOIN tblIceCreamIngredient i " & _
"ON s.fkIceCreamID = i.fkIceCreamID "
sWHERE = " AND i.fkIngredientID = " & cboIngredientID
End If

They have a Query Building Form in it that uses the above Join when the "chkIngredientID" check box is ticked, which means the inclusion of another table

If you want me to explore this any further let me know.

[JimmyTheHand]

Assuming that you want to use the established Relationships for your Joins you can use the System Table "MSysRelationships" to find the "names" of the Relationships and which Tables and Fields they are joined by.
Tony,
I don't know whether the OP wants to use predefined relationships or ad-hoc created links in the query. That's his job to decide. My personal feeling is that he wants the latter, because otherwise he wouldn't want the user to specify a 'join type' in the query builder form, since that info is already part of relationships.

In case of 2 tables, SQL syntax for JOIN is very straightforward, yes.
What troubles me is the case of 3-4 or more tables, which looks a little less easy. Just choose any query in one of your databases that has several joint tables, and take a look at its SQL string. You will see lots of round brackets. If you remove them, Access will give a syntax error message, so it seems that brackets are obligatory.

I still couldn't quite grasp the logic behind the rules of using brackets, for sometimes there are more of them than what seems reasonable. Also, I'm confused about the order of tables, as they succeed one another in the SQL.

Unfortunately, the code you provided doesn't use brackets at all, and doesn't tell anything about table order. I personally am very curious how the source you referred to does these things. You can explore, if you want, or you could share a link for that source.

Jimmy

[OBP]

Jimmy, I will email you a copy and an explanation.
My concern with AD Hoc joins is that you could match up completely unconnected fields, whereas using the Related tables you at least know that the data types and content must match.
You do not necessarily have to use the same "Join" properties as the Relationship, just pick up the Table and Field names. I have written a routine, based on the Access VBA help topic that does that. See Form1 in the database that I have sent you.

[rangudu_2008]

Dear Jimmy and Tony,
I'm happy about all the support i'm getting on coding for this query builder...:clap: I've the feeling that i'm at home right here @ VBAX... :thumb

Right now i'm trying to get some reference books for this that wud help and lead us in the right direction for building that algorithm but i'm sorry that i'm not successful so far... You guys are more well aware of the requirement on the coding to be done than me... I just trying to think of a better solution for designing a workflow for that algorithm which Jimmy proposed... :think:

However, i came across some other forum while browsing; Maybe u guys can find something useful here... : pray2:

http://www.utteraccess.com

P.S.: Can any of u guys copy me on that mail?

Ranga

[OBP]

Ranga, can you clarify what kind of Joins you want to use, ie Related fields already set up by relationships or allowing any fields to try and be joined?
If you private mail me you email address I will send you the database that I sent Jimmy.

[rangudu_2008]

Tony,
The join queries need to be built based on the primary key fields of any two tables at a time.

The joins need to be saved at an intermediary stage that may be used as subqueries in another query...

Plz have a look at that flash demo attached earlier.

I've explained this more mathematically using set theory concept.
Plz refer to the attachment with this post.

Ranga
__________________________________________________________
Tony n Jimmy,
Probably any of u guys can get ideas from Oorang on this algorithm...
(I don't his real name:dunno)

Ranga

[Oorang]

rofl omg Please don't make me read the whole thread what was I helping on? (It's Aaron btw)

[rangudu_2008]

Aaron,
I had developed a code for a query builder some time back which can build simple SQL queries... I would like to use it up for some MIS work (Management Information System) so i would like to improve the code.

What needs to be implemented in this new version is to build up ad-hoc join queries through VBA code. A join query can be built with more than 2 tables that can be used as part of subqueries as well.

To build join queries more that can combine more than 2 tables, we are thinking of a solution and we wanna develop an algorithm. :think:

U can see the basic code that i developed, the new form design i?ve made and a flash demo that shows what needs to be implemented through VBA in the previous pages.

[OBP]

ranga, I am afraid the maths and algorithm concept leaves me cold.
I prefer to just "do it", it might not be the best way in the end, but I prefer "doing" to planning.
I would just combine the 2 methods in the database that I sent you. One identifies the available Indexed Key fields for each table and the other provides the method of joining them. I haven't looked at joining more than 2 tables though. But it can't be that difficult to produce some Queries that provide the SQL to do that.

[Oorang]

Hi Rangudu,
I'll be honest I haven't really been following the thread closely, but why can't you just use a "Natural Language" approach? Where the interface has five drop downs "Table1 Field1 Join Table2 Field2". Then just let the center drop down be the join type. So just reparse that info into a SQL statement. "From Table1 Join Table2 On Field1 = Field2" if you want to get really fancy you can add a drop down box for the relational comparison operator, even Access's query builder doesn't do that. Access only has 3 joins anyways Inner, Left (Outer), & Right (Outer).

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

[rangudu_2008]

Jimmy, you're GREAT... :clap: :bow: :thumb :cool:

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. :dunno

Secondly, I'm :wot 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 (http://support.microsoft.com/kb/q191713/) 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 (http://www.mztools.com/v3/mztools3.aspx)" 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
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. :thumb


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.