by Ross Mack - GUI Computing
If Mohammed won't go to the System Tables...
In the past few weeks it has been my lot in life to do some maintenance and jack-of-all-trades (master of none) work on a large client server app. Thatís cool as I get to do something different all the time - often three or four different things at one time.
And so it came to pass that one of the project managers asked me if I knew of a way to simply get to the SQL strings for all the queries in the access database we were using (why an Access database in a client/server app? Itís a long story...). It seems he wanted to know for each QueryDef what TableDefs and other QueryDefs it was dependent on. I believed the simplest way to determine this was to check for an occurrence of each objectís name in the SQL string of each QueryDef.
Concerned that this was starting to sound like a lot of work, here is what I did...
I knew, from experimentation and a previous conversation with Peter Wone, that retrieving the SQL string for an Access query directly from the system tables was possible. However, this required more research and stuffing about than even Peterís enthusiasm for such things could bear.
Remembering my VB data access objects I recalled that VB QueryDef objects expose a property called SQL which is their SQL string (strange!). I soon discovered that this was also the case in Access Basic. (My apologies to Access programmers who may believe Iím thinking backwards.) This meant that I could get to the SQL string fairly easily - and have my evil way with it. Being in a database environment, the most useful thing I could think of to do with it was store it with the QueryDef name in a table. From there we can do whatever we like with it, after all this is Access - it likes tables.
What I needed to do with this function was do a basic check of what objects (TableDefs or QueryDefs) are used in the databases QueryDefs. A list of dependencies. Now that we have a list of QueryDefs, and their SQL, it is comparatively easy to do this.
What we have is a function that uses two SQL statements. One to build a list of dependencies, the other to put that list into a table. As the query which creates the table must work from an existing QueryDef, we create one on the fly from the first SQL statement, use it in the next SQL statement (which is directly executed), and then remove it from the database again. This way we are able to use Access to do all the work as queries (which it attempts to execute optimized, as always) instead of code. We also donít add any objects permanently to the database - except the results table and the module of code that does all this.
This sub takes two parameters, the source table and the destination table. You should also note that this function is not fool proof. It can easily be confused by object names which are parts of other object names and similar Ďpeculiaritiesí. But it gives you a good starting point, much better than checking each and every object by hand.
The source code shown here has virtually no error handling at all, so as to improve readability.