Re: Calling an Oracle stored procedure from Microsoft Access 97

From: David Ball <dgb_at_ornl.gov>
Date: 2000/06/13
Message-ID: <39462C10.EE6ACCF2_at_ornl.gov>#1/1

Here's what worked:

 Dim Mydb as database
 Dim qdf as QueryDef

 Sub submitit_click()
 set Mydb = CurrentDb
 set qdf = Mydb.CreateQueryDef("temp_qry")

 qdf.connect = "ODBC;..."
 qdf.SQL = "BEGIN package_name.procedure_name(); END;"
 qdf.ReturnsRecords = False

 DoCmd.SetWarnings False
 DoCmd.OpenQuery "temp_qry"
 Mydb.QueryDefs.Delete "temp_qry"
 DoCmd.SetWarnings True
 End Sub

David Ball wrote:

> Hi,
> I am able to create an ODBC pass-through query using the Access Design
> Query tool with code like "begin packagename.procedurename; end;"; this
> works, but I would rather do this by code rather than having a permanent
> query. What is the best way to call an Oracle stored procedure with
> code in Access 97? I tried the code on pp.23-24 of "Using The Oracle
> ODBC Drivers with Third Party Products ":
>
> Dim oradb As database
> Dim Conn1 As String
> Dim sql1, ret
> Set oradb = OpenDatabase(“”, False, False, Conn1$)
> sql1 = “begin test_sp(‘one_a’,’two_a’); end;”
> ret = oradb.ExecuteSQL(sql1)
> oradb.Close
>
> but got the following error when trying to compile in Access: "function
> or interface marked as restricted or the function uses an Automation
> type not supported in Visual Basic." "ExecuteSQL" was highlighted.
>
> The following code in Access:
>
> Set Mydb = CurrentDb()
> strSQL = "begin packagename.procedurename; end;"
> set qdf Mydb.CreateQueryDef("",strSQL)
> qdf.Connect = "ODBC connect parameters"
> qdf.ReturnsRecords = False
> qdf.Execute dbSQLPassThrough
>
> yields error number #3129, "Invalid SQL statement, expected "delete",
> "select", etc." although the dbSQLPassThrough parameter was specified.
>
> The following code works but requires another logon to the database:
> Conn1$ = "ODBC connect parameters"
> set oradb = OpenDatabase("", False, False, Conn1$)
> sql1 = "begin packagename.procedurename; end"
> oradb.Execute sql1, dbSQLPassThrough
> oradb.close
>
> Access 97 help says that EXECUTESQL has been replaced by EXECUTE.
>
> Thanks,
>
> Dave Ball
> Lockheed Martin
Received on Tue Jun 13 2000 - 00:00:00 CEST