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=10.1.4.1;Uid=xxxxx;Pwd=xxxxx;Library=PWRDTA41;QueryTimeout=0”
ConnectString = “Driver={ISeries Access ODBC Driver};System=10.1.4.1;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
Cells.Select
ActiveSheet.Range(“A7:Z65535”).ClearContents
‘copy the Recordset to excel sheet starting at A7
ActiveSheet.Range(“A7”).CopyFromRecordset RS
‘Close Connection and RecordSet
RS.Close
CS.Close
‘Reset cell back to A1
ActiveSheet.Range(“A1”).Select
End Sub
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.
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.
Many many thanks Brandon for this routine, it helped me a lot and it is wooooorrrrkinnng nooooww :-))) after days of searching
Catherine
Thanks!
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 ?)
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
André
Could you trim it out before writing to the cell? But I think you might have to do that field by field.
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!
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.
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.
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.