Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

find unsued tbls, qry's, modules, etc

Discussion in 'Information Technology' started by Brook, Nov 10, 2005.

  1. Brook

    Brook
    Expand Collapse
    Guest

    good day all,

    I have been developing my db for a while now and have misc tbls, qry's etc
    and was wondering if there was a way to find out what is being used / unused?

    Thanks,

    brook
     
  2. Loading...

    Similar Threads Forum Date
    Pathfinder Joins Sikh Philosophy Network! New SPN'ers Sep 5, 2016
    The Salmon Story, Finding Home Sikh Sikhi Sikhism Jul 29, 2016
    Sikhi Guru Nanak's Methodology Of Finding Truth Sikh Sikhi Sikhism Jan 1, 2016
    Movies Ashdoc's movie review---Finding Fanny ( English version ) Theatre, Movies & Cinema Sep 15, 2014
    Leisure Can someone pls help me find this Waheguru Simran...?? Business, Lifestyle & Leisure Aug 5, 2014

  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    Brook,

    If you're working with A2K3 (not sure about A2K2, but probably the same
    holds true there) you are lucky, it offers the "object dependencies"
    functionality; just right-click on a table or query, and select this option.
    If you are using an older version, things are not that simple, but still
    workable; paste the code below in a general module:

    Sub Export_Table_Dependencies()
    Dim vPath, vName
    vPath = CurrentProject.Path
    vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
    Open vPath & "\" & vName & " Table Dependencies.txt" For Output As #1
    For Each tbl In CurrentDb.TableDefs
    If Left(tbl.Name, 4) <> "MSys" Then
    For Each qry In CurrentDb.QueryDefs
    If InStr(1, qry.SQL, tbl.Name) > 1 Then
    Print #1, tbl.Name & " is used in query " & qry.Name
    End If
    Next
    End If
    Next
    Close #1
    End Sub

    Sub Export_Query_Dependencies()
    Dim vPath, vName
    vPath = CurrentProject.Path
    vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
    Open vPath & "\" & vName & " Query Dependencies.txt" For Output As #1
    For Each qry In CurrentDb.QueryDefs
    If Left(tbl.Name, 4) <> "MSys" Then
    For Each qry1 In CurrentDb.QueryDefs
    If InStr(1, qry1.SQL, qry.Name) > 1 Then
    Debug.Print qry.Name & " is used in query " & qry1.Name
    End If
    Next
    End If
    Next
    Close #1
    End Sub

    And run both subs; each will create a text field in the same directory
    as the database, with lines like:

    TableX is used in query QueryY
    etc.

    and

    Query1 is used in query Query2
    etc.

    They will also catch SQL expressions in the rowsource of list boxes or
    combo boxes in forms (where the query name starts with "~sq_"), but they
    won't catch SQL expressions in forms' or reports' RecordSource property
    that are not saved queries, so you need to be careful there.

    HTH,
    Nikos
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 9 Nov 2005 18:21:02 -0800, "Brook"
    <Brook@discussions.microsoft.com> wrote:

    >good day all,
    >
    > I have been developing my db for a while now and have misc tbls, qry's etc
    >and was wondering if there was a way to find out what is being used / unused?
    >
    >Thanks,
    >
    >brook


    If you're not using Access2003 (as Nikos has covered that possibility)
    you'll need to obtain one of the several third-party tools to cover
    this need:

    Free:
    http://www3.bc.sympatico.ca/starthere/findandreplace
    Find and Replace: http://www.rickworld.com
    Speed Ferret: http://www.moshannon.com
    Total Access Analyzer: http://www.fmsinc.com

    John W. Vinson[MVP]
     
  5. Brook

    Brook
    Expand Collapse
    Guest

    Thank you Nikos,

    That was exactly what I was looking for!

    Brook

    "Nikos Yannacopoulos" wrote:

    > Brook,
    >
    > If you're working with A2K3 (not sure about A2K2, but probably the same
    > holds true there) you are lucky, it offers the "object dependencies"
    > functionality; just right-click on a table or query, and select this option.
    > If you are using an older version, things are not that simple, but still
    > workable; paste the code below in a general module:
    >
    > Sub Export_Table_Dependencies()
    > Dim vPath, vName
    > vPath = CurrentProject.Path
    > vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
    > Open vPath & "\" & vName & " Table Dependencies.txt" For Output As #1
    > For Each tbl In CurrentDb.TableDefs
    > If Left(tbl.Name, 4) <> "MSys" Then
    > For Each qry In CurrentDb.QueryDefs
    > If InStr(1, qry.SQL, tbl.Name) > 1 Then
    > Print #1, tbl.Name & " is used in query " & qry.Name
    > End If
    > Next
    > End If
    > Next
    > Close #1
    > End Sub
    >
    > Sub Export_Query_Dependencies()
    > Dim vPath, vName
    > vPath = CurrentProject.Path
    > vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
    > Open vPath & "\" & vName & " Query Dependencies.txt" For Output As #1
    > For Each qry In CurrentDb.QueryDefs
    > If Left(tbl.Name, 4) <> "MSys" Then
    > For Each qry1 In CurrentDb.QueryDefs
    > If InStr(1, qry1.SQL, qry.Name) > 1 Then
    > Debug.Print qry.Name & " is used in query " & qry1.Name
    > End If
    > Next
    > End If
    > Next
    > Close #1
    > End Sub
    >
    > And run both subs; each will create a text field in the same directory
    > as the database, with lines like:
    >
    > TableX is used in query QueryY
    > etc.
    >
    > and
    >
    > Query1 is used in query Query2
    > etc.
    >
    > They will also catch SQL expressions in the rowsource of list boxes or
    > combo boxes in forms (where the query name starts with "~sq_"), but they
    > won't catch SQL expressions in forms' or reports' RecordSource property
    > that are not saved queries, so you need to be careful there.
    >
    > HTH,
    > Nikos
    >
     

Share This Page