Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deconstructing queries (XP/SP2)

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    - uses the MSysObjects and MSysQueries tables to "parser" the querydef
    - what I'm unsure of is where the actual "join" detail is <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - 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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deconstructing queries (XP/SP2)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deconstructing queries (XP/SP2)

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deconstructing queries (XP/SP2)

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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deconstructing queries (XP/SP2)

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    - 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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    - 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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deconstructing queries (XP/SP2)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deconstructing queries (XP/SP2)

    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.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deconstructing queries (XP/SP2)

    Nice thought Charlotte, shame not in there <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    - according to the MS Smart Solutions site, all source code, etc. is on the CD, but they're quoting 4-8 weeks <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> for delivery
    - think I'll order it, hopefully <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> the powers-that-be won't want anything too soon

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deconstructing queries (XP/SP2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •