Execute a Stored Procedure, How to...

[Ed S.]

Trying to execute a stored procudure in VBA.
(Excel 97).

following is out of a textbook, but does not work - should be pretty close.

Does not recognize method on dim statments.

Set Con1 = New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Con1.Open "DSN=BD01;UID=PASSWORD;PWD=xxxx;"
Cmd1.CommandText = "{CALL QGPL.MYPROCTEST (?)}"
Cmd1.Execute
JobNum = Cmd1.Parameters(0).Value
Set Cmd1 = Nothing
Con1.Close
Set Con1 = Nothing

End Sub

Does Excel 97 suppport ADO technology

If can be done via sql, that's fine.

 

[dk]

You need to set the appropriate reference. From the VB editor choose Tools, References and tick Microsoft ActiveX Data Object 2.n Library where n depends on what version you have installed.

 

[Ed S.]

DK - thank you.

Q. how do i find/determine the ADO level installed?

Here is the finished code for the next person - works like magic...

Sub Connection()

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Con1.Open "DSN=MyDSN;UID=MYID;PWD=MyPWD;"
Cmd1.ActiveConnection = Con1
Cmd1.CommandText = "{CALL QGPL.MYPROCTEST (?)}"
Cmd1.Parameters.Refresh
Cmd1.Execute
JobNum = Cmd1.Parameters(0).Value
Set Cmd1 = Nothing
Con1.Close
Set Con1 = Nothing

End Sub

 

[dk]

On 2003-01-15 08:48, Ed S. wrote:
DK - thank you.

Q. how do i find/determine the ADO level installed?

You should see several versions of ADO installed. For example, my machine looks has versions 2.0, 2.1 and 2.5. To determine the most recently installed run this small piece of code:-

Sub GetADOVersion()
Set Conn = CreateObject("ADODB.CONNECTION")
MsgBox "You are using version " & Conn.Version & " of the MS ADO Library"
End Sub