Calling an Oracle stored procedure from Microsoft Access 97

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 Wed Jun 07 2000 - 00:00:00 CEST