MS Access 2003: Call an Oracle stored procedure using VBA code

This MSAccess tutorial explains how to call an Oracle stored procedure using VBA code in Access 2003 (with screenshots and step-by-step instructions).

Question: I'm using Access 2003/XP/2000/97 as the front-end development tool and Oracle RDBMS as the back-end database. There are packages, procedures, and functions in the Oracle database that I'd like to call from Access. How do I execute or call an Oracle stored procedure from Access?

e.g. application_program_interface(member_id, provider_id, service_date)

Answer: To call an Oracle stored procedure, you'll have to create a query using VBA code.

The first thing you need to do is create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.

To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.

Microsoft Access

Set up your ODBC connection.

In this example, we've setup the Data Source with a name of AAAA, with a user name of BBBB, and an Oracle server called CCCC. You'll need to configure the ODBC connection with your own settings.

Microsoft Access

Stored procedure without any parameters

This first example below would call a stored procedure call application_program_interface without any parameters passed into the stored procedure.

Open your Access database, click on the Modules tab and create a new Module.

Paste in the following code:

Function CallSProc() As Boolean

   Dim db As Database
   Dim LSProc As QueryDef
   On Error GoTo Err_Execute

   Set db = CurrentDb()

   Set LSProc = db.CreateQueryDef("")

   'Use {Microsoft ODBC for Oracle} ODBC connection
   LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"
   LSProc.SQL = "BEGIN application_program_interface; END;"
   LSProc.ReturnsRecords = False
   LSProc.ODBCTimeout = 0

   LSProc.Execute

   Set LSProc = Nothing

   CallSProc = True

   Exit Function

Err_Execute:
   MsgBox "The call to the Oracle stored procedure failed."
   CallSProc = False

End Function

Please note that you'll need to customize the following line of code:

LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

So that:

AAAA is the name of the ODBC Data Source that you set up.
BBBB is the user name that you will use to log into Oracle.
CCCC is the name of your Oracle server.
DDDD is the password that you will use to log into Oracle.

If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.

Important Tip: While it is possible to use Access to call an Oracle stored procedure, it is not possible to retrieve a status back from Oracle as to whether the Oracle stored procedure successfully executed. What we recommend is creating an audit table and then having your stored procedure write a record to this audit table indicating the status of the stored procedure. That way, you can query this table from Access to see if your stored procedure succeeded.

Stored procedure with parameters

This second example below would call a stored procedure call application_program_interface with parameters as follows:

application_program_interface (member_id, provider_id, service_date)

where:

member_id is a numeric value
provider_id is a numeric value
service_date is a date value