How to parse SQL query to get table names involved in that query in Access

Question

I have decided to solve this problem by using vba and regular expression. Here is part of the code:

Dim strPattern As String: strPattern = "(FROM|JOIN|from|From|Join|join)\s+([^ ,]+)(?:\s*,\s*([^ ,]+))*\s*"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String

Set MyMatches = regEx.Execute(Form)

If MyMatches.Count <> 0 Then
    With MyMatches
        For myMatchCt = 0 To MyMatches.Count - 1
            If Left(MyMatches.Item(myMatchCt), 6) <> "FROM (" And Left(MyMatches.Item(myMatchCt), 6) <> "JOIN (" Then              
                str = MyMatches.Item(myMatchCt)
                lenght = Len(str)
                format_data = Right(str, lenght - 4)
                pos = InStr(format_data, ")") + InStr(format_data, "(select")
                If pos = 0 Then
                    rst.AddNew
                    rst!block_id = rs("block_id")
                    rst!trans_table = format_data
                    rst.Update
                End If
            End If
        Next
    End With
End if

Parsed tables are written to rst.

It works almost fine with many types of query, I interested only in select queries. However I can't figure out how to handle subqueries, for example from this query I get odd bracket (:

select *
from (
select * from t1
union 
select * from t2
) t
where 1=1;

So what is wrong here?

score 1 · Accepted Answer · edited May 23 '17 at 12:14

At first glance...

Left(MyMatches.Item(myMatchCt), 6) <> "FROM ("

<> is case sensitive

ie this is true Left("From (", 6) <> "FROM ("

You will need to change it to

Ucase(Left(MyMatches.Item(myMatchCt), 6)) <> "FROM ("

Similarly with the "JOIN" condition

I can see why you want to write your own code as buying SQL parsers is expensive!

Here's some text and links I found about this:

Please also see this SO questions: here which states:

RegEx isn't very good at this, as it's a lot more complicated than it appears:

What if they use LEFT/RIGHT INNER/OUTER/CROSS/MERGE/NATURAL joins instead of the a,b syntax? The a,b syntax should be avoided anyway. What about nested queries? What if there is no table (selecting a constant) What about line breaks and other whitespace formatting? Alias names? I could go on.

What you can do is look for an sql parser, and run your query through that.

Also note: You want access to arbitrary substructures of a SQL query (including sub SELECTs)? What you need is a full parser for the SQL dialect of interest.

Try here

This one will set you back $400! I'm starting to see why you are writing one - nice by the way.

SQL is a pretty large and complicated language. It is possible to hand-code a recursive descent parser to do this, but that's quite a lot of work. You'd be likely better off with a parser generator and an SQL BNF to feed it.

PS. I read that this shareware Access VBA based framework (for $60) https://www.arrow-of-time.com/AXF.aspx has a SQL parser in it. I've not used it though. — HarveyFrench, Oct 07 '15 at 20:32

How to parse SQL query to get table names involved in that query in Access

1 Answers1