SQL view - not user-friendly

[Jay]

I'm slowly trying to learn SQL in Access. Probably the usual way - for
every query I build with the QBE grid I then switch to sql view and get
to grips with the code. (That and the usual book learning etc.)

However, SQL view in Access is *so* unfriendly. The code in a one-colour
long stream of text makes it very difficult to parse & follow the syntax
etc.

I've seen our IT guys coding SQL and the interface they use (not sure of
the name) allows for indenting, colour-coding etc. so the code is much
easier to follow.

Is there a similar thing I can use in Access? Any kind of add-on or
something that makes query SQL view a bit more like this & more
user-friendly.

Any help greatly appreciated.

Regards,

Jason

(I know it's a long shot

 

[Dale Fye]

Not that I know of.

One of the problems with Access is the way that it wraps parenthesis around
joins when you have more than one in a query. Another thing I hate is all
the extra parenthesis in the WHERE clause when Access interprets the criteria
I enter in a query.

Just keep on plugging.

Dale

 

[John W. Vinson]

However, SQL view in Access is *so* unfriendly. The code in a one-colour
long stream of text makes it very difficult to parse & follow the syntax
etc.

Join the crowd, Jay. Everyone hates this!

I've even tried using UltraEdit to create a SQL style, copy the SQL text out
of Access into UltraEdit, edit it and paste back - but that becomes a major
hassle too.

This is certainly high on the list of suggestions the MVP's have made to
Microsoft.

John W. Vinson [MVP]

 

[Chris2]

Jason,

Comments in-line below.

I'm slowly trying to learn SQL in Access.

Fantastic! You're on your way to learning how to get more out of MS Access.

Probably the usual way - for
every query I build with the QBE grid I then switch to sql view and get
to grips with the code. (That and the usual book learning etc.)

SQL Tutorial:

http://www.w3schools.com/sql/default.asp

However, SQL view in Access is *so* unfriendly. The code in a one-colour
long stream of text makes it very difficult to parse & follow the syntax
etc.
(I know it's a long shot

You have hit upon one my greatest MS Access pet peeves.

Each QueryDef in MS Access has a property called SQL, it is basically a stream of
characters.

MS Access uses proportional fonts, which no programmer in the entire universe would ever
use. (I understand that in a version later than MS Access 2000, at least this it is
possible to pick a non-proportional font.)

You cannot embed comments.

You cannot do even some elementary editing (like cntl-A).

If you write SQL yourself and copy and paste it into place, MS Access usually reformats it
by removing line-breaks, spaces around operators, entering new () characters, and
replacing some () with []. In some cases, this insane and unwanted and incorrect
reformatting breaks the code you wrote.

When translating the Query Grid into SQL, MS Access creates code that is *horrifying*.

I get around this by developing all of my SQL in a real text editor so it is aligned in a
readable format.

I copy and paste the code into the SQL View of the QueryDef and then do testing.
Revisions, etc., all get done in the text editor and copied and pasted into MS Access as
necessary.

Yes, this is a kludge. I can't see a way around it. Writing and storing SQL directly in
MS Access is a lost cause. (I don't know if MS Access 2007 fixed any of these problems, I
can only hope.)

I've seen our IT guys coding SQL and the interface they use (not sure of
the name) allows for indenting, colour-coding etc. so the code is much
easier to follow.

MS Access would become much more friendly for developers if something like this existed.
(Hello, Microsoft . . .)


Sincerely,

Chris O.

 

[Jay]

Join the crowd, Jay. Everyone hates this!

I've even tried using UltraEdit to create a SQL style, copy the SQL text out
of Access into UltraEdit, edit it and paste back - but that becomes a major
hassle too.

This is certainly high on the list of suggestions the MVP's have made to
Microsoft.

John W. Vinson [MVP]

Wow, I've found exactly what I was looking for. It's called 'Quick
Query 1.1.1' and is a $20 add-on.

I'm just trying it on a 30 day free trial and it's a great little tool.

Once installed it's accessible via a button on the Access toolbar, and
when opened gives a 2-pane window. The top pane is to write the SQL.
The code is automatically colour-coded with a 4 colour system and also
allows indents and carriage returns for extra clarity. F5 executes the
code 'on-the-fly' as it were with no need to 'create' a new query. The
returned records are displayed in the pane below. There's also 'Save'
functionality which when used automatically saves the query to the open
database, as well as the ability to open already existing queries.

I've only been using it an hour and the speed with which it allows you
to trial-and-error while learning is great.

If anyone's interested google 'Quick Query+Access' and you can't miss
it. Alternatively see versiontracker.com,

Jason

 

[Hunter57]

Here is my review of the product. Just my opinion.

I gave the "Quick Query" a try and it does not work well with Access. It
does not accept criteria such as Like "A*". The font is tiny and there is
no apparent way to change it. If you load a saved query, you have to hand
type the query name to save it with the original name. If there is a problem
with the query you are left to guess at the cause. The add-in needs quite a
bit of improvement before I will use it. I have used some free query add-ins
that worked much better.

Hunter57
http://easymsaccess.blogspot.com/ (Access Tips)
http://www.ChurchManageSoftware.com

 

[Jay]

I agree that the font is small and if it could be increased would be a
big plus. I haven't come across the problem with wildcards yet as I've
only just downloaded the trial.

Not sure what you mean about having to re-type an opened query name,
there's a save function with just saves it with the original name.
There is a littlr prompting as regards possible problems with the query,
i.e it points to the FROM clause.

What other query add-ons could you recommend?

Jason

 

[Hunter57]

Hi Jay,

I have not found anything better that the Access Query Grid. I have read in
several places that there is nothing to match it in it's ability to help you
make powerful queries. I do agree with you that the SQL window could use
some improvement. But if you know how to format your SQL it is a great help.
For instance:

SELECT CustomerID, FirstName, LastName, Company, WorkPhone
FROM tblCustomers
WHERE Company Like "B*" And LastName Is Not Null
ORDER BY LastName, FirstName, Company;

I could post some awesome looking Union Queries, but I won't bore you with
them.

Here are a couple of interesting SQL tools. They can be helpful, but
limited. I don't use them much except for the V-Tools.

SQL Scratchpad add-in
http://www.mcwtech.com/downloads.aspx

FindRecord2k
http://www.mvps.org/access/forms/frm0045.htm

V-Tools
http://www.skrol29.com/dev/en_vtools.htm

To help with VBA MZTools is a great add-in with an abundance of quality
features. It is free and easy to use. http://www.mztools.com/v3/mztools3.htm

Regards,
Hunter57
http://easymsaccess.blogspot.com (Simple Access Tips)
http://www.ChurchManageSoftware.com