CBlog(handziuk)

Bradley Handziuk's blog

Use SQL Developer to query Access

Writing queries in Access can be ugly because it doesn't save any of your formatting. Well it does but then if you make any change in the designer it messes it all up again. Either way it can be a real pain to write queries in Access and view results smoothly in a sort of more robust program style (like SSMS) where the results appear below your query as opposed to opening up a new view where you can only see your query or result and not both at once.

In comes Oracle's SQL Developer. It provides all that for Access.

There are a few catches:

  • You need to install a version of the Java Development Kit (JDK) which is in the 1.7.X range and for "Windows x86". Newer versions in the 1.8.X range are available from Oracle but they are not supported by SQL Developer. When you install a newer version SQL Developer will open and will connect to an Oracle DB but you will not have the option to connect to an Access DB. The option won't even show up in the Connections dialog.
  • You need to use the "Windows 32/64-bit" version. There is a 64 bit version ("Windows 64-bit") available but because SQL Developer relies on using ODBC for connecting to Access and since Access is 32 bit there is only a 32 bit ODBC driver provided by Microsoft. The 64 bit version gives you the option to connect but will give the error 
[Microsoft] [ODBC Driver Manager] data source name not found and no default driver specified

Install the JDK first, get the installation path of where it installed (probably something like C:\Program Files (x86)\Java\jdk1.7.0_75), then install SQL Developer. The SQL Developer will probably prompt you for where the version of the JDK you want to use is.

After installation you can add your connection to Access. Click New Connection...

Name your connection, click to the Access tab, navigate to your mdb/accdb file, then click Connect.

You should now see the tables and view in your database in the connections window

You can look at table data by clicking on the table. You can look at query data by Select * from yourQuery.

You can make new queries like

CREATE VIEW MyNewView AS
Select * from Table1

If you want to ALTER Access doen't use the ALTER VIEW syntax so you need to DROP and CREATE again. However it also doesn't call views VIEW, it calls them TABLE. So if I wanted to change MyNewView I'd have to do it like

DROP TABLE MyNewView 
CREATE VIEW MyNewView AS
Select * from Table2

You can save out your queries as .sql files so they don't clutter your database.

I have not seen away to grab the existing definition yet.

Loading