in vba: Get list of all tables involved in the query

Hi

Is there an easy way in VBA to get a list of all the tables that are
involved in a particular query/sql_string? I have made a couple of
dummy examples below (possible syntax errors, just demo'ing the
intention)

For example:

SELECT FirstName
FROM t_People
WHERE (t_People.[FirstName] = "David")

would return [t_People]


SELECT t_People.[FirstName]
FROM t_People INNER JOIN t_Members ON t_People.[Alive] = t_Members.
[Alive]
WHERE (t_People.[FirstName] = "David");

would return [t_People, t_Members]


One idea I know will work is to pre calculate a list of all the table
names, and then simple search each SQL string for those table names
and print out only those which match, but its a bit ugly and
cumbersome and wondered if there was a better way.

Thanks
AndyC

No: there's not a simple way to write a query parser, Andy.

You can OpenRecordset, loop through the Fields, and examine the SourceTable
of each. But this will only report the tables that actually output a field.

You can try to parse the FROM clause. But you run into several walls here,
e.g.:
a) Access lets users create queries that use reserved words as table/field
names, so your parser will need to cope with fields named Select, From,
Where, etc. (Typically Access adds square brackets around these names, but
you can't be sure.)

b) The FROM clause may contain a subquery where you expect a table name,
e.g.:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q INNER JOIN
(SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID) As T
ON Q.BookId=T.BookId AND Q.DateOut = T.S

c) The FROM clause may contain a query name rather than a table name, and
that query may also be stacked on another query, and so on.

d) It gets quite confusing when these stacked queries use a table multiple
times, or when the same aliases are used for different tables in stacked
queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AC" wrote in message
...

Is there an easy way in VBA to get a list of all the tables that are
involved in a particular query/sql_string? I have made a couple of
dummy examples below (possible syntax errors, just demo'ing the
intention)

For example:

SELECT FirstName
FROM t_People
WHERE (t_People.[FirstName] = "David")

would return [t_People]


SELECT t_People.[FirstName]
FROM t_People INNER JOIN t_Members ON t_People.[Alive] = t_Members.
[Alive]
WHERE (t_People.[FirstName] = "David");

would return [t_People, t_Members]


One idea I know will work is to pre calculate a list of all the table
names, and then simple search each SQL string for those table names
and print out only those which match, but its a bit ugly and
cumbersome and wondered if there was a better way.

Thanks
AndyC

Looking at a query in Visual Sourcesafe, it is already parsed. See the
sample below. You can use the undocumented SaveAsText routine to create the
same output. This looks a lot easier to use for extracting the table names.

Call Application.SaveAsText(acQuery, "~qtotSubmissionsByDay",
"c:\temp\QueryTest.txt")

SAMPLE QUERY (PORTION) IN VSS:
Where ="(((ABSTRACT.abWebSubmissionDate) Is Not Null)) OR
(((ABSTRACT.abDateReceived) Is"
" Not Null))"
Begin InputTables
Name ="MEETING"
Name ="SESSION"
Name ="ABSTRACT"
End
Begin OutputColumns
Expression ="SESSION.meetStartDate"
Alias ="DaysBeforeDeadline"
Expression
="DateDiff(\"d\",nz([abWebSubmissionDate],[abDateReceived]),[meetEmailSubmissionDa"
"te])"
Alias ="SubmissionCount"
Expression ="Count(*)"
End
Begin Joins
LeftTable ="SESSION"
RightTable ="ABSTRACT"
Expression ="SESSION.sessionID = ABSTRACT.sessionID"
Flag =1
LeftTable ="MEETING"
RightTable ="SESSION"
Expression ="MEETING.meetStartDate = SESSION.meetStartDate"
Flag =1
End
Begin OrderBy
Expression
="DateDiff(\"d\",nz([abWebSubmissionDate],[abDateReceived]),[meetEmailSubmissionDa"
"te])"
Flag =1
End


"Allen Browne" wrote in message
...
No: there's not a simple way to write a query parser, Andy.

You can OpenRecordset, loop through the Fields, and examine the
SourceTable of each. But this will only report the tables that actually
output a field.

You can try to parse the FROM clause. But you run into several walls here,
e.g.:
a) Access lets users create queries that use reserved words as table/field
names, so your parser will need to cope with fields named Select, From,
Where, etc. (Typically Access adds square brackets around these names, but
you can't be sure.)

b) The FROM clause may contain a subquery where you expect a table name,
e.g.:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q INNER JOIN
(SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID) As T
ON Q.BookId=T.BookId AND Q.DateOut = T.S

c) The FROM clause may contain a query name rather than a table name, and
that query may also be stacked on another query, and so on.

