Unanswered: How to list all tables/queries that the query is based on?
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...
For A = 1 To Len(SqlStr)
If Mid(SqlStr, A, 1) = " " Then
Start_Point = A
If Mid(SqlStr, A, 1) = "." Then
End_Point = A
the_item = Mid(SqlStr, Start_Point, End_Point - Start_Point)
For A = 0 To I - 1
Msg = Msg & Tbl_Qry(A) & ","
MsgBox "You're query <" & Your_query & "> uses " & Msg & " as data sources", vbOKOnly + vbInformation, "Data Source"
For B = 0 To UBound(Tbl_Qry)
If the_item = Tbl_Qry(B) Then Return
Tbl_Qry(I) = the_item
I = I + 1
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.