Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011

    Unanswered: Access Calling Oracle Stored Procedure

    I searched around a bit on the forum but didn't find a direct answer for my question.

    Anyone have experience calling an Oracle Stored Procedure from access and passing it parameters?

    I am trying to call a simple insert/update/delete procedure for an old application.

    Thanks in advanced and my search continues.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Here's an example using DAO:
    Public Function Execute(ByVal Connection As Variant, _
                            ByVal Name As Variant, _
                            ByVal Argument As Variant, _
                            ByVal Persistent As Variant) As Boolean
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strName As String
        On Error GoTo Err_Execute
        ' Process parameters.
        Connection = Trim(CStr(Connection))
        Name = Trim(CStr(Name))
        Argument = Trim(CStr(Argument))
        Persistent = CBool(Persistent)
        strName = "Qry_" & Name
        ' Concatenate procedure name and its arguments (if any).
        strSQL = Name
        If Len(Argument) Then strSQL = strSQL & " " & Argument
        ' Delete former same QueryDef (if any) in the database.
        DeleteQueryDef strName
        ' Create the QueryDef.
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef
        With qdf
            .Connect = Connection
            .Name = strName
            .SQL = strSQL
            dbs.QueryDefs.Append qdf
        End With
        ' Clean up.
        If Persistent = False Then DeleteQueryDef strName
        Set dbs = Nothing
        Set rst = Nothing
        Set qdf = Nothing
        Exit Function
        ' Handle error here
        Resume Exit_Execute
    End Function
    Private Function DeleteQueryDef(ByVal QueryName As String) As Boolean
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            If qdf.Name = QueryName Then
                DoCmd.DeleteObject acQuery, QueryName
                Exit For
            End If
        Next qdf
        Set qdf = Nothing
        Set dbs = Nothing
    End Function
    You can call the Execute function where:
    - Connection is a connection string for Oracle, such as:
    provider=OraOLEDB.Oracle;DataSource=<Database Path>;User ID=<Username>;Password=<Password>;
    For more information about connection string, see: Connection Strings

    - Name is the name of the stored procedure.

    - Argument is a string containing the parameter(s) (if any) passed to the procedure.

    - Persistent indicates whether the created query must be kept or not (usefull if you want to call a stored procedure that returns rows of data: you can open the created query to access these rows.

    There are other methods using ADO. See: DAO Advanced Programming with Microsoft Access for ADO in general and : ADO Tutorial for a more practical approach.
    Have a nice day!

Posting Permissions

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