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

Question

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?

Bywaters

Answer 1

I found a hack, here. The secret lies in enclosing your query inside a dummy query. Like so:

SELECT * FROM (SELECT   <-----here
Table1.Field1,
Table1.Field2
FROM
Table1

WHERE
Table1.Field2 = "Yes") AS query1 <-----here

I've indicated where and how you wrap the code. The only issue I can see is if you wanted to edit the code in design view, then it doesn't seem to pick the enclosed code up at all.

Bywaters

That's the idea, the design view builder optimises the SQL statement the way it thinks is best (not often the case in my opinion), if you're skilled at manual writing of SQL then stick with that, you don't need to enclose your statement as a derived table though, as long as you only view in SQL mode and don't switch to design view, it will not amend your statement. – Began

@MattDonnan that's true but I often find myself flicking back and forth. Now if I had a proper interface to write sql in... – Bywaters

This trick works well -- but it seems that you have to be careful to only save the query in datasheet view (Access 2010). Counterintuitively, saving the query while in SQL view wipes out all the newlines. However, even with a save in SQL view, this hack still prevents the massive SQL reformatting that usually occurs. – Endothelium

Answer 2

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.

Endoenzyme

Answer 3

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! :(

Renie

Answer 4

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

Beseech

What does it do? – Bywaters

See the comments in the function. @Pureferret – Amoretto

@Amoretto I saw. I was hoping the OP would add some examples to the non-code part of the answer. – Bywaters

It's not intended to be called from a program. "Examples" would just be an SQL that Access reformatted and the same query with newlines and 5-space indents. – Beseech

Note that I've slightly modified it so it replaces ALL double-quotes with single quotes. So regarding my June 30 comment, it will now be "same query with newlines and 5-space indents AND double-quotes REPLACED with single-quotes." – Beseech

Answer 5

You could use the above code to format your SQL code using Poor Man's T-SQL formatter .NET 3.5 library (register it in Access) and then just do

Dim sQry As String
Dim oSQLMgr As New PoorMansTsqlFormatterLib35.SqlFormattingManager
sQry=Replace(oSQLMgr.Format(CurrentDB.QueryDefs("MyQueryName")),vbTab,"    ")

and then paste it into your query. Which could also be automated like this:

Dim clp As New MSForms.DataObject
clp.SetText sQry
clp.PutInClipboard
DoCmd.OpenQuery "MyQueryName", acViewDesign, acEdit
DoCmd.RunCommand acCmdSQLView
DoCmd.RunCommand acCmdPaste 

Brierroot

Answer 6

I've done SQL Server heavily at work but at home I keep 20 personal databases, so daily I've seen both worlds. I've modified a single practice, and it's the workhorse I use constantly in Access:

After initial setup of whatever quickies I can do in Design View, I copy to Notepad and refine the query from there.

In fact I version-control records in a table called "QueryArchive," where I plop a starting copy of any query I'm about to modify. I designed the table so the records are timedatestamped, have a comment (what i worked on, what I need to write later, etc.) and have a yes/no box to indicate if a version went to "prod" (that is, if I actually copy that code version to its named Access query where I execute it).

In short, I use Design View for quick config tweaks, but Notepad for the heavy development--Notepad and my table QueryArchive KEEP the formatting.

In spite of my best coding efforts, there will be that most-complicated project where Design View craps the bed--it's one reason I keep this practice : I can unendingly pull the fresh copy of my initial code in its unaltered format.

The other reason ? This practice is dead-simple: No code mod !

Gladsome