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. I am not demonstrating how to setup Oracle server and client because it is beyond the scope of this article. The screenshots are
Setup ODBC profile
Click on the Windows logo at the bottom left of the Windows Desltop, 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.
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 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
http://en.wikipedia.org/wiki/Open_Database_Connectivity
Access link to external ODBC data source
沒有留言:
張貼留言