How do I force MS Access to retain its SQL formatting?

user_1075247 0
Viewed 13276

I have a lot of SQL at work in MS-Access, and I need to formatted so that it's human readable. The issue is when I change between views I end up with the SQL being condensed down into something that I can't read.

How do I force SQL to retain its 'shape' when I go to other views?

ms-access code-formatting sql
4 Answers

If you have admin access to your PC (required for installation), Access SQL editor could be the right tool.
I never used it: as a consultant I rarely have the right to install stuff on the machines I use, but it looks exactly like what I would like to have: a replica of the SSMS editor.

user_785220

Sometimes the method proposed by @Pureferret can fail with Access keeping reformatting the SQL.

The only way I've found that works all the time is to append a UNION ALL block at the end of the query.
Indeed as Access does not handle UNION ALLs it won't even propose to edit in design mode.
(I've tried with UNION but got an Overflow error!)

SELECT
    ...
    ...
FROM
    ...
WHERE
    ...
UNION ALL SELECT NULL, NULL, NULL FROM ANY_TABLE WHERE NULL

(if targetting a real table bothers you, you can use a "Dual" table to the database)

Moreover I find it a little "cleaner" to only add one line at the end.
But you need to write as many NULLs as there are selected fields in the real query.

EDIT: /!\ Be careful with this method in sub-queries as it can cause some Query too complex error in queries using it! :(

user_1457570

This won't keep the formatting, but it can reformat.

Sub FormatSQL()
' self-contained FormatSQL()

' 1) Takes WHATEVER is in the clipboard:
' 2) replaces all comma + space with comma, vbCrLf and 5 spaces,
' 3) replaces ALL double-quotes chr(34) with single-quotes
' 4) puts result in clipboard

' CAUTION - This CAN screw up DOUBLED double-quotes (see below)

' Use to format SQL in MS Access SQL View
'
' Usage:
' 1) Copy source to clipboard (Ctrl-a, Ctrl-c)
' 2) Run this
' 3) Paste to target (Ctrl-v)
'
' Note: Does NOT have a "Done" popup.
'
' To add it Access ribbon, create a function that calls this and
' create a macro that calls the function using RunCode.
'
' Ex:
' Function FmtSQL()
'   Call FormatSQL()
' End Function

'NOTES: Must enable Forms Library: _
'   Tools > References > Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"

Dim indent As String
    indent = "," & vbCrLf & "     "

Dim DataObj As New MSForms.DataObject
DataObj.Clear   ' may prevent crashes

Dim clip As Variant
DataObj.GetFromClipboard
clip = DataObj.GetText

clip = Replace(clip, ", ", indent)

' Replace ALL double-quote with single-quote
' NOTE: This WILL screw up DOUBLED double-quotes, i.e.
' "Write ""My name is Paul."" "

clip = Replace(clip, Chr(34), "'")

DataObj.SetText clip
DataObj.PutInClipboard
Set DataObj = Nothing

' Sub FormatSQL()
End Sub
user_90690310
Related