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

[Daven Thrice]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Bx****************@newsread1.news.pas.earthli nk.net...

This is a test - please ignore!!!

Thanks for the warning. I don't need to be tested.

[Steve Jorgensen]

If you want to do it right, it's actually not easy at all - it's damn hard.
However, if you can be satisfied with 90% automnatic, and do the rest by hand,
it can be dome pretty simply.

Basically, use InStr to find where the word SELECT is, and then again to find
the next FROM, parse out that piece of the string using Mid$, then use Split
to get an array of items delimited by commas.

On 10 Dec 2004 15:52:47 -0800, te**@i-vibe.com wrote:

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

[PC Datasheet]

This is a test - please ignore!!!
<te**@i-vibe.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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.

TIA.
ross

[Trevor Best]

PC Datasheet wrote:

This is a test - please ignore!!!

We always ignore your posts anyway Steve :-)

--
This sig left intentionally blank

[Daven Thrice]

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Bx****************@newsread1.news.pas.earthli nk.net...

This is a test - please ignore!!!

Thanks for the warning. I don't need to be tested.

[Steve Jorgensen]

If you want to do it right, it's actually not easy at all - it's damn hard.
However, if you can be satisfied with 90% automnatic, and do the rest by hand,
it can be dome pretty simply.

Basically, use InStr to find where the word SELECT is, and then again to find
the next FROM, parse out that piece of the string using Mid$, then use Split
to get an array of items delimited by commas.

On 10 Dec 2004 15:52:47 -0800, te**@i-vibe.com wrote:

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*********************@f14g2000cwb.googlegro ups.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*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.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

[Trevor Best]

PC Datasheet wrote:

This is a test - please ignore!!!

We always ignore your posts anyway Steve :-)

--
This sig left intentionally blank

[Dimitri Furman]

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.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*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.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********@bway.net.invalid>
wrote in news:Xn**********************************@24.168.1 28.86:

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

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.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********@bway.net.invalid>
wrote in news:Xn**********************************@24.168.1 28.86:

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

On Dec 10 2004, 06:52 pm, te**@i-vibe.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.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