SQL in the VBA for Excel

[DaveMac]

Need help running SQL statement in excel.

Not sure where to begin but here is where I am.

I made an ODBC connection to an AS/400 DB

I have an SQL statement that when run in WIN_SQL runs fine.

I now need to add a text_box or cell that allows users to enter data and then click or something to run the query with that data as the where

So does anyone have a sample of the code to call the connection?
And then an example of how to pass the value?

Thanks!

 

[SkipVought]

Hi,

"I made an ODBC connection to an AS/400 DB"

Using ADO?

Using MS Query?

What?


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Can you use MSQuery (menu data -> external data -> ...) ?
You may then use a cell value as parameter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"I now need to add a text_box or cell that allows users to enter data and then click or something to run the query with that data as the where"

Using MS Query, as PHV has suggested, if you have a known set of values, which you should, as they are values in your table for that field, the cell containing the parameter value, can be selected from a Data > Validation-LIST drop down. Changing that cell value can also trigger execution of the query with the selected value.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

SkipVought I am sorry but I am not sure what you mean. I used ODBC connection in Admin tools and created a connection to the AS/400. I was thinking you could use that as an ADO connection, Right????

PHV I played with the Query tool however I could not get it to understand the Create Alias statement I need to properly query an AS/400 member file.

 

[SkipVought]

"I used ODBC connection in Admin tools and created a connection to the AS/400. "

You did not create a connection. You configrured a driver.

Can you not do...

Data > Import External Data > New database query - SELECT YOU AS/400 Driver...
???

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

SkipVought Yes sorry for the confusion but I just configured the driver as you said I now need to understand how to use that driver to make a connection in excel. The Excel query tool wants to help me however I already have a statement so I want to use my statement. I was thinking it would be something like this:

Dim as400conn as connection
‘somehow point at the driver
‘more connection stuff

‘start SQL

Declare SQL as lang????

CREATE ALIAS testfiles#.ordersdave FOR testfiles #.order_file (MBR5)

SELECT * FROM testfiles#.ordersdave where Order# = Cell A1

drop alias testfiles#.ordersdave

 

[SkipVought]

"however I already have a statement so I want to use my statement."

Why make it hard on yourself.

Use this in the SQL box...

SELECT * FROM testfiles#.ordersdave where Order# = ?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

What SQL box? When I goto Data> Query Database> I get this wizard not a text box.

What am I missing?

 

[PHV]

Uncheck the use wizard checkbox when you choose the data source...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

If you want to use ADO, you will need the ODBC data source configured. You may then use something like:

'Set reference to MS ADO in Tools>References
Dim rs as ADODB.recordset
Dim strCon as ADODB. connection
Dim strSQL as string

set strcon = new ADODB.connection

strcon.open "ODBC;DSN=ODBC Driver Name"

strSQL = "SQL String here"

set rs = strcon.execute(strSQL)

'work with rs - fields collection for headers, copyfromrecordset method to dump data to worksheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveMac]

When I go to connect to new data source it always fails. I have found that MS products do not like the AS/400 DB2 file system. There are not tables for it to connect to so I cannot get the new data source registered.

I know there is a way to make this work because I have seen an AS/400 queried from an excel sheet.

Are there other ways to establish a connection to an ODBC driver?

 

[xlbo]

If you are doing it in VBA as I have shown, all you do is reference the appropriate ODBC driver (which can certainly be configured - I have done so for our JDE AS400)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Could you explain, as DB2 is a DATABASE and not a file system.

" the AS/400 DB2 file system. There are not tables for it to connect to ..."

If your have flat files, you might need a TEXT driver. It becomes more involved to query.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

xlbo thanks for the help, same to PHV and SkipVought
So my ODBC drive is called

“Site_DB2”

'Set reference to MS ADO in Tools>References
‘ was not sure about this ref piece
Dim rs as ADODB.recordset
Dim strCon as ADODB. connection
Dim strSQL as string

set strcon = new ADODB.connection

strcon.open "ODBC;DSN=Site_DB2"

strSQL = " CREATE ALIAS testfiles#.ordersdave FOR testfiles #.order_file (MBR5)

SELECT * FROM testfiles#.ordersdave where Order# = Cell A1

drop alias testfiles#.ordersdave"

set rs = strcon.execute(strSQL)

‘if I rename the fields via the AS in my SQL I would hope I could just ‘start at A1 and drop all data vertically?????

Does that look right for the connection?

 

[xlbo]

You need to execute each SQL statement individually:

strSQL = " CREATE ALIAS testfiles#.ordersdave FOR testfiles #.order_file (MBR5)"

set rs = strcon.execute(strSQL)


strSQL = "SELECT * FROM testfiles#.ordersdave where Order# = '" & range("A1").value & "'"

set rs = strcon.execute(strSQL)

'process getting data into excel at this point

strSQL = "drop alias testfiles#.ordersdave"

set rs = strcon.execute(strSQL)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

strSQL = " CREATE ALIAS testfiles#.ordersdave FOR testfiles #.order_file (MBR5) "

strSQL = strSQL & "SELECT * FROM testfiles#.ordersdave where Order# =" & [A1]

assuming that Order# is a numeric data type (which it should not be as you'll never do arithmetic to it. is should be a STRING. it is an identifier)

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

Sorry but I am limited on my knowledge of the AS/400 but the way it has been explained to me by our RPG programmers is that the AS/400 is different in that you have a file that could be seen as a single member therefore it says file lets say "sales" is a file that contains all “sales” this is what I would be familiar with. Or you could have “sales” and it is broken apart into the 20 different sub files ( even though they are still one file) they reside at the Member level so you wind up with “sales” and it is Member1, Member2, and Member3 and then file “Orders” which is Member01 which is called a single member file. The bottom line is software that is not designed to administer this relationship does not respond well and consistently ie MS SQL and WIN SQL and Access. Like I said I am no where near qualified to really say I am only passing experiences on.

 

[SkipVought]

"you could have "sales" and it is broken apart into the 20 different sub files "

That is a conceptual "model" that someone might find helpful to grasp, but that's not what you have, I believe.

You do have DB2 database, that can be queried using SQL, from my brief research.

http://en.wikipedia.org/wiki/AS/400

You just may not have staff that has ever done that.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveMac]

Skip – You are correct! I have been very disappointed in the gap between the AS/400 world and the next level SQL world. I thought doing what I am doing which is allowing the high level RPG 3 and 4 programmers do the actual ERP work and I am really just looking for data and some better interface options. Without always asking for a single member logical I am creating my own on the fly with my create alias statement. I have had to do things like this to avoid having to make a simple request like a simple query turn into 4 meeting with 20 programmers. There is some information out there but the AS/400 does not lend itself to easy access with the microsoft suite like other DB types.

Thanks for the help!