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

[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

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

2) Create rollback query

1. Go to the Queries tab of your database (in Access)
2. Click New
3. Select the backup table you want to roll back from
4. Select the asterisk (*)[this selects everything in the table]
5. Change the Query Type to Make Table- a window will appear
6. enter the name of the existing table you want to roll back
7. 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
[VBA]DoCmd.RunQuery "QueryName"[/VBA]
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/...badacexportado
http://www.erlandsendata.no/english/...badacimportado

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

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

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

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.
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?

[OBP]

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

[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,

[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:

[vba]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
[/vba] 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.

Jimmy