Get list of querys/table that a query is based on AND queries that reports are using?

[Cosmos75]

Is there any easy way to get a listing of;

1) Tables and other queries that a query is based on (for all queries in a database).
2) Queries that ALL Reports in a database are using as a record source?

 

[jfgambit]

Cosmos:

not sure if this is what you are looking for, but the attached link will take you to a site where you can download an Add-in for Access called Ezy Documentor 2000.

Check it out

http://www.aadconsulting.com/

 

[Cosmos75]

jfgambit,

WONDERFULL!! That works great!!


The only problem is that it doesn't seem to work for queries.


I get an error message that says

Queries List Error 5 Invalid procedure call or argument


You ever see that?

 

[jfgambit]

Strange.
I just ran it for one of my databases and it worked on the Queries...could have been a glitch in the installation. Try to remove the Active X control and then restore it.

 

[Cosmos75]

Nope, that didn't work...

 

[jfgambit]

My next option would be to see if http://www.aadconsulting.com/ has a FAQ or CS where you could the problem. if it is happening to you I'm sure it's happened to another user.

 

[Cosmos75]

Will update when and if I find a solution!

 

[Cosmos75]

It worked on a very simple database but not on my 100 query monster with some over 900 characters...

 

[The_Doc_Man]

I can give you your answer but won't show you the code. It isn't public domain. I'll have to explain the method.

First, you have to be able to write VBA code reasonably well. Not super, but you can't be afraid of it.

Second, you have to be cognizant of collections. You will be dealing with two collections: CurrentDB.QueryDefs (a collection of QueryDef objects) and CurrentDB.Containers!Reports.Documents (a collection of Document objects).

For querydefs, you can write a For Loop

For Each myQueryDef in CurrentDB.QueryDefs
Next myQueryDef

(where of course myQueryDef is dim'd as QueryDef)

A QueryDef object has an .SQL clause as one of its attributes. So all you have to do is find everything between the "FROM" keyword and the next significant element, which could be any one of "WHERE" "ORDER BY" "GROUP BY" or the end of the line. The stuff you get between "FROM" and the next significant keyword will be table names, query names, plus possible JOIN syntax: OUTER JOIN, INNER LEFT JOIN, INNER RIGHT JOIN, etc.

Be warned that sometimes the Access environment synthesizes a name for queries that were created through wizards. It is a funky name, often something that begins with "~sqx", but it is SQL. In that sequence of tilde, s, and q, the next letter isn't really x but rather is c for field lookups, d for parent-child linkages, f for synthesized form queries, and r for synthesized report queries. So really ~sqf or ~sqr is what you would see.

The report document object will have to be opened in design view briefly. Once it is open, you can examine the open report's .Recordsource attribute, which will be a table name, query name, or actual SQL query. A brief rule of thumb important in this context: If you opened it, you must close it.

You can do a For Loop over document objects, too.

For Each myRepDoc in CurrentDB.Containers!Reports.Documents
Next myRepDoc

where of course myRepDoc is dim'd as a Document. You have to do a DoCmd.OpenReport myRepDoc.Name in ViewDesign mode, then you need a variable of type Report that you would do a

Set myReport = Reports(myRepDoc.Name)

Then you can examine the report attributes such as .Recordsource

If the object is a table name or query name, you can try to look it up in the CurrentDB.QueryDefs or CurrentDB.TableDefs collection. If the object is SQL, the first word of the recordsource string will be "SELECT" and will be followed by more syntax including a FROM clause.

Now, the next question is, "So what if I can do this...?"

The answer - every time you visit an item you can write an entry to a temporary table that shows the name of the parent item and the name you extracted from it. Since Access will happily do "GROUP BY" operations at the query level, you don't have to supply a prime key for this temporary table. Just sort it and export it. And that's your list.

 

[Cosmos75]

The_Doc_Man,

Thanks for the reply!

I'm not afriad of VBA but I am only moderately good at EXCEL VBA and haven't yet started to use VBA in ACCESS with the exception of making sure that txtboxes on forms are filled out and showing msgboxes, but beyond that.....

me =

When I finally do get to learn and use some VBA I'll be sure to try this out!