Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    13

    Unanswered: How to list all tables/queries that the query is based on?

    Hi guys,

    I am just trying to develope form which will be very usefull while working with Access DBs

    it is divided into a few sections:

    in Tables section there are 2 ListBoxes
    first shows all tables in the DB and other shows all Queries that selected table is used in (it's useful for example when we change name/structure of the table - we know which queries will be affected)

    in Queries section there are again 2 ListBoxes
    first shows all Queries in DB and other shows all Tables/Queries that selectes query is based on....

    and the question is

    HOW TO LIST ALL TABLES AND QUERIES ON THE SECOND LISTBOXES???

    as for now I compare Query.SQL string with Table Name (Query.SQL LIKE TblName, looping through all tables in DB) and this way I can list all tables used in the query (the same for queries...)

    but this is not very good way

    as in SQL string there are a lot of sings like dots, comas etc I had to compare SQL string with different "versions" of TblName ( "*TblName.*", "*TblName,*" etc) which case the problem that two tables from database with similar name for example TblDate and TblDate_Flash will be listed even if only TblDate is used in the query...

    so, is there any other way to list them?

    Any help will be apprecited

    Thanks again for your time

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Not the prettiest solution but try this:


    Sub Tables()
    Dim SqlStr As String
    Dim Start_Point As Integer, End_Point As Integer, A As Integer, B As Integer, I As Integer
    Dim Tbl_Qry(10)
    Dim Found_Me As Boolean
    I = 0

    Your_query = "Query2"

    SqlStr = CurrentDb.QueryDefs(Your_query).Properties("sql")

    For A = 1 To Len(SqlStr)
    If Mid(SqlStr, A, 1) = " " Then
    Start_Point = A
    End If
    If Mid(SqlStr, A, 1) = "." Then
    End_Point = A
    the_item = Mid(SqlStr, Start_Point, End_Point - Start_Point)
    GoSub Add_me
    End If
    Next

    For A = 0 To I - 1
    Msg = Msg & Tbl_Qry(A) & ","
    Next
    MsgBox "You're query <" & Your_query & "> uses " & Msg & " as data sources", vbOKOnly + vbInformation, "Data Source"
    Exit Sub
    Add_me:
    For B = 0 To UBound(Tbl_Qry)
    If the_item = Tbl_Qry(B) Then Return
    Next

    Tbl_Qry(I) = the_item
    I = I + 1
    Return
    End Sub


    There is probably a very easy way of doing this, but I cant find it..I'm still looking, but this is the best I can do so far.



    Ken

  3. #3
    Join Date
    Apr 2004
    Posts
    8
    Give this a try. Paste this SQL into a query and use it as the starting point - each record should be a table name and query name.

    If you then add your table list box as the criteria under the TableName field it should do what you want....

    good luck!
    Attached Files Attached Files

Posting Permissions

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