Deconstructing queries (XP/SP2)

Topic

New Reply

I’ve been given the task of seeing if it’s possible to automatically convert potentially a lot of user databases
– the reason is there is a change in the table keys
– the tables are in Oracle, so are linked using ODBC
– as I see it, the basic requirement will be to poke around in the database for anywhere the old joins are, and I’ve presumed in the QueryDefs, and replace them with the new ones

I’d hoped that there were properties of the QueryDef, but no luck

I’ve found part of an article by Ken Getz on “Deconstructing QueryDefs” which looks like a start, well the Google cache as the Smart Solutions site has taken down the original
– uses the MSysObjects and MSysQueries tables to “parser” the querydef
– what I’m unsure of is where the actual “join” detail is
– I’m discounting the Relations collection, as it’s very unlikely the users have used it

What I’d be interested in is any other pointers, suggestions, warnings, etc.

Reply | Quote

Replies

The description of how they get the JOIN part of a query from the system tables is in part 2 of their series. They explicitly state that this is the most complicated part of deconstructing a query.

(Moreover, it is possible to “hide” a join by putting it in the WHERE clause of the query instead of in the FROM clause.)

You would have to loop through the MSysQueries table. Records with Attribute = 7 contain info about the joins. The Expression field contains the join in the form “TableA.FieldA = TableB.FieldB”. So you could scan the Expression field for the occurrence of field names that have to be changed. This would only help in determining which queries have to be changed; you can’t edit the information in MSysQueries. You’d still have to get the SQL of the querydef, find the JOIN part and replace the field names.

Reply | Quote

Thanks for the response Hans
– yeap got that from Google, just wish I could get to the actual pages so I could download their example code

Reply | Quote

ARe you talking about the joins in querydefs or the relationshps between the tables? Relationshps have their own existence separate from queries and tables.

Reply | Quote

Charlotte,
I was looking at the joins in queries (querydefs). I did have a look at the Relations collection, wondering if that held the information, but soon found out it didn’t
– looking at the Ken Getz/Mike Gilbert set of articles, the problem look more complicated than I first thought, but if I limit to simpler SQL, i.e. exclude crosstab’s, unions, etc. it should be possible
– what’d be good would be to get hold of the source code for their QueryInfo class, but it’s a pain that the MS Office Solutions seem to have decided to drop the older pages
– the class + query seem to go a long way to getting the SQL out of the MSSys… tables. I could then adjust it for the new joins and push it back into the qurydef SQL property

Reply | Quote

If the queries are not too complicated, you might as well parse the SQL of the queries. The joins will be in the FROM part of the SQL string.

Reply | Quote

Biggest problem Hans is whatever I develop will be used on potentially 200+ sites, so I really need to make it as bomb proof as possible
– the class from Getz/Gilbert has a lot in, and it looks like a good point to build from

Thanks to you and Charlotte for the suggestions

Reply | Quote

Check the Access Developers Handbook and see if you can find the info there. Gets and Gilbert were among the authors of the 2000 version, although Gilbert departed the ADH in the 2002 version.

Reply | Quote

Nice thought Charlotte, shame not in there
– according to the MS Smart Solutions site, all source code, etc. is on the CD, but they’re quoting 4-8 weeks for delivery
– think I’ll order it, hopefully the powers-that-be won’t want anything too soon

Reply | Quote