Compile list of all joins within queries

[dalien.51]

Is it possible programmatically to go through all of my queries and compile a list of every join ie Left Table, Left Field, Right Table, Right Field

Or do I have to go through each one and create it manually?

 

[The_Doc_Man]

Well, it might not be the most elegant solution, but if you go through the AllQueries collection and examine each querydef.SQL property, you get a string that expresses the actual query contents. If you aren't sure how to do that, search the forum for "Collections" to see the use of the "For Each xxx In collection-of-xxx" construct.

It would be trivial to search that SQL string for " JOIN " (with the spaces so you don't catch field/table names that contain the letters JOIN embedded in the names).

Once you did it would then be possible to parse using searches for keyword FROM to locate the source recordset names and ON, ";", WHERE, ORDER BY, HAVING, GROUP BY, etc. (whichever comes first) to identify the end of the source recordset names.

Then, if you get really ambitious, parse out the ON clause to identify the fields.

 

[static]

Query definitions are stored in the msysqueries table.

SELECT MSysObjects.Name, msysqueries.Expression
FROM   msysqueries INNER JOIN MSysObjects ON msysqueries.ObjectId = MSysObjects.Id
WHERE  msysqueries.name1 Is Not Null AND msysqueries.name2 Is Not Null