We help IT Professionals succeed at work.

I need a 3rd party tool for editing SQL text queries that supports connecting to MS ACCESS, I tired Oracle SQL Developer, but I can not get it to connect to MS ACCESS ?

Ay I
Ay I asked
on
I do a lot of development on MS ACCESS, but the interface for writing text SQL queries is very simple. I need to use a 3rd party tool for editing SQL text queries.  I tried using Oracle SQL Developer, as many sites on internet confirms it supports MS ACCESS, however that didn't work with me despite several attempts. Please recommend another tool ?
Comment
Watch Question

Mark EdwardsChief Technology Officer

Commented:
OK.  Oracle SQL Developer is for use with the Oracle database system.  Being able to "connect" to MS Access doesn't mean that you can use it to create MS Access queries - just Oracle SQL that can query MS Access database tables.  What "SQL" are you referring to here?
What do you mean by "the interface for writing text SQL queries is very simple".  Why is that bad in this case?  Are you trying to edit Access SQL queries in a text content?  Why not just switch your query to SQL View?
Access-Query-Builder.PNGQuery-Builder-SQL-View.PNG… or is this the "simple" you were talking about?  What are you looking for?  How much more "complex" do you need it to be?
Software & Systems Engineer
Distinguished Expert 2019
Commented:
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If your main task is formatting or documentation, I use Visual Studio Code and the SQL add-in, then copy-paste the SQL back and forth in the Access SQL editor. This is faster to work with than it sounds.

On-line services are also available, for example:

https://www.sqlinform.com/online-sql-formatter/
http://www.dpriver.com/pp/sqlformat.htm
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
I will add for formatting only I use Notepad++ with the Poor's man SQL formatting plugin

Author

Commented:
The Access SQL Editor mentioned earlier seems to work OK with MS ACCESS , it works well as it integrates with the existing MS ACCESS file and shows all existing queries.

However , I am worried if it is supported by Microsoft or not ?

but I a did a search on the web, youtube .. etc but didn't find much information about, like wether if it is supported by Microsoft or not ?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It is not. It is supported by the developer.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
If it was supported by Ms it would have 1 more 0 added to the price. .... :)
If you want something completely free Notepad++ is the best option i have found.
For the its price SQL editor should be the best
If you want the Ultimate SQL editor then SQL Assistant seems to be the best ...but its price is way beyond normal ...and its a bit heavy resources.
Mark EdwardsChief Technology Officer

Commented:
Just curious....  I've used advanced editors for SQL Server projects involving views and stored procedures.
Likewise, I've used Access query wizards and builder and VBA code to handle any complex situations in Access.
I've never had a need for anything fancy for Access.
Although I'll admit that it would be nice to be able to jump into a formula-1 racecar for a quick trip to the local grocer for a carton of milk, but it's not practical.
Why would you need a tool like an fancy SQL editor for Access? ...build it and they will come?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Ay I,

I think we are all a bit confused by your question...

The Access SQL Editor mentioned earlier seems to work OK with MS ACCESS , it works well as it integrates with the existing MS ACCESS file and shows all existing queries.
However , I am worried if it is supported by Microsoft or not ?

The Access query editor (Query By Example) is a part of Ms Access.
You are referring to it is as if it was a 3rd part app that you have to install, then it "integrates" with Access...

So in that sense, it *IS* supported by Microsoft, ...as it is part of MS Access.
So it confuses us when you ask "Is it supported", ...when you state that:
 "I do a lot of development on MS ACCESS"

So lets be clear....
When you say:
I do a lot of development on MS ACCESS, but the interface for writing text SQL queries is very simple.
...are you talking about the ms Access QBE interface that looks like this:
Ms Access QBE GridIf so, ...then this interface has Three views:
-Design View (The QBE grid in the image above)
-SQL view (Looks just like a text editor, where you type in the SQL manually)
-Datasheet View (Where you see the results of your query
..so which of theses views is the one that is "very simple", ...and clearly describe what is lacking.

Also you say:
it works well as it integrates with the existing MS ACCESS file and shows all existing queries.
In its most basic sense, The QBE grid displays one query at a time.
If you want to see:
"all existing queries",....
...you can see all the existing queries in the Navigation Pane, ....not in the QBE grid.

So lets be clear, ...can you post a screenshot of this "simple" interface you are referring to?
...and explain why it is lacking

Thank You

JeffCoachman