run a stored procedure on an IBM DB2

[Suttoa]

Hey guys,



So I am not very familiar with access VBA connection strings, or stored procedures on an IBM DB2 database. But from Access VBA I need to run a stored procedure on the IBM DB2.

Currently my ODBC driver connects me to the IBM DB2 mainframe with a saved uid/pw.. the vb code creates the string formatted in the way it needs to be sent is ok. But trying to run the stored procedure has me ramming my head into the wall..

Code for stored procedure:

Public Sub DB2SP_GS02NRSN_Command(strCriteria As String)
'   Used for DB2 Stored Procedure calls
    Dim DB2SPcon As ADODB.Connection
    Dim DB2SPcmd As ADODB.Command
    Dim DB2SPRS As ADODB.Recordset
    Dim DB2SPparm As ADODB.Parameter
    
    Dim DB2SPenv As ADODB.Connection
    Set DB2SPcmd = New ADODB.Command
    
    '---  Replace below highlighted names with the corresponding values, then open it - not used:
         'DB2SPcon = "Driver={IBM DB2 ODBC DRIVER};DSN=GOLDTEST;DBALIAS=alias;"
         'DB2SPcmd.Open ("DB2SPcon")


    Set DB2SPcmd = New ADODB.Command
    Set DB2SPcmd.ActiveConnection = "TEST"
    DB2SPcmd.CommandType = adCmdStoredProc
    DB2SPcmd.CommandText = "GOLDTEST.GS02NRSN"
'   Parameter list corresponds to Stored Procedure"s Linkage Section.
'   various error checking functions here


    Set DB2SPRS = DB2SPcmd.Execute

Right now when i try to run this I get a "compile error: Object Required" on the "TEST" name highlighted in red...
I thought maybe I need to establish a connection to the odbc driver (called GOLDTEST) highlighted in Green.. but then I get a "Compile error: Method or data member not found" on DB2SPCon

any help or thoughts on this would be nice.. I was reading about possibly using a "pass through query", but there are various error checking variables that need get filled in when this stored procedure is run.. so it sort of overcomplicated it..
I guess I just want to know if I am connecting properly..

Thank you!

[pbaldy]

ADO is not a strength, but I don't think you use Set for that line. Here's a snip from a working app:

    Set cmd = New ADODB.Command
    With cmd
      .ActiveConnection = objConn
      .CommandText = "procResAddNew" 
      .CommandType = adCmdStoredProc
      ...

In my case objConn is a connection object set in a separate procedure:

  Set objConn = New ADODB.Connection
  objConn.ConnectionString = dbConnectionString
  objConn.CommandTimeout = 5
  objConn.Open

[Suttoa]

Thank you, that worked for me!

[pbaldy]

Happy to help!