Parse/deconstruct SQL with VBA-Access

Question

Does anyone know of a way to deconstruct a SQL statement (take a select SQL statement, extract columns from each SELECT, tables from each FOR and each JOIN, and filtering criteria from each WHERE. I can then put this data into a BOM table to create a "map" of the query), including subqueries, using VBA? I have a project to map Teradata views into a Access DB. I'd like to have an automated method to do this.

I want to take a select SQL statement, extract columns from each SELECT, tables from each FOR and each JOIN, and filtering criteria from each WHERE. I can then put this data into a BOM table to create a "map" of the query. — DataZombies
– DataZombies, Commented Sep 3, 2010 at 18:01
If you save a SELECT statement as an Access QueryDef, you can identify the output fields by looking through the QueryDef.Fields collection. Unfortunately, that is only one of the items you want. I don't know how to get the others. — HansUp
– HansUp, Commented Sep 3, 2010 at 19:27
The QueryDef object lacks a Fields collection, but if you open it as a recordset, the Fields collection gets you both the list of Fields, and each Field has a SourceTable property. This would only work on SELECT statements, though. This all seems a pretty complicated task. On the other hand, I'd think a generic SQL parser would be a pretty common task and it might that they exist in other languages and the logic could be ported to VBA. — David-W-Fenton
– David-W-Fenton, Commented Sep 3, 2010 at 22:36
CurrentDb.QueryDefs("qryMinutesPerClient").Fields.Count returns 2. CurrentDb.QueryDefs("qryMinutesPerClient").SQL returns SELECT Time_Sub.CLIENT_ID, Sum(Time_Sub.MINUTES) AS SumOfMINUTES FROM Time_Sub GROUP BY Time_Sub.CLIENT_ID; That was from Access 2003. — HansUp
– HansUp, Commented Sep 4, 2010 at 11:41

Ira Baxter · Accepted Answer · 2010-09-06 03:14:50Z

1

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

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.

But the fact that you want to do this in VBA hints that you are unlikely to find such a parser generator. You may have to call a parser generator coded in another langauge (e.g., C#) if you want to have a reasonable chance of doing this with modest effort, and go find a preexisting SQL parser.

answered Sep 6, 2010 at 3:14

Ira Baxter

95.9k24 gold badges188 silver badges357 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse/deconstruct SQL with VBA-Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related