How to create custom query design ability

[Seth Schrock]

I'm creating a database for a business that wants the ability to generate queries and reports on their own without having to ask me every time that they want a knew one. My preference would be to create a form that allows them to select the tables, fields, and criteria that they want, save the query, and then generate reports off of that. That way, I don't have to give them access to the design ribbon where they can accidentally or intentionally make changes to my design. I plan on using SQL Server as the backend so I have easy access to system views that list all the fields in tables or views. Has anyone done anything like this before that might be able to provide a bit of a template or a game plan for how to begin? I have spent a little time on this several months ago and I think that it is doable, but it would be nice to hear from someone who has tried it before I spend a ton of time on it just to find out it really doesn't work too well.

[Rabbit]

What about making a copy of a blank query and then opening it in acViewDesign?

[Seth Schrock]

To provide the option to save the query, I could create a form that has you enter a query name and description. I could then create a querydef using code with the name provided and then open it in design view. I would then have a table of query names that they could open from a list.

My thinking wheels are turning. Not sure if that is good or not :) Thanks Rabbit. I'll get a test database going to try that idea.

Is there anyway to limit what kinds of queries that they can do, like a make table query?

[Rabbit]

I don't think you can limit what they can do in the query design. You could probably affect the query definition after they save it, but that doesn't prevent them from running the query in the designer. If you need that, you would need to replicate the query designer.

[jforbes]

I've worked on creating an Ad hoc report system (the Industry buzz word is Business Intelligence now) for a big ERP system and there were a few things that we had to take into consideration. The system we built wasn't in MS-Access, it was in a Third Party toolset and VB.NET, but I think some of the things we learned could apply.

• Technical competency of the customer. If you give them a tool that is too complex for them to use, they won't use it. If it's not complex enough it will be unusable. The type of user we found that was required to attempt Ad hoc reporting was someone that was highly technical (an not just thinks they are), but not quite a programmer. There aren't a lot of people that fall into this category.
• You will probably need to go through your tables and create Views in SQL for them with as many calculated fields that you can think of. Without them, your customers wont be able to make very detailed reports. Things you should add are summaries from other tables that aren't directly linked and as many flags that your customer can use to determine the condition for that record. Like, onCreditHold, waitingOnParts. .. the kind of thing that you would programmaticall y check for in your application. The Views will probably run slow with that many calculations, but Ad hoc reports can get away with running slower than the norm, it is in their nature.
• So, if you provide views to your tables, You could rely on MS-Access for linking of your Views, but I think you might get some un-expected results as Users can be quite creative on what they will attempt.
• Related to the previous bullet, we opted to create a Form to define the linking between Views. For validation and to define a one to many relationship or a one to one relationship (one to one can be tricky). Then we built the SQL on the fly when the report was run. We also did more than create reports with the ResultSet, we also put it into a DataGrid that had sorting, grouping and hierarchical grouping, and summary capabilities. So having the SQL built on the fly and not stored as a View provided flexibility.

I know this information is probably overkill for what you about to do, but you'll probably find out soon enough that this is a pretty deep hole.

Other things you might consider...

• There are quite a few third-party Business Intelligence tools out there written for SQL Server.
• With SQL Server 2008r2 and newer Microsoft includes reporting (SSRS/Business Intelligence). It's similar to MS-Access and pulls directly from SQL, so it would bypass your application and keep it safe. If you really want to get tricky, the reports are defined with an XML file, so you could use your MS-Access app to write the XML and deploy it to SSRS.

Gook Luck

[dsatino]

I've done this in the past, on two separate occasions using two different techniques. In both instances I used access as a front-end interface, a form to select the fields, and an SQL server as the back-end. It's been several years so I'm going to just have to give the general gist. One is going to far simpler to implement, but not as all-encompassing from a user standpoint, while the other will give them anything they want but it's an extensive project.

First, the "easy" one.
If you've been continually building reports for these people then you probably have a good idea of what they'll be looking for 90% of the time, which was the case for me. Basically I built a couple of views on the sql server that covered the 90%. I built a form in access that allowed to select the data set(view), fields, groupings, summaries, filters, etc. And then I wrote all the code necessary for the controls and the dynamic build of the sql that would query the selected view.

Now, the harder one.
Conceptually, this is actually not that complicated. It's just a lot of work coding for all the possibilities the user might try to achieve. You're going to be relying heavily on the system tables on the SQL server so you're going to have to figure out the exact names since I don't have access to one at this moment.

First, build two tables on the SQL server. One for tables and one for columns. I called them ui_Tables and ui_Columns. For both tables you want to have columns for the unique system id (i think they are from sysTables and sysColumns), alias you want the user to see, and an indicator on whether to make it visible to the end user. For progTables I also had a column for a sql alias since some of the table names are obnoxiously long.

Next I build a form that had two main list boxes to populate the requested outcome. These were for the user to select grouping and summary data they wanted. I don't remember exactly how these were populated, but I think I used the typeID of the column for the sysColumns table to determine that. Adjacent to these list boxes were empty list boxes that would populate as the user double-clicked items in the programmaticall y populated boxes. Whatever the user clicked into these two boxes determined the result set.

Every time the user added something via the two main list boxes, several things would happen in the background and result in adding/suppressing functionality in the user interface.
1. It would build out the sql statement. This was a dynamic function and was probably the hardest part because of the joins. Each part of the sql statement had it's own string function which I called get_SELECT, get_FROM, get_WHERE, etc.
They were all pretty easy to design, except the get_FROM.

To create the joins, I used the sysRelationship s(name?) table and wrote a process that compared relationships of both tables at the first, second, third, fourth level, etc until they joined. Obviously, once the first join is created you need to check if the next table is already included and skip the process. I seem to remember that getting the paretheses right was the hardest part.

2. Populated several arrays for easy access to certain items, like the list of tables being used in the sql statement.

I had another part of the form they could use for filters. Filter fields were limited based on tables that were in the sql statement.


Hopefully, that a good summary of the angles I took. The second one took a lot longer, but it was basically 100% extensible as long as all the table relationships were defined.

Also, the sql statement was generated in SQL Server syntax and was inserted into a single pass through query with the appropriate ODBC info saved to it. It runs a lot faster like this since the sql server does all the work.

[Seth Schrock]

Thanks jforbes and dsantino for your insight. I realize that this could be a bottomless pit on top of reinventing the wheel (never a good combination).

I'll use these ideas and tinker around. If I run into any particular problems, I'll post them individually in separate threads. If I do get something figured out, I may write an article about how I did it. If I give up on that, I'll go with the much simpler and perfectly valid idea that Rabbit suggested.

Thanks again to everyone for your insights.

[zmbd]

Seth,

I know this isn't the answer to your immediate issue; however, this may provide a temporary workaround until you have a better solution:

Something I've done is to make a table where I can store the sql text... it's the one time I'll use a memo field.
[tbl_customquery]![pk];[tbl_customquery]![fk_user];
[tbl_customquery]![qname];[tbl_customquery]![abstract][tbl_customquery]![qsql]

[tbl_customquery]![fk_user]![qname];create a compound indexed key... I know, it could be the primary, I just hate using compound keys from a programing point.

I then have a from that the user can make the request, which stores this in the [tbl_customquery]![abstract] (amazing how concise people can get in 255 characters), and their query's name along with sending me an email.

I will place the new query in the backend, and the frontend has code in the custom query form to look for the new sql and pull it from the backend to the local... this way, if the user PC drops a harddrive when they reload the frontend to their PC their custom queries pull up too.

I then pull the SQL text into vba and either temp add to the qrydefs and docmd.openquery or execute, etc... as needed. There's a bit of complexity in the custom query form ((^_^))


Another thing, I was playing with the ACC2010 version of the ubiquitous NorthWinds template to get a better feel of the Access Macro (I may have to make a hybrid SharePoint database - pray for me), looks like they are using macros to make the save; however, MS is saving the user's lookup query. I don't have time to dissect it at the moment; however, I am certain they are storing the user's query so there might be something their for one to "borrow" and translate into VBA.