Link to an Oracle table using VBA code

Question: In 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.




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.


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