d) It gets quite confusing when these stacked queries use a table multiple
times, or when the same aliases are used for different tables in stacked
queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AC" wrote in message
...

Is there an easy way in VBA to get a list of all the tables that are
involved in a particular query/sql_string? I have made a couple of
dummy examples below (possible syntax errors, just demo'ing the
intention)

For example:

SELECT FirstName
FROM t_People
WHERE (t_People.[FirstName] = "David")

would return [t_People]


SELECT t_People.[FirstName]
FROM t_People INNER JOIN t_Members ON t_People.[Alive] = t_Members.
[Alive]
WHERE (t_People.[FirstName] = "David");

would return [t_People, t_Members]


One idea I know will work is to pre calculate a list of all the table
names, and then simple search each SQL string for those table names
and print out only those which match, but its a bit ugly and
cumbersome and wondered if there was a better way.

Thanks
AndyC

On May 12, 4:00*pm, "Paul Shapiro"
wrote:
Looking at a query in Visual Sourcesafe, it is already parsed. See the
sample below. You can use the undocumented SaveAsText routine to create the
same output. This looks a lot easier to use for extracting the table names.

Call Application.SaveAsText(acQuery, "~qtotSubmissionsByDay",
"c:\temp\QueryTest.txt")

SAMPLE QUERY (PORTION) IN VSS:
Where ="(((ABSTRACT.abWebSubmissionDate) Is Not Null)) OR
(((ABSTRACT.abDateReceived) Is"
* * " Not Null))"
Begin InputTables
* * Name ="MEETING"
* * Name ="SESSION"
* * Name ="ABSTRACT"
End
Begin OutputColumns
* * Expression ="SESSION.meetStartDate"
* * Alias ="DaysBeforeDeadline"
* * Expression
="DateDiff(\"d\",nz([abWebSubmissionDate],[abDateReceived]),[meetEmailSubmi*ssionDa"
* * * * "te])"
* * Alias ="SubmissionCount"
* * Expression ="Count(*)"
End
Begin Joins
* * LeftTable ="SESSION"
* * RightTable ="ABSTRACT"
* * Expression ="SESSION.sessionID = ABSTRACT.sessionID"
* * Flag =1
* * LeftTable ="MEETING"
* * RightTable ="SESSION"
* * Expression ="MEETING.meetStartDate = SESSION.meetStartDate"
* * Flag =1
End
Begin OrderBy
* * Expression
="DateDiff(\"d\",nz([abWebSubmissionDate],[abDateReceived]),[meetEmailSubmi*ssionDa"
* * * * "te])"
* * Flag =1
End

"Allen Browne" wrote in message

...



No: there's not a simple way to write a query parser, Andy.

You can OpenRecordset, loop through the Fields, and examine the
SourceTable of each. But this will only report the tables that *actually
output a field.

You can try to parse the FROM clause. But you run into several walls here,
e.g.:
a) Access lets users create queries that use reserved words as table/field
names, so your parser will need to cope with fields named Select, From,
Where, etc. (Typically Access adds square brackets around these names, but
you can't be sure.)

b) The FROM clause may contain a subquery where you expect a table name,
e.g.:
* SELECT Q.BookID, Q.BorrowerID
* FROM TableName As Q INNER JOIN
* * * *(SELECT BookID, Max(DateOut) As S
* * * * FROM TableName
* * * * GROUP BY BookID) *As T
* * * *ON Q.BookId=T.BookId AND Q.DateOut = T.S

c) The FROM clause may contain a query name rather than a table name, and
that query may also be stacked on another query, and so on.

d) It gets quite confusing when these stacked queries use a table multiple
times, or when the same aliases are used for different tables in stacked
queries.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AC" wrote in message
...

Is there an easy way in VBA to get a list of all the tables that are
involved in a particular query/sql_string? *I have made a couple of
dummy examples below (possible syntax errors, just demo'ing the
intention)

For example:

SELECT FirstName
FROM t_People
WHERE (t_People.[FirstName] = "David")

would return [t_People]

SELECT t_People.[FirstName]
FROM t_People INNER JOIN t_Members ON t_People.[Alive] = t_Members.
[Alive]
WHERE (t_People.[FirstName] = "David");

would return [t_People, t_Members]

One idea I know will work is to pre calculate a list of all the table
names, and then simple search each SQL string for those table names
and print out only those which match, but its a bit ugly and
cumbersome and wondered if there was a better way.

Thanks
AndyC- Hide quoted text -

- Show quoted text -

Hi Paul

I tried your SaveAsText and got very different output, see below:

