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.
