Defeat Microsoft Access’ SQL Reformatter

Many of us would just rather not use Microsoft Access for anything, but there are those times when you are forced to do it. In times like these, you have a problem. In times like these, you need a solution. The problem is that if you write SQL that is at all interesting, it quickly becomes unreadable. If you try to beautify your SQL with some nice line breaks and indentation, Microsoft Access will “fix” that for you the next time you open your query. We struggled with this for some time till we realized that Access will not touch SQL inside of an inline view. So your solution is to put your SQL inside of an inline view. Instead of seeing this after you save and open your query:

SELECT field1, field2, field3, field4, field5
FROM table1 a inner join table2 b on field1 = field2 and field3 = field4
WHERE field1 = 'junk' and field3 = 'that' and field5 is not null

You could see this:

select * from (
SELECT field1, field2,
       field3, field4, field5
  FROM table1 a inner join table2 b on field1 = field2
                                   and field3 = field4
 WHERE field1 = 'junk'
   and field3 = 'that'
   and field5 is not null
) as a

The only problem is that Access gets confused about the parenthesis around the view and replaces them with brackets. For us, replacing the brackets with parenthesis is a better situation than manually beautifying the code all over again every time we open it.