VB routine to parse SQL string and get the field names

[temp]

Hi all,

I know this is easy for you guys but I am not a VB developer. My boss
just wanted me to generate a mapping of all the queries in our Access
DB. So I managed to create some subroutines to get the queries. I just
need now to parse the SQL string and get the field names. Then import
it to Excel.

Input:
Select field1, field2 FROM Table1

Output:
QUERY1
------
Table1.Field1
Table1.Field2

Of course the sample is a simple sql. The routine should handle complex
sql's.

TIA.
ross

[Dimitri Furman]

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11******** *************@f 14g2000cwb.goog legroups.com:

Hi all,

I know this is easy for you guys but I am not a VB developer. My boss
just wanted me to generate a mapping of all the queries in our Access
DB. So I managed to create some subroutines to get the queries. I just
need now to parse the SQL string and get the field names. Then import
it to Excel.

Input:
Select field1, field2 FROM Table1

Output:
QUERY1
------
Table1.Field1
Table1.Field2

Of course the sample is a simple sql. The routine should handle complex
sql's.

If you only need to handle SELECT and UNION queries, then instead of
parsing the string, you could open a recordset based on each query, and
enumerate its Fields collection to get the field names. To make it run
faster, open empty recordsets:

SELECT * FROM [query name] WHERE False;

--
remove a 9 to reply by email

[David W. Fenton]

Dimitri Furman <df*****@cloud9 9.net> wrote in
news:Xn******** *************** *****@127.0.0.1 :

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11******** *************@f 14g2000cwb.goog legroups.com:

I know this is easy for you guys but I am not a VB developer. My
boss just wanted me to generate a mapping of all the queries in
our Access DB. So I managed to create some subroutines to get the
queries. I just need now to parse the SQL string and get the
field names. Then import it to Excel.

Input:
Select field1, field2 FROM Table1

Output:
QUERY1
------
Table1.Field1
Table1.Field2

Of course the sample is a simple sql. The routine should handle
complex sql's.

If you only need to handle SELECT and UNION queries, then instead
of parsing the string, you could open a recordset based on each
query, and enumerate its Fields collection to get the field names.
To make it run faster, open empty recordsets:

SELECT * FROM [query name] WHERE False;

There might be essential tables in the query that are not in the
output.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Dec 11 2004, 11:53 am, "David W. Fenton" <dX********@bwa y.net.invalid>
wrote in news:Xn******** *************** ***********@24. 168.128.86:

Dimitri Furman <df*****@cloud9 9.net> wrote in
news:Xn******** *************** *****@127.0.0.1 :

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11******** *************@f 14g2000cwb.goog legroups.com:

I know this is easy for you guys but I am not a VB developer. My
boss just wanted me to generate a mapping of all the queries in
our Access DB. So I managed to create some subroutines to get the
queries. I just need now to parse the SQL string and get the
field names. Then import it to Excel.

Input:
Select field1, field2 FROM Table1

Output:
QUERY1
------
Table1.Field1
Table1.Field2

Of course the sample is a simple sql. The routine should handle
complex sql's.

If you only need to handle SELECT and UNION queries, then instead
of parsing the string, you could open a recordset based on each
query, and enumerate its Fields collection to get the field names.
To make it run faster, open empty recordsets:

SELECT * FROM [query name] WHERE False;

There might be essential tables in the query that are not in the
output.

True, but the requirement, as I understand it, is to document the output,
not the entire query:

I just need now to parse the SQL string and get the
field names.

--
remove a 9 to reply by email

[Dimitri Furman]

On Dec 11 2004, 11:53 am, "David W. Fenton" <dX********@bwa y.net.invalid>
wrote in news:Xn******** *************** ***********@24. 168.128.86:

Dimitri Furman <df*****@cloud9 9.net> wrote in
news:Xn******** *************** *****@127.0.0.1 :

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11******** *************@f 14g2000cwb.goog legroups.com:

I know this is easy for you guys but I am not a VB developer. My
boss just wanted me to generate a mapping of all the queries in
our Access DB. So I managed to create some subroutines to get the
queries. I just need now to parse the SQL string and get the
field names. Then import it to Excel.

Input:
Select field1, field2 FROM Table1

Output:
QUERY1
------
Table1.Field1
Table1.Field2

Of course the sample is a simple sql. The routine should handle
complex sql's.

If you only need to handle SELECT and UNION queries, then instead
of parsing the string, you could open a recordset based on each
query, and enumerate its Fields collection to get the field names.
To make it run faster, open empty recordsets:

SELECT * FROM [query name] WHERE False;

There might be essential tables in the query that are not in the
output.

True, but the requirement, as I understand it, is to document the output,
not the entire query:

I just need now to parse the SQL string and get the
field names.

--
remove a 9 to reply by email