Execute a Stored Procedure, How to...

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Execute a Stored Procedure, How to...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.
    EMSS

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    EMSS

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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:-

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com