467,118 Members | 1,053 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

VB routine to parse SQL string and get the field names

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

Nov 13 '05 #1
  • viewed: 5861
Share:
13 Replies

"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.
Nov 13 '05 #2
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


Nov 13 '05 #3
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

Nov 13 '05 #4
PC Datasheet wrote:
This is a test - please ignore!!!


We always ignore your posts anyway Steve :-)

--
This sig left intentionally blank
Nov 13 '05 #5

"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.
Nov 13 '05 #6
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


Nov 13 '05 #7
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
Nov 13 '05 #8
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
Nov 13 '05 #9
PC Datasheet wrote:
This is a test - please ignore!!!


We always ignore your posts anyway Steve :-)

--
This sig left intentionally blank
Nov 13 '05 #10
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
Nov 13 '05 #11
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
Nov 13 '05 #12
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
Nov 13 '05 #13
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
Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by fowlertrainer@anonym.hu | last post: by
29 posts views Thread by gs | last post: by
7 posts views Thread by Bob Darlington | last post: by
5 posts views Thread by portCo | last post: by
16 posts views Thread by Chuck | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.