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
- 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.