Get SQL of query and any it's based on

[LarryJ]

Ok, I have what I think is going to be next to impossible, but I
thought I'd see what the brains around here might be able to come up
with. Bare with me as I lay the ground work, then get to the question.

I have an Access 2003 form which will show the user all the queries in
the database (that don't start with "qry") which are queries they like
to view ("Yearly Cost Export") and export to Excel. All they have to do
is double-click the query and they can see the results, without having
to bother me or get into the database window. When they need a new one,
I create it and that's it, they can access it.

Now, some of the queries have date ranges, so I wrote functions
GetFromDate and GetToDate to get those dates via a msgbox (which I
hate).

I have another nice form to let the user use a calendar control to set
the From/To date, but I need to figure out a way to show that form
before the query executes, if they choose one of those types of
queries.

SO I added some code to the Double-Click event which will check the
queries SQL looking for the "GetFromDate" function in the query. If
it's there, I then show the form before I open the query and it's
working nicely.

Here's where the problem comes in though. One of the queries they can
double-click is based on another query that uses the GetFromDate
function. The problem though, is that the SQL of the underlying query
the top level query is based on, is not exposed when I look at the
QueryDefs SQL.

Now, I guess I could run through the entire SQL string and look for
other queries, but some of these queries are very complicated and may
be based on many queries, so I'm hoping not to have to go that way.
Mainly because finding the query names in the sql statement, could be
very time consuming.

Do I have any other options?

Thanks,
Larry

 

[Duane Hookom]

I think you can implement the query by form found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. All you
would need to do is import the various objects and rename your queries from:
Yearly Cost Export
to:
<Yearly Cost Export>

Users can then select fields, set sorting and criteria, group by,... They
are a couple clicks from seeing their data in Excel.

 

[LarryJ]

Sorry Duane, I already built something like this for simpler queries
and the users are fine with that.

The ones I build could not be built this way b/c they usually contain
too many tables and paramters or are crosstabs and such.

Thanks anyway.

 

[John Spencer]

One option would be to add a table that contains the query names and whether or
not they need the parameters. Then use the code that checks for your functions
in the sql to also check the table of "UserQuery" to see if it says you need to
open the form.

Most of my reports are called from a form that uses tblReports to decide what
controls are available on the form. I have have a listbox that shows all the
reports and two comboboxes to show choices (Counties, Cities, VendorNames, etc)
plus two date controls to allow users to specify a date (or date range).

ReportsTable has fields
AccessReportName - Text - PK
ReportTitle - Text - Human readable name for report
Description - Text - show description of report
StartDate - Boolean - Show/hide start date control
EndDate - Boolean - Show/Hide End date control
Choice1 - Text - SQL for Combobox1 Row source
Choice2 - Text - SQL for Combobox2 Row source

When the reports form opens I populate the list box with the above data (hiding
all but the ReportTitle) and then when the user selects a report I can show/hide
the various controls based on what is required.

You should be able to use the same concept for your queries.