VBA to parse SQL into [FieldName] and [Source]

[rubbernilly]

Just wondering if there is some pre-built code to handle a querydef and parse out the SQL into [FieldName] and [Source]?

For instance, given the SQL of:

SELECT tblTable1.WidgetID, tblTable1.WidgetName, IIF(ISNULL(tblTable1.WidgetPrice),2.99, tblTable1.WidgetPrice) AS Price, tblTable1.WidgetReOrderQty AS OrderAmt, (Price * OrderAmt) AS WidgetPricePerOrder
FROM tblTable1;

It would produce something along the lines of:

QueryField		Source
WidgetID		tblTable1.WidgetID
WidgeName		tblTable1.WidgetName
Price			IIF(ISNULL(tblTable1.WidgetPrice),2.99, tblTable1.WidgetPrice)
OrderAmt		tblTable1.WidgetReOrderQty
WidgetPricePerOrder	(Price * OrderAmt)

Obviously the all fields selector (i.e., "*") would be passing a bunch of fields that we wouldn't be able to parse except for by opening the query and accessing the fields, but I think for these purposes I don't think I would need it, as the source and query field would be the same (that is, the field name in the query would be the same in the table, so it doesn't matter to me). Mostly I want to snag the fields that are constructed on the fly. It doesn't have to do it in any particular way... array, dumping to a table via DAO... just so long as I can get at the list.

I'm hoping this has been done already, as parsing for commas will be problematic given their presence in various formulas (vba function argument lists, IIF() statements, etc.).

Thanks for any suggestions.

 

[SkipVought]

Hi,

Check out the Split() function. You'll need to parse in steps.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

I doubt you could split this very easily and reliably. I think I would try to use the DAO object model. Obviously getting all the field names is trivial. The ones that are not aliased would have the same source as the name and I would not worry about them, because that would be column 2 for those fields. Determing if the Alias and field name are not the same is done by comparing the field.name to the field.sourcefield property. That would give you a list of those fields that are aliased as a starting point. Then I think I would write the code only for those aliased fields. Search for each of those field names in the the string and try to determine the expression. There is an "expression" property of the fields collection, but it does not same to return the expected expression for the calculated fields. You would think somewhere in the dao model you can find IIF(ISNULL(tblTable1.WidgetPrice),2.99, tblTable1.WidgetPrice)" for the field Price, but I did not see anywhere.