Execute iSeries Access Stored Query

[stones...@gmail.com]

I have a query stored on the AS400 that I am trying to execute from Excel VBA.
The following code bombs at "PGM.Execute Rcds" and gives an error message:

Sub QryTest()
Dim Conn As New ADODB.Connection
Dim Pgm As New ADODB.Command

Conn.Open "Driver={iSeries Access ODBC Driver};System=OurSys;TRANSLATE=1;"";"";"
Set Pgm.ActiveConnection = Conn

Pgm.CommandText = "{CALL " + "RUNQRY OurLib/MyQry" + ".RMTSTRT}"
Pgm.Execute Rcds
Conn.Close
End Sub

The error message is:

Run-time error '-2147217900 (80040e14)':

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token OURLIB was not valid. Valid tokens: (INTO USING.

I found the code on the internet and made the necessary changes for our application. I don't know why the "+" signs are used in the the PGM.CommandText statement - it looks like the statement could be enclosed in quotes without concatenation.

Can someone please help?

Thanks in advance.

[CRPence]

I think referencing an example culled from a message stating that
they do not have the function working properly is not the best approach;
i.e. from:
_i Excel/Dim error i_
http://en.allexperts.com/q/Excel-1059/Dim-error.htm
"... however I am having problems with a 'Dim' statement. The macro
below will not execute because I get an error message when I try to run
the macro"
For lack of any follow-up there is no way to know if they might have
had a slew of other difficulties beyond the initial error they recorded.

In review of that example... With the "CALL" I would expect that was
possibly intended to be an SQL CALL, especially with the apparent SQL
naming for a ".NAME" suffix. As well, for both failure to have set the
Pgm.CommandType=adCmdText versus the apparent default setting of
Pgm.CommandType=adCmddStoredProc and that Execute seems to expect a
result set of Rcds which is something that the CL RUNQRY can not
provide. However the text "RUNQRY Lib/Qry" between the "CALL" and the
apparent procedure ".NAME" seems like a CL command invocation for which
both a SQL and a CL "CALL" are incompatible. I expect either that the
SQL syntax for invoking the system-provided CL interpreter would have
been used instead to invoke a RUNQRY CL command via "CALL QSYS.QCMDEXC
('RUNQRY OurLib/MyQry', 0000000019.00000)", or the SQL syntax to call a
stored procedure "CALL SomeLibr.RMTSTRT" which returns a result set to
the client. In other words, what they have in that example seems [they
were] a bit confused.

I am a bit confused only by the expected as-valid next token for the
SQLcode -104; i.e. I would have expected the SQL error to suggest
instead, an <END-OF-STATEMENT>.

Some better examples might be found using on of:
https://www.google.com/search?q=".CommandText"+"runqry"
https://www.google.com/search?q=".CommandText"+"chgjob"

_i More AS/400 Client/Server Programming with ADO and VBA i_
Written by Michael Sansoterra
Wednesday, 31 March 1999 18:00
http://www.mcpressonline.com/programming/apis/more-as400-clientserver-programming-with-ado-and-vba.html
<Figure 6>
Dim ADOCmd As ADODB.Command
With adoCMD
.ActiveConnection = gadoConn
.CommandText = "{{CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES) }}"
.Execute adCmdText
. . . Additional ADO Code Goes Here
if err_flag then
.CommandText = "{{OVRPRTF QPJOBLOG OUTQ(PRT01) OVRSCOPE(*JOB) }}"
.Execute adCmdText
.CommandText = "{{DSPJOBLOG OUTPUT(*PRINT) }}"
.Execute adCmdText
end if
End With
</Figure 6>: The command data type provides an easy way to execute
AS/400 commands

Without .CommandType set, notice the command embedded between two '{'
characters a command delimiter; i.e. perhaps that syntax tells the ADO
to direct the request to the CL interpreter versus the SQL, irrespective
the [defaulted] "stored procedure" expectation?

Anyhow, hopefully a non-problem web document will assist better than
the originally referenced document which was opened as a "problem" and
for which any resolution\corrective actions were left unstated.

Regards, Chuck