Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Connecting Oracle to MS Access

Article by author Steve Callan

Will your Access and Excel users be able to view data within an Oracle database right now, or do they have to wait for a SQL or SQL*Plus class next month? Fortunately, for you and your users, giving them the ability to access an Oracle database using either Access or Excel is easy to do. It takes all of three minutes to set up the link or connection.

Moreover, even for DBA's in larger Oracle shops, knowing how to expose table data via these applications is a useful skill. Because Access and Excel can both be used to view table data within an Oracle database, the tools Oracle lacks with respect to data manipulation and analysis are more than made up for when using Access or Excel. With Access, users can also insert, delete and update data (Excel is view only).

Setting up the data source definition

If you expand the Start>Programs>Your Oracle Home program>Configuration and Migration Tools path, you will see a utility named Microsoft ODBC Administrator. Launch that utility to begin the process. If you have a fairly standard PC from any major vendor, it will already have definitions for dBASE, Excel and Access (note that these are all Microsoft products). Not to worry, because adding an Oracle database as a source is just a few clicks away. Shown below is a typical pre-Oracle view of the window.

Click on the Add button to add a new data source. In the window shown below, simply enter the name of your Oracle database, give it a description, enter the name of the service and provide a user ID (I added Scott's password so as not to be prompted for it down the road). If you want to test the connectivity, click on the Test Connection button (good idea).

If your connection test was successful, you will see the following:

After clicking OK on the Oracle ODBC Driver Configuration window, you will see your Oracle database listed in the ODBC Data Source Administrator window.

Now you are ready to make a link in Access.

Configuring Access to connect to Oracle

Configuring Access is just about as simple as the previous steps. In the next example, I created a new Access database and used it to read Scott's EMP table. I also updated data and added a new employee.

As a side note, Oracle has stated that the Scott schema has become a deprecated feature. It seems that future releases will be using the sample schemas (HR, SH, etc.) you see installed when using DBCA. If you want to keep the Scott schema, you can get its creation scripts from one of two places: ORACLE_HOME\rdbms\admin\utlsampl.sql and \sqlplus\demo\demobld.sql.

Start Access and create a new database. Go to File>External Data>Link Tables. In the Link window, you will have to scroll down the list shown in the Files of Type field. Select ODBC Database as shown in the screenshot.

Once you have done that, you will be prompted to select a data source. Switch tab views to the Machine Data Source tab. Recognize the items in that list? You should, because it is the same as what you previously saw in the ODBC Data Source Administrator window.

After highlighting your Oracle database data source, click OK. You will be presented with a list of tables. You can control-click on tables to add more than one at a time. Shown below is the area in the list where Scott's tables reside (somewhat towards the bottom of the list).

I selected BONUS, EMP, DEPT and SALGRADE. After selecting the tables, you will be ready to access them. Highlight one (EMP, in this case) and click Open.

A simple query in SQL*Plus confirms the update (LOOK HERE was typed in later). That was easy, but what if I had made a mistake? For example, what happens if I create a new employee, but violate a constraint? In the screenshot shown below, I attempted to insert a new employee named Linda Cole, but I used an existing empno. What does Access tell me about what happened?







 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks