MS Access Queries with Oracle SQL Developer 1.2 Tool

Exclusive offer: get 50% off this eBook here
Oracle SQL Developer 2.1

Oracle SQL Developer 2.1 — Save 50%

Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook

$32.99    $16.50
by Jayaram Krishnaswamy | August 2007 | .NET Microsoft

This article by Jayaram Krishnaswamy shows how you can install the Oracle SQL Developer 1.2 and connect to an MS Access database. This article will cover the steps right from downloading and installing the Oracle SQL Developer, to connecting to a MS Access database, and using the SQL interface along with a few sample queries.

Introduction

In my previous article with the Oracle SQL Developer 1.1, I discussed the installation and features of this stand alone GUI product which can be used to query several database products. Connecting to an Oracle Xe 10G was also described. The many things you do in Oracle 10G XE can also be carried out with the Oracle SQL Developer. It is expected to enhance productivity in your Oracle applications.

You can use Oracle SQL Developer to connect, run, and debug SQL, SQL*Plus and PL/SQL. It can run on at least three different operating systems. Using this tool you can connect to Oracle, SQL Server, MySql and MS Access databases. In this article you will learn how to install the Oracle SQL Developer 1.2 and connect to an MS Access database. The 1.2 version has several features that were not present in version 1.1 especially regarding Migration from other products.

 

Downloading and installing the Oracle SQL Developer

Go to the Oracle site (you need to be registered to download) and after accepting the license agreement you will be able to download sqldeveloper-1.2.2998.zip, a 77MB download if you do not have JDK1.5 already installed. You may place this in any directory. From the unzipped contents, double click on the SQLDeveloper.exe.

The user Interface

On a Windows machine you may get a security warning which you may safely override and click on Run. This opens up the splash window shown in the next picture followed by the Oracle SQL Developer interface shown in the picture that follows.

Figure 1

The main window

The main window of this tool is shown in the next picture.

Figure 2

It has a main menu at the very top where you can access File, Edit, View, Navigate, Run, Debug, Source, Migration, Tools and Help menus. The menu item Migration has been added in this new version. Immediately below the main menu on the left you have a tabbed window with two tabs, Connections and Reports. This will be the item you have to contend with since most things start only after establishing a connection. The connection brings with it the various related objects in the databases.

View Menu

The next picture shows the drop-down of the View main menu, where you can see other details such as links to the debugger, reports, connections and snippets. In this new version many more items have been added such as Captured Objects, Converted Objects, and Find DB Object.

Figure 3

Snippets are often-used SQL statements or clauses that you may want to insert. You may also save your snippets by clicking on the bright green plus sign in the window shown, which opens up the superposed Save Snippet window.

Figure 4


In the Run menu item you can run files as well as look at the Execution Profile.

Debug Menu

The debug menu item has all the necessary hooks to toggle break points: step into, step over, step out and step to End of Method, etc., including garbage collection and clean up as shown in the next picture.

Figure 5


Tools Menu

Tools give access to External Tools that can be launched, Exports both DDL and data, schema diff, etc. as shown in the next picture.

Figure 6


Help gives you both full text search and indexed search. This is an important area which you must visit; you can also update the help.

Figure 7


About Menu

The About drop-down menu item in the above figure opens up the following window where you have complete information about this product that includes version information, properties and extensions.

Figure 8


Migration Menu

As mentioned earlier the Migration is a new item in the main menu and its drop-down menu elements are shown in the next picture. It even has a menu item to make a quick migration of all recent versions of MS Access (97, 2000, 2002, and 2003). The Repository Management item is another very useful feature. The MySQL and SQL Server Offline Capture menu item can capture database create scripts from several versions of MySQL and MS SQL Server by locating them on the machine.

Figure 9


Connecting to a Microsoft Access Database

If you are interested in Oracle 10G XE it will be helpful if you refresh your Oracle 10G XE knowledge or read the several Oracle 10G XE articles whose links are shown on the author’s blog. This is a good place for you to look at new developments, scripts, UI description, etc. This section however deals with connecting to an MS Access database.

Click on the "Connections" icon with the bright green plus sign as shown in the next figure.

Figure 10


This opens up the next window, New/Select Database Connection. This is where you must supply all the information. As you can see it has identified a resident (that is a local Oracle 10G XE server) Oracle 10G XE on the machine. Of course you need to configure it further. In addition to Oracle it can connect to MySQL, MS Access, and SQL Server as well. This interface has not changed much from version 1.1; you have the same control elements.

Figure 11


On the left hand side of this window you will generate the Connection Name and Connection Details once you fill in the appropriate information on the right. Connection name is what you supply; to get connected you need to have a username and password as well. If you want, you can save the password to avoid providing it again and again. At the bottom of the screen you can save the connection, test it and connect to it. There is also access to online help.
In the above window, click on the tab, in the middle of the page, Access. The following window opens in which all you need to do is to use the Browse button to locate the Microsoft Access Database on your machine (windows default for mdb files is My Documents).

Figure 12


Hitting the Browse button opens the window, Open with the default location, My Documents—the default directory for MDB files.

Figure 13


Choosing a database Charts.mdb and clicking the Open button brings the file pointer to the New / Select Database Connection in the box to the left of the Browse button. When you click on the Test button if the connection is OK you should get an appropriate message. However for the Charts.mdb file you get the following error.

Figure 14


The software is complaining about the lack of read access to the system tables.
Providing read access to System tables.
There are a couple of System tables in MS Access which are usually hidden but can be displayed using Tools option in MS Access.

Figure 15


In the View tab if you place a check mark for System objects then you will see the following tables. The System tables are as shown in the red rectangle.

Figure 16


If you need to modify the security settings for these tables you can do so as shown in the next figure by following the trail, Tools  Security User and Group permissions.

Figure 17


Click on the User and Group Permissions menu item which opens the next window Users and Group Permissions shown here,

Figure 18


For the user who is Admin, scroll through each of the system tables and place a check mark for Read Design and Read Data check boxes. Click on the OK button and close the application.
Now you again use the Browse button to locate the Charts.mdb file after providing a name for the connection at the top of the New / Select Database Connection page. For this tutorial MyCharts was chosen as the name for the connection. Once this file name appears in the box to the left of the Browse button, click on the Test button. This message screen is very fast (appears and disappears). If there is a problem, it will bring up the message as before. Now click on the Connect button at the bottom of the screen in the New / Select Database Connection page window. This immediately adds the connection MyCharts to the Connections folder shown in the left. The + sign can be clicked to expand all the objects in the Charts.mdb database as shown in the next figure.

Figure 19


You can further expand the Table nodes to show the data that the table contains as shown in the next figure for the Portfolio table.

Figure 20

The Relationships tab in the above figure shows related and referenced objects as shown. This is just a lone table with no relationships established and therefore none showing.

Figure 21

It may be noted that the Oracle SQL Developer can only connect to MDB files. It cannot connect to Microsoft Access projects (ADP files), or the new MS Access 2007 file types.

 

Using the SQL Interface

SQL Statements are run from the SQL Worksheet which can be displayed by right clicking the connection and choosing Open SQL Worksheet item from the drop-down list as shown.

Figure 22

You will type in the SQL queries in area below Enter SQL Statement label in the above figure (now hidden behind the drop-down menu).

Making a new connection with more tables and a relationship

In order to run a few simple queries on the connected database, three more tables were imported into Charts.mdb after establishing a relationship between the new tables in the access database as shown in the following figure.

Figure 23

Another connection named, NewCharts was created in Oracle SQL Developer. The connection string that SQL Developer will take for NewCharts is of the following format (some white spaces were introduced into the connection string shown to get rid of MS Word warnings).

@jdbc:odbc: Driver= {Microsoft Access Driver (*.mdb)}; DBQ=C:Documents and SettingsJayMy DocumentsCharts.mdb; DriverID=22;READONLY=false}

This string can be reviewed after a connection is established in the New / Select Database Connection window as shown in the next figure.

Figure 24

A simple Query

Let us look at a very simple query using the PrincetonTemp table. After entering the query you can execute the statement by clicking on the right pointing green arrowhead as shown. The result of running this query will appear directly below the Enter SQL Statement window as shown.

Figure 25

Just above the Enter SQL Statement label is the SQL Toolbar displaying several icons (left to right) which are as follows with the associated key board access:

<!--[if !supportLists]-->

  1. Execute SQL Statement(F9) ->Green arrow head
  2. Run Script(F5)
  3. Commit(F11)
  4. Rollback(F12)
  5. Cancel(CTRL+Q)
  6. SQL History(F8)
  7. Execute Explain Plan(F6)
  8. Autotrace(F10)
  9. Clear(CTRL+D)
  10. It also displays time taken to execute ->0.04255061 seconds.
<!--[if !supportLists]--><!--[if !supportLists]--><!--[if !supportLists]--><!--[if !supportLists]--><!--[if !supportLists]--><!--[if !supportLists]--><!--[endif]--><!--[if !supportLists]--><!--[if !supportLists]--><!--[if !supportLists]-->

The bottom pane is showing the result of the query in a table format. If you would run the same query with the Run Script (F5) button you would see the result in the Script Output tab of the bottom pane. In addition to SQL you can also use the SQL Worksheet to run SQL *PLUS and PL/SQL statements with some exceptions as long as they are supported by the provider used in making the connection.

Viewing relationship between tables in the SQL Developer

Three tables Orders, Order Details, and Products were imported into the Charts.mdb after enforcing referential integrity relationships in the Access database as seen earlier. Will the new connection NewCharts be able to see these relationships? This question is answered in the following figure.

Click on any one of these tables and you will see the Data as well as Relationships tabs in the bottom pane as shown.

Figure 26

<!--[if gte vml 1]> <![endif]--><!--[if !vml]--><!--[endif]-->Now if you click on the Relationships tab for Products you will see the display just showing three empty columns as seen in an earlier figure. However if you click on the Order Details which really links the three tables you will see the following displayed.

Figure 27

Query joining three tables

The Orders, Order Details, and Products tables are related by relational integrity as seen above. The following query which chooses one or two columns from each table can be run in a new SQL worksheet.

Select Products.ProductName,
Orders.ShipName,
Orders.OrderDate,
[Order Details].Quantity
from Products, Orders, [Order Details]
where Orders.OrderID=[Order Details].OrderID
and
Products.ProductID=[Order Details] and Orders.OrdersDate >
'12-31-1997'

The result of running this query (only four rows of data shown) can be seen in the next figure.

Figure 28

Note that the syntax must match the syntax required by the Provider ODBC, date has to be #12-31-1997# instead of ’12-21-1997’.

 

Summary

The article described the new version of the stand alone Oracle’s GUI SQL Developer tool. It can connect to couple of databases such MS Access, SQL Server, Oracle and MySQL. Its utility could have been far greater had it provided connectivity to ODBC and OLE DB. I am disappointed it did not, in this version as well. The connection to MS Access seems to bring in not only tables but the other objects except Data Access Pages, but the external applications that you can use are limited to Word, Notepad, IE etc but not a Report Viewer. These objects and their utility remains to be explored.  Only a limited number of features were explored in this article and it excluded new features like Migration and Translation Scratch Editor which translates MS ACCESS, SQL Server and My SQL syntaxes to PL / SQL. These will be considered in a future article.
Oracle SQL Developer 2.1 Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook
Published: December 2009
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Drupal 6 JavaScript and jQuery: RAW
Drupal 6 JavaScript and jQuery: RAW

WordPress Theme Design
WordPress Theme Design

WordPress for Business Bloggers
WordPress for Business Bloggers

WordPress Complete
WordPress Complete

Building Powerful and Robust Websites with Drupal 6
Building Powerful and Robust Websites with Drupal 6

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

Drupal 6 Themes
Drupal 6 Themes

Professional Plone Development
Professional Plone Development


Your rating: None Average: 4.3 (3 votes)
Thanks much by
Thanks alot for this tutorial..Was really helpful for me.
wonderful piece of information by
Great article. very nice

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
u
a
t
W
i
1
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software