MY CODE:
Call Application.SaveAsText(acQuery, "q_set_g", "c:\temp
\QueryTest.txt")

RESULTED IN THE FOLLOWING TEXT IN QUERYTEST.TXT
dbMemo "SQL" ="SELECT data_h_IndivNewUnitData.[Unit Name] AS Unit
\015\012FROM data_h_IndivNewUn"
"itData\015\012UNION\015\012SELECT data_h_UnitData.[Unit Name] AS
Unit\015\012FRO"
"M data_h_UnitData\015\012UNION SELECT data_h_UnitSpawn.Unit as
Unit\015\012FROM "
"data_h_UnitSpawn;\015\012"
dbMemo "Connect" =""
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
dbBoolean "OrderByOn" ="0"
dbByte "Orientation" ="0"
dbByte "DefaultView" ="2"
Begin
End

Am I doing something wrong?


FWIW I will post my original SQL parser here in case anyone else is
interested. It is very simplistic, but it is smart enough to drill
down recursively into any sub queries if they exist. I wont post all
the code but will document where I have cut it out.


'----------------------------------------------------------------------
' @@GetSQLTables
' @Takes a SQL statement and reference to a db and returns a list of
the
' @db tables that are in the query.
' @Eg SELECT t_People.FirstName FROM t_People WHERE
t_People.LastName="Blogs"
' @would return the table t_People.
' @Handles nested queries, so if the SQL was
' @"SELECT x FROM q_myQuery INNER JOIN t_People ON q_myQuery.y =
t_People.y" it
' @will drill down into q_mySubQuery and return the tables from that
(and if
' @q_myQuery contains its own queries it will drill into those, and so
on)
' @THIS ROUTINE IS VERY BASIC, IT ONLY RETURNS TABLES THAT ARE IN
' @THE REFERENCED DATABASE.
' @INPUTS
' @- strSQL = SQL statement
' @- db = searches for tables in this db
' @OUTPUTS
' @- csv list of the tables involved in the SQL query.
'----------------------------------------------------------------------
Function GetSQLTables(ByVal strSQL As String, db As DAO.Database) As
String
On Error GoTo GetSQLTables_Err

Dim strMsg As String
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef

'Initialise
GetSQLTables = ""

'Check the db parameters
If (db Is Nothing) Then
MsgBox "Invalid parameter in routine GetSQLTables: db =
nothing", vbCritical + vbOKOnly, "ERROR"
GoTo GetSQLTables_Exit
End If

'Eliminate the trivial case
If (IsNull(strSQL) Or Len(strSQL) = 0) Then
GoTo GetSQLTables_Exit
End If

'The SQL query may contain references to other queries, if so
recursively
'call this routine again with THOSE queries
For Each qdf In db.QueryDefs
If (InStr(strSQL, qdf.Name)) Then
GetSQLTables = GetSQLTables & "," & GetSQLTables(qdf.SQL,
db)
End If
Next qdf

'Loop through each table in db and if it is present in the SQL
statement add it to the list
For Each tdf In db.TableDefs
If (InStr(strSQL, tdf.Name)) Then
GetSQLTables = GetSQLTables & "," & tdf.Name
End If
Next tdf

'Clean up any leading or trailing "," in the list
While (Left(GetSQLTables, 1) = ",")
GetSQLTables = Right(GetSQLTables, Len(GetSQLTables) - 1)
Wend
While (Right(GetSQLTables, 1) = ",")
GetSQLTables = Left(GetSQLTables, Len(GetSQLTables) - 1)
Wend

'Remove any duplicates
GetSQLTables = GetUniqueCSVItems(GetSQLTables) '### THIS IS A
UTILITY ROUTINE I WROTE WHICH TAKES A CSV LIST AND RETURNS THE UNIQUE
ITEMS


GetSQLTables_Exit:
Set tdf = Nothing
Set qdf = Nothing
Exit Function

GetSQLTables_Err:
On Error Resume Next

'### DEAL WITH ERRORS HERE

GetSQLTables = ""
GoTo GetSQLTables_Exit

End Function

Thanks Paul. IME, SaveAsText is informative for a very simple query, but the
results of a query stacked on another query are less than useful (pretty
much as AC found.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul Shapiro" wrote in message
...
Looking at a query in Visual Sourcesafe, it is already parsed. See the
sample below. You can use the undocumented SaveAsText routine to create
the same output. This looks a lot easier to use for extracting the table
names.

Call Application.SaveAsText(acQuery, "~qtotSubmissionsByDay",
"c:\temp\QueryTest.txt")

No: there's not a simple way to write a query parser, Andy.