Recently I had to help someone by creating a small piece of VBA code that would fetch rows returned by an ISeries stored procedure. The stored procedure however required two parameters to be set; a from date and a to date. Since this took some time to research I thought I’d post a simplified version on here for myself as well as anyone who needed the reference.
Note: It is highly recommended that you use parameterized queries and not simply a concatenation of values. https://support.microsoft.com/en-us/kb/181734
The following code gets an user input start date (in YYYYMMDD format) from cell B1 and an end date from cell B2. It then places those dates into the stored procedure as parameters. Any resulting rows (with the exception of headers) are displayed starting from cell A6.
An important thing to note here is the use of
in the connection string. Using this keeps your procedure call from timing out.
Additionally note how instead of using a loop, rows returned are simply placed from the recordset to cells A6+ using
Sub UpdateQuery() If IsNumeric(Range("B1").Value) Then intStart = Range("B1").Value Else Exit Sub End If If IsNumeric(Range("B2").Value) Then intEnd = Range("B2").Value Else Exit Sub End If Dim ConnectionString As String ConnectionString = "Driver=iSeries Access ODBC Driver;System=ISERIES.COMPANY.COM; QUERYTIMEOUT=0;UID=Username;pwd=Password;" Dim Cn Set Cn = CreateObject("ADODB.Connection") Cn.ConnectionTimeout = 0 Cn.Open ConnectionString Set Geti5Connection = Cn Dim Cm Set Cm = CreateObject("ADODB.Command") Set Cm.ActiveConnection = Geti5Connection Cm.CommandText = "call mylib.myproc(" & intStart & "," & intEnd & ")" Set Geti5Command = Cm Set Rs = Cm.Execute Range("A6").CopyFromRecordset Rs Set Cm = Nothing End Sub