6

Like a lot of developers who are comfortable with SQL syntax I get frustrated when working with Access's query editor. I'm talking about the raw SQL Syntax view, obviously.

One of its many annoying properties is that upon saving it will discard my layout / formatting. When reopening the query all I see is a bunch of unformatted SQL.

However, if my syntax is long and/or complex enough I've noticed that Access will retain my formatting and layout and, oh joy, the query remains clear and readable. I'm looking at an example right now with a page of SQL containing couple of UNIONs all nicely laid out from a few days ago.

At what point does Access flip over to allowing the user to retain his own formatting? Is it length? Complexity? And is there maybe even a trivial structural edit (if trivial structural isn't an oxymoron) I can make to all my queries which will force Access to leave my layout in place?

hawbsl
  • 15,313
  • 25
  • 73
  • 114
  • Access is neat tool and all that but from a SQL coder's point of view, is there *anything* good about the Access UI? Back when I had to use Access, I resorted to employing a SQL parser component in building my own 'management studio' type app! – onedaywhen Nov 08 '11 at 11:56
  • @onedaywhen yes there are workarounds to the loss of layout (external component like yours), but since access _does_ sometimes retain your layout (see my question) i'd love to know at what point access decides to do so – hawbsl Nov 08 '11 at 12:01
  • I think the `UNION` makes the difference. – dwo Nov 08 '11 at 12:01
  • I offered comments in recognition of them being no answer to your question. "Have you considered SQL Server and its Management Studio, which is much more appealing to SQL coders...?" is similarly not an answer ;) – onedaywhen Nov 08 '11 at 12:40

4 Answers4

3

There are certain things that Access' query editor is not able to display in design mode.
Queries with UNION are the only thing that come to my mind right now, but there are probably more.

In my experience, Access always changes the layout as long as it's able to display the query in design mode.
As soon as you put something in the query that Access can not display in design mode (like UNION), Access leaves your layout and formatting as it is.

Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • I somehow thought it changed the query only when you switch to design view. And you're right, it cannot show union queries in design view. – HK1 Nov 08 '11 at 16:26
  • @HK1: Maybe it depends on the Access version. I just tried it in Access 2003, and it didn't change the layout when I switched to design view...only when I saved the query. – Christian Specht Nov 08 '11 at 16:36
  • 2
    It's the many unnecessary layers of ((())) in the WHERE clause that drive me completely crazy. – HK1 Nov 08 '11 at 18:50
2

I couldn't figure out why Access kept changing my format in a union query (but not for every query or table included).

I simply created another SELECT query based upon the Union query and corrected everything in design view. It's a lot easier.

When I created the SELET query based upon the UNION query, I included tables or queries that I used as lookup tables and had formatted a field to select the second column from a record in a lookup field that the ubion query had anoyingly converted back to the first field in the selected record (usually the ID No of the record).

For example, I might lookup the account name in a record in the cash disbursements table that should display "Office Supplies Exp" but the Union Query converts at least one of the queries or tables I have combined in the union query to the Account Number, the first record in the lookup table, which was originally hidden in the lookup field.

Dan'l
  • 21
  • 2
1

Just to add to Christian's answer, I've done some more testing and find that UNION and DDL queries are left alone by Access.

If we add Pass through queries to that list, then that would match the queries deemed SQL Specific on the menu:

enter image description here

So, those would seem to be the three special cases.

hawbsl
  • 15,313
  • 25
  • 73
  • 114
-1

Before saving just type the word union before the ;.

After opening Access next time, remove the word union and start working. When you want to save, first type union again.

Benjamin W.
  • 46,058
  • 19
  • 106
  • 116
drftr
  • 1
  • 2
    Yeah well, except when you want to actually **use** the query, then you get a syntax error. – Andre Apr 26 '16 at 06:21