Format Query in SQL View

[Guest]

How can I format the SQL I write in SQL View. That is, how can I preserve
the indenting and carriage returns I use to make long code easy to read.
Sometimes it is preserved -- the SQL for union queries for example always
retains the formatting I use to type them. But all others, it seems, revert
to a single continuous wrapping line for each clause of the query. This
makes a FROM clause with many outer joins impossible to read.

Thanks.

 

[MGFoster]

How can I format the SQL I write in SQL View. That is, how can I preserve
the indenting and carriage returns I use to make long code easy to read.
Sometimes it is preserved -- the SQL for union queries for example always
retains the formatting I use to type them. But all others, it seems, revert
to a single continuous wrapping line for each clause of the query. This
makes a FROM clause with many outer joins impossible to read.

In Access, you're stuck w/ that continuous string. That's the way
Access stores the queries. A PITA, but that's all we have.

 

[John Spencer]

If you do it in Access, the answer is "you can't".

Some people keep a text file (word or other editor) and paste the nicely
formatted SQL into the file so they can copy and paste it back into Access
when they need to edit the SQL.

 

[Guest]

Thanks.

If you do it in Access, the answer is "you can't".

Some people keep a text file (word or other editor) and paste the nicely
formatted SQL into the file so they can copy and paste it back into Access
when they need to edit the SQL.

 

[Jamie Collins]

In Access, you're stuck w/ that continuous string. That's the way
Access stores the queries. A PITA, but that's all we have.

This is another of those occasions where it pays to know the difference
between Access and Jet <g>.

The issues can get easily muddled. An 'Access Query object' is
synonymous with a Jet VIEW. Access stores all its objects, include
non-Jet elements such as reports and forms, in Jet tables. Access
'sees' a Jet VIEW as an Access Query. Does a query object on a Jet
database get stored by Access as a/in a Jet VIEW? I'm lost, so let's
move on to something that I *do* know the answer to...

When you create a Jet VIEW the white space is persisted in the
definition in the schema catalog. This is easily demonstrated (the
query is intended to be viewed in a fixed width font, so paste this
into the Visual Basic Editor):

Sub ViewWhiteSpace()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test4 (" & _
" data_col INTEGER NOT NULL);"

' Create exmaple VIEW
.Execute _
"CREATE VIEW TestView AS " & vbCrLf & _
"SELECT data_col " & vbCrLf & _
" FROM Test4 " & vbCrLf & _
" WHERE 2 >= ( " & vbCrLf & _
" SELECT COUNT(*) " & vbCrLf & _
" FROM Test4 AS T1 " & vbCrLf & _
" WHERE Test4.data_col " & vbCrLf & _
" = Test4.data_col " & vbCrLf & _
"); "

' Retrieve VIEW definition from schema
Dim rs
Set rs = _
.OpenSchema(23)
Debug.Print rs!VIEW_DEFINITION
MsgBox rs!VIEW_DEFINITION
rs.Close
End With
Set .ActiveConnection = Nothing
End With

End Sub

Whether subsequently opening the Jet VIEW in Access causes Access to
'clean' the whitespace and save this back to the schema catalog is
something you will have to test.

Jamie.

--