Parse Table Names From SQL String

[dtempleton]

Does anybody know a good way in VBA to parse the table names from a SQL string?

 

[bboffin]

Short of writing your own SQL parser it's not easy. However if the SQL string is used to create an Access query and that query is then saved in design mode (NOT SQL mode) Access parses the query for you.

You can then use the hidden function SaveAsText to write the query to a text file. This text file contains the parsed details of the query including a section that looks like this:

Begin InputTables
    Name ="VanLoads"
    Name ="VanDeliveries"
    Name ="ClientSignatures"
    Name ="DeliveryDetail"
    Name ="Deliveries"
End

which appears to be exactly what you want although it's all a bit messy.

The SaveAsText function was introduced to provide VSS support but it has a lot of other uses such as this.

 

[SiJP]

Um, at the risk of sounding a few tiles short of a bathroom, what do you mean?!

If you have the following:

strSQL = "SELECT * FROM tblEmployees WHERE blah = blah"

.. do you want to 'pull out' that table name?

-or-

Are you trying to parse a table name into the sql string:

strSQL = "SELECT * FROM " & strMyTableName & " & WHERE blah = blah"

It may be best for you to give an example of your code, and what you are trying to ultimately acheive.




------------------------
Hit any User to continue

 

[dtempleton]

Think I found it:

rs.Open "SELECT MSysQueries.Name1 FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId WHERE MSysQueries.Attribute = 5 and MSysObjects.Name = '~sq_r" & AccRpt.Name & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    While Not rs.EOF
        MsgBox rs.Fields(0)
        rs.MoveNext
    Wend
End If
rs.Close

I'm trying to dynamically pull out the tables/queries used by different Access reports.

Sorry for not explaining in detail what I was trying to do

 

[GummowN]

I did something recently - which remind me I need to do more work on it - it pulls every use of a name from a database - puts it into a table - rips through the queries for that name - store the query and the source and then builds a treeview going up or down the query structure ie top query showing all sources or table showing all dependents.

The premise is:

For each table in tabledefs
add record to recordset giving name, "table", uniqueID

For each query in querydefs
add record to recordset giving name, "Query", uniqueID

For each name in recordset
For each query look for that name
add record to relationshiptable giving name, query found in, uniqueID
exit looking at that query

The relationship table can then be used to build a treeview from.

WARNING this will only work if you name your queries with some sort of structue. qsel_Main will loop if there is a query called qsel_Main_sub and they are related!!!!



If at first you don't succeed, try

http://www.tek-tips.com

for the answer.