---

MS Access 2003: Connect to Oracle using VBA code

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

Question: I'm developing an application using Access 2003/XP/2000/97 as the front-end and Oracle as the back-end database. I've run into an scenario wherein I'd like to suppress the "ODBC Driver Connect" window prompt that asks for the username and password to connect to the Oracle database.

So I've created a form called "Login", but I need help writing the VBA code that would connect to Oracle. Can you help?

Answer: To connect to Oracle as you described above, you'll have to first 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.

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

Paste in the following code:

Function OracleConnect() As Boolean

   Dim ws As Workspace
   Dim db As Database
   Dim LConnect As String

   On Error GoTo Err_Execute

   'Use {Microsoft ODBC for Oracle} ODBC connection
   LConnect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

   'Point to the current workspace
   Set ws = DBEngine.Workspaces(0)

   'Connect to Oracle
   Set db = ws.OpenDatabase("", False, True, LConnect)

   db.Close

   OracleConnect = True

   Exit Function

Err_Execute:
   MsgBox "Connecting to Oracle failed."
   OracleConnect = False

End Function

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

LConnect = "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.