Call PL/SQL from MsAccess

From: Pardee, Roy E <roy.e.pardee_at_lmco.com>
Date: Thu, 14 Feb 2002 09:56:12 -0800
Message-ID: <F001.0040FF7A.20020214093840@fatcity.com>

Here's some code that sends two parameters--you should be able to adapt it to your needs. I'm using ADO 2.6.

---

S u b RunOracleSP()

Dim cmd As ADODB.Command
Dim con As ADODB.Connection
Dim prm As ADODB.Parameter

Dim Network As Object

Set cmd = New ADODB.Command
Set con = New ADODB.Connection

Set Network = CreateObject("WScript.Network")

   With con

.ConnectionString = "Provider=MSDAORA.1;" _

                        & "Password=*******;" _
                        & "User ID=rpardee;" _
                        & "Data Source=devl;" _
                        & "Persist Security Info=False"


      .Open

   End With

   With cmd

      Set .ActiveConnection = con


      .CommandType = adCmdStoredProc

      .CommandText = "RPARDEE.LOGLOGIN"

      Set prm = .CreateParameter("M_NAME" _
                              , adVarChar _
                              , adParamInput _
                              , 4000)


      .Parameters.Append prm

      Set prm = .CreateParameter("U_NAME" _
                              , adVarChar _
                              , adParamInput _
                              , 4000)


      .Parameters.Append prm

      .Parameters("M_NAME").Value = Network.ComputerName

      .Parameters("U_NAME").Value = Network.UserName

      .Execute , , adAsyncExecute

   End With

   Set Network = Nothing
   Set cmd = Nothing
   con.Close
   Set con = Nothing

End Sub

---

HTH, -Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----
Sent: Thursday, February 14, 2002 3:43 AM To: Multiple recipients of list ORACLE-L

Hallo,

anyone whom can give me an example onhow to write an MsAccess procedure which make a call to a pl/sqlprocedure. I want the MsAccess procedure send 5 parametsrs through to pl/sql.

Thanks in advance

Roland

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Roland.Skoldblom_at_ica.se

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pardee, Roy E
  INET: roy.e.pardee_at_lmco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Feb 14 2002 - 11:56:12 CST