totn Access

MS Access 2003: Link to an Oracle table using VBA code

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

Question: In Microsoft Access 2003/XP/2000/97, I'd like to link to an Oracle table using VBA code. How can I do this?

Answer: 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

Then you can execute the following code:

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC", acTable, "schema.table_name", "Access_table_name", False, False

Please note that you'll need to customize the above line of code, 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.
schema.table_name is the table in Oracle that you wish to link to. For example, "collect.suppliers".
Access_table_name is the name that you'd like Access to assign to the linked table. For example, "suppliers".

Share on: