tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

MS Access: Call an Oracle stored procedure using VBA code in Access 2003/XP/2000/97

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:

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:

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.