Documenting Queries (97)

Topic

New Reply

Is there a way to use code to document the tables, fields, and criteria of a query? I would like to store the data in a table so reports could be generated.

The database I am working with has all the major query type, Select, Delete, Append, and Update.

Thanks for your help

Reply | Quote

Replies

In Access 97, you can already use the built-in Documenter (Tools –> Analyze –> Documenter) to generate a bunch of information in a report and then select Save asTable from the File menu to generate an Object Definition table. For some obscure reason, they removed the Save As Table capability in Access 2000.

Otherwise, you would need to create a querydef object and a recordset object in code and then step through its properties collection of the querydef to collect that information and write it to the recordset.

Reply | Quote

I have code that loops through the querydefs and puts the source table and fields in to a table.

Two limitations this code has is it only reports fields for Select queries and it does not report criteria.

Are these available through the proprties? I have not been able to find a way to get this info so far.

Thanks for you help.

Reply | Quote

You can extract criteria from the SQL by parsing out the WHERE clause. Each querydef has a SQL property that will return the entire SQL statement for the query. You can also examine the parameters collection of the querydef for parameters (criteria) that have to be populated, i.e., [Enter CustomerID], etc.

I don’t know why your code would only handle select queries unless you’re filtering them by query type. You won’t get any fields for action queries because they don’t return rows, but they all have a SQL property.

Reply | Quote

Any chance either one of you could elaborate on this topic a bit more? This sounds like exactly what I need to do:

I have code for ONE QUERY that loops through a list of sales reps and applies each name in turn as the criteria and exports the report under that person’s name. I’ve been trying to add an extra loop that would go through the first query, loop through that and generate each person’s report; THEN go to the next query and do the same thing, etc, etc. The rsCriteria loop works perfectly when I type in the name of the queries/reports as text strings.

I obtained and modified the original loop code from Roger’s Access Library (PrintingSpecificReports) : he’s labelled that “Expert Level” and I ain’t nowhere close to that.

Here’s my lay attempt at adding that extra loop – would anyone be able to point me in the right direction for correcting this. The error happens at the Set qdf line “Error in FROM clause”.

Thanks in advance.

rsReports.MoveFirst
Do Until rsReports.EOF

rsCriteria.MoveFirst
Do Until rsCriteria.EOF

strSQL = “SELECT * FROM rsReports![QueryReportMain] WHERE ”
strSQL = strSQL & “[Manager] = ‘” & rsCriteria![Manager] & ” ‘”

db.QueryDefs.Delete rsReports![QueryReport]
Set qdf = db.CreateQueryDef(rsReports![QueryReport], strSQL)

DoCmd.OutputTo acReport, rsReports![ReportName], “SnapshotFormat(*.snp)”, “X:AnixterMonthly IAP InfoBizman” & rsCriteria![FileName] & rsReports![alias] & “.snp”, False, “”

rsCriteria.MoveNext
Loop
rsCriteria.close

rsReports.MoveNext
Loop
rsReports.close

Reply | Quote

[indent]

---

strSQL = “SELECT * FROM rsReports![QueryReportMain] WHERE “

---

[/indent]If this is the FROM clause in question, then I can see your problem. You can’t run a query on an open recordset. You can filter a recordset using the Filter method or you can open another recordset based on the same SQL source but with a WHERE clause, but you can’t do what you’re trying to do the way you’re trying to do it.

Reply | Quote