By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,353 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,353 IT Pros & Developers. It's quick & easy.

VB routine to parse SQL string and get the field names

P: n/a
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
Share this Question
Share on Google+
13 Replies


P: n/a

"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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

"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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.