Access link to external ODBC data source

This tutorial shows how to link Access to external ODBC data source.

Access link to external ODBC data source

ODBC (Open Database Connectivity) is a standard programming language middleware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.

The reason for connecting Access to ODBC is that Access can directly get database data once the connection is setup, saving a lot of time to make  Query through the database system storing the data.

In this article, I will demonstrate how to setup ODBC connection for Oracle Peoplesoft HCM database as an example, you can do the same steps even if you are not using Oracle. I am not demonstrating how to setup Oracle server and client because it is beyond the scope of this article.

Setup ODBC profile

Click on the Windows logo at the bottom left of the Windows Desktop, search “odbcad32.exe”, and then open it.


Click on Add button > Select “Oracle in OraClient 11g_home1” > Finish


Fill in all the information.


“Data Source Name” and “Description” do not matter, only “TNS Services Name” and “User ID” (case insensitive) are used to log in to the database. Ask your DBA to setup an account if you don’t  have an account.

You can click on the “Test Connection” button and input password to test if the account can connect to Oracle successfully.

Change Oracle password using SQL Plus:

Click on the Windows logo at the bottom left of your screen, search “cmd” and open Command Prompt


Type the following code to connect

sqlplus <UserID>@<HCM Instance>

Then type in the password

Connect Oracle Database from Access

In the Access Menu, click on External Data tab > ODBC Database


Select “Link to the data source by creating a linked table”, this allows you to get real time information in Oracle database.


Selectt he Data Source Name


Login to ODBC connection


Upon successful login, a list of Oracle Peoplesoft tables are listed, select a table that you want to link, you can also select multiple tables in this screen.


Outbound References

Leave a Reply

Your email address will not be published.