I have done it in Access 2000 and I assume it would work in newer versions but here's the basic steps.
1. create a blank query called whatever you want to name it.
2. Go to the SQL view and change the query type to a Pass-Through Query.
3. Call your package/stored proc just as if you were in SQLPlus
4. Off of the View menu, Click on the Query Properties. One of the properties is an ODBC Connection String.
You're done. What the pass through does is allow you to type in PL/SQL or T-SQL or any non-access SQL and execute. Access assumes you know what you are doing. You can just call
"Exec package_name.stored_proc param1, param2" and as long as your proc returns a cursor you will get it.
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ActiveConnection = "Your Connection String Here"
'If you are not returning records
cn.Execute "Exec StoredProcedureName", adExecuteNoRecords
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!