Tidying Up Access SQL (2003)

Topic

New Reply

Hi,

I’ve just moved to a new job and it seems I’ll be using Access a lot more than I’d like to (we don’t have SQL Server licenses at the moment).
So I was just wondering if you had a few neat tips for coding using the SQL View.

First off: there is no way to comment a query, right?

Secondly: how do you indent your query? Tabs don’t seem to work. I tried using ten spaces to substitute them, but Access seems to remove the spaces when I close and reopen the query.
Third: is there any way to prevent Access from replacing brackets “( )” with square brackets “[ ]”? I don’t quite remember now, but I guess when the query contained a subquery in the form:

Select *
from (subquery) as subqueryname;

on query save and close Access would keep:

Select *
from [subquery] as subqueryname;

or something.

Fourth: any other suggestions?

I hadn’t polished these aspects yet as I had rather occasional need to use Access SQL, but now the time has come when coding more properly will make my life happier.

Thank you all in advance

Reply | Quote

Replies

1) No, comments are not possible.
2) No, indenting is not possible.
3) No reason to bother.
4) Create a query in design view as much as possible, and only switch to SQL view when you really need it.

Reply | Quote

Hello Hans,

Thanks for your promptness!!

The reason why I bother in 3) is that I remember having written a complex query with many subqueries. Some time later I needed to edit it, and when my edition was complete I tried saving the query but Access wouldn’t let me. The cause of trouble was that Access had replaced the brackets with square brackets and conspicuously required me to restore them to normal brackets.

Reply | Quote

I guess something was wrong with your query, but without knowing details it’s impossible to say what.

Reply | Quote

Access did this consistently in A97, but became more accepting of inline subqueries in 2000 and above. Even in XP it would sometimes choke on the punctuation, but in that version, at least, it understood that square brackets followed by a period were acceptable punctuation for an inline subquery.

Reply | Quote

Thanks Charlotte.

Now that you mention the chronology I recall the version I was working with when I had that problem was 2000, so it’s probably fixed in 2003 (and probably Hans was relying on my version being 2003 when he replied — he can’t be wrong!)

Reply | Quote