Call an Oracle stored procedure using VBA code

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.


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.

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

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
    Dim LSQL As String
    On Error GoTo Err_Execute
    Set db = CurrentDb()
    Set LSProc = db.CreateQueryDef("")
    'SQL to call stored procedure (with parameters)
    LSQL = "BEGIN application_program_interface (" & member_id & ", "
    LSQL = LSQL & provider_id & ", "
    LSQL = LSQL & "to_date('" & Format(service_date, "mm/dd/yyyy") & "','mm/dd/yyyy'))"
    LSQL = LSQL & "; END;"
    'Use {Microsoft ODBC for Oracle} ODBC connection
    LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"
    LSProc.SQL = LSQL
    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.