Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unhappy Unanswered: Access Query table dependancies

    Hi evrybody peeps!!

    just wondering if any of you access gurus out there know of any code that will list a query and the tables that it relies on for its data?

    I have been given the enviable task of modifying a database designed by a part-timer with around 50+ queries, so i need a painless way to find the relationships


    Thanks in advance people

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Access Query table dependancies

    As far as I know (and that is quite much), there is no dependency stored anywhere between views and tables. Some of the big DBMS like Informix do have this knowlegde, and remove, for example, all depending views when the table is dropped, but in Access, you can drop a table, or change the name without effect on the depending views.

    I would use DAO, browse through the tabledef collection, and for each tabledef, I would browse through the querydef collection and looking whether InStr(QueryDef.SQL, TableDef.Name) > 0. It's not 100%, but I would store the results in a new table, and check the result manually.

    Let me know, if this helps.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Access Query table dependancies

    As an alternative, you can also consider, to change within the loop about the tabledefs for every tabledef the name, loop through the querydefs and retrieve the parameter count. If you are getting an error, this query could be depending on your table. So, this is an alternative for the InStr() method.

    Don't forget, to change the name of the table back at the end of the block!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    Re: Access Query table dependancies

    good idea my friend

    Thanks

    if anyone else needs help then here's the code i butchered together

    '--------------------------------

    Sub QueryDefX()

    Dim dbsDB As Database
    Dim qdfLoop As QueryDef
    Dim tdfLoop As TableDef

    Set dbsDB = CurrentDb

    With dbsDB
    Debug.Print .QueryDefs.Count & " QueryDefs in " & .Name

    ' Enumerate QueryDefs collection.
    For Each qdfLoop In .QueryDefs
    Debug.Print "Query: " & qdfLoop.Name
    For Each tdfLoop In .TableDefs
    If InStr(qdfLoop.SQL, tdfLoop.Name) > 0 Then
    Debug.Print " " & tdfLoop.Name
    End If
    Next tdfLoop
    Next qdfLoop
    End With
    End Sub

Posting Permissions

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