Home > Excel, ISeries (AS/400), VBA > Excel VBA to query ISeries DB2 connection using connectionless ISeries ODBC Driver

Excel VBA to query ISeries DB2 connection using connectionless ISeries ODBC Driver

Below is a VBA subroutine executed from excel to query an ISeries
table using the ISeries Access ODBC Driver. I need to clean up
a little and declare my variables etc, but it works. I’ve always
found it difficult to get a complete example of this on the Web.
I’m sure it’s out there but searching for me has been time consuming.
I’ve had to put bit’s and pieces together to get it to work the way I want.
It’s nice because the ODBC connection is connectionless so the user doesn’t
have to have it defined on their machine. s long as the have client access
and a login it should work.

Sub transfer()

Dim varState As String

Set CS = CreateObject(“ADODB.Connection”)
Set RS = CreateObject(“ADODB.Recordset”)

‘Retrieve values from Cells on the sheet to use as selections in the query below
varState = ActiveSheet.Range(“C2”).Value
varFrom_Date = ActiveSheet.Range(“B3”).Value
varTo_Date = ActiveSheet.Range(“B4”).Value

‘ ISeries connection String
‘ConnectString = “Driver={ISeries Access ODBC Driver};System=;Uid=xxxxx;Pwd=xxxxx;Library=PWRDTA41;QueryTimeout=0”
ConnectString = “Driver={ISeries Access ODBC Driver};System=;Library=PWRDTA41;QueryTimeout=0”

CS.Open (ConnectString)

SqlString = “SELECT hhicusn , C.FFDCNMB, C.FFDSTEB, hhiclsn, SUM(hhiqysa), SUM(hhiexsn), SUM(hhiexac) ” & _
” FROM pwrdta41.hhiorddp ” & _
” left outer join PWRDTA41.FFDCSTBP c” & _
” ON hhicusn = c.ffdcusn and” & _
” hhidivn = c.ffddivn and” & _
” c.ffdcmpn = hhicmpn and” & _
” c.ffddptn = hhidptn” & _
” WHERE hhidtei between ” & varFrom_Date & ” and ” & varTo_Date & _
” and hhiclsn = ‘105’ and c.ffdsteb = ‘” & varState & “‘” & _
” GROUP BY hhicusn, c.ffdcnmb, c.ffdsteb, hhiclsn” & _
” ORDER BY hhicusn”

‘Message box can be used for debugging the SQL statement
‘MsgBox (SqlString)

RS.Open SqlString, CS

‘Clear Previous contents of Cells

‘copy the Recordset to excel sheet starting at A7
ActiveSheet.Range(“A7”).CopyFromRecordset RS

‘Close Connection and RecordSet

‘Reset cell back to A1

End Sub

  1. April 16, 2012 at 11:48 am

    Is there a way that a user can connect to iSeries without through VBA without needing the iSeries client? I am trying to pull some data, but thus far the only way I have found is to install the iSeries client.

    I am hoping there is a small set of *.dlls that are necessary for the connection.

    • bparman
      April 16, 2012 at 4:46 pm

      I’ve read you can use the universal DB2 drivers now, but I haven’t tried personally. It might depend on the OS Version of the ISeries also.

  2. Catherine
    November 7, 2012 at 10:05 am

    Many many thanks Brandon for this routine, it helped me a lot and it is wooooorrrrkinnng nooooww :-))) after days of searching

    • bparman
      January 15, 2013 at 9:58 am


    • André Ledoux
      January 16, 2013 at 2:44 pm

      I’m actualy using the trim, but only once the subroutine transfer is done. So it’s kind of a patch. I was (hopping) thinkning that since the Iseries transfer add-in is keeping the format, there might be a way to set it up in the subroutine parameters (could it be define in the SQL script ?)

  3. André Ledoux
    January 11, 2013 at 11:33 am

    Thanks for your initiative. It helped me alot.
    Although, i’m having a problem with the field format.
    When using Iserie Add in button, the formating is good. But with the VBA code, It’s all gone. My major broblem is the blank space filling the end of the cells. Is there a way to set this up in the retreving process.

    Thank you

    • bparman
      January 15, 2013 at 10:00 am

      Could you trim it out before writing to the cell? But I think you might have to do that field by field.

  4. October 4, 2013 at 11:05 am

    Thank you for posting this but I am having a problem that only numerical is being returned to the spreadsheet. I have attempted a search but cannot find anything that addresses this problem. Also, is it possible to pass the credentials from client access to the query?

    Thank you!

    • bparman
      October 4, 2013 at 2:47 pm

      If you don’t specify a user and password it will depend on the way the connection is set up in Client access, it will either prompt or use the existing credentials if there is already a connection open. I’m not sure why it would not return the text from the field, maybe make sure it’s showing all the cell? The fields where set up as fixed with Alpha on Iseries. It may not work for variable, I’m not sure.

  5. azohawk
    October 29, 2014 at 1:52 pm

    Anthony, the issue with the blank text fields sounds like the same issue I’m having. If you look at the file on the iSeries, I am guessing that you will find that the character set id is 65535. I am trying to figure a way aournd this w/o creating duplication of effort on my iSeries.
    I did a work around this before when I was pulling a lot of data and had a workfile on the i that I downloaded into excel via VBA. I recompiled my workfile wich CCSID = 37.
    But since a) this is a very simple select/where query-why should I have to go through extra steps and b) I expect to see this again in the future. I am looking for a good solutions that I can use moving forward.

  6. John
    September 11, 2015 at 4:54 pm

    Thanks 🙂
    I did struggle with this a bit at first but eventually got it working. I had to replace all of the Quotes and remove the { }. I am using it in a Access 2013 DB.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: