image of SQL Spacer results with spaces and line breaks Ms Access Gurus      
Home Articles Code Tools

SQL Spacer VBA and Access Add-in

Quickly make SQL Statements easier to understand by inserting spaces and line breaks where it makes sense. Untangle logic, inspect something that was constructed in VBA and doesn't work quite right, create documentation, and more.

You can run the VBA code and get the result to put somewhere yourself, or use the add-in for a convenient form.

On the Menu form that opens, simply paste an SQL statement into the textbox on the left for the Original, and then press TAB to run the AfterUpdate event and write the Result into the textbox on the right. The Result will be selected so you can quickly copy it — or activate the command button to "Copy to Clipboard" or "Save to File".

This is a free Add-in with open source VBA code for Access. Although it's designed to be an add-in, you don't have to use it that way! You can open the ACCDA file with Access and use the form to add spacing to SQL statements without installing it as an add-in. You can also call the VBA code from your application.

Interesting and insightful observation by Jack Drawbridge: if the add-in form is active, apparently, you can only switch from SQL to Datasheet or (a proper) Design view if a query has been saved. The saved version doesn't have to be the current SQL. When you make a new query, under these circumstances, it doesn't work.

Further testing reveals that a new query created from SQL does work, as long as my Add-in menu form isn't what's active. Therefore, you need to OPEN an object in your current database, anything it seems, before you choose to make a new query, so the CurrentDb is where a new query goes!

This is not the behavior I expected. All is good if you're just replacing current SQL somewhere. Perhaps this is because the add-in isn't protected? It's just an ACCDB that's been renamed to ACCDA.

Quick Jump

Menu Features Steps to install add-in Video tutorials VBA Download Backstory Share Help to develop yourself Donate

Menu

When activated, there's a menu form. Paste an SQL statement into the "Original" textbox on the left. Tab or click out of that control so its AfterUpdate event runs and shows the SQL Result with line breaks and spacing in the textbox on the right. Much easier to understand and modify.

image of Menu form for SQL Spacer to Add line breaks and spacing to SQL statements

Goto the Very Top  

Features

Add spacing and line breaks

Space in the right places can help greatly with faster understanding, and enable you to focus on what's most important.

Count the number of characters

The number of characters are displayed on the form for the Original and the Result.

Copy to Clipboard

Copy the Result to the Windows clipboard. You can also select this and copy it yourself.

Save to File

Create a text file with the SQL results with spaces and line breaks. There's a prompt to specify an optional Title to be included in the filename. By default, files will be created in a folder on the destop called "strive4peace", which you can change by editing the code. When done, you can choose to open the folder with Result files.

Goto Top  

Steps to install an add-in

  1. Run Access As Administrator
    • Right-click on the MSACCESS.EXE file or a shortcut to it
    • Choose "Run as administrator"
  2. Open any database.
  3. On the DATABASE TOOLS ribbon tab, drop down the list under the Add-ins icon.
  4. Choose the Add-in Manager.
  5. Add New...
  6. Browse to the ACCDA file and click Open.
  7. Close the Add-in Manager.

SQL Spacer is now on the Add-ins menu.

image of SQL Spacer on the Add-ins menu

Goto Top  

Videos

SQL Spacer VBA and Access Add-in

watch on YouTube: SQL Spacer VBA and Access Add-in (6:58)

How to Make and Install an Access Add-In

watch on YouTube: How to Make and Install an Access Add-In (11:51)

Goto Top  

VBA

Standard module

This code is called by the add-in menu form. It can also be run on its own. SQLSpacer_s4p returns a string with spacing and line breaks given an SQL statement. 

'*************** Code Start *****************************************************
' module name: bas_SQLSpacer_s4p
'-------------------------------------------------------------------------------
' Purpose  : return SQL Statement with added line breaks and spaces
'                 so it's easier to comprehend
'            this module also includes a function to add quotes and line continuations for VBA
' Author   : crystal (strive4peace)
' web site : https://msaccessgurus.com
' This code: https://msaccessgurus.com/tool/Addin_Addin_SQLSpacer.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk
'-------------------------------------------------------------------------------
'           LaunchMenu
'-------------------------------------------------------------------------------
Function LaunchMenu() 
'230727 strive4peace
   DoCmd.OpenForm  "f_Menu_SQLSpacer_s4p"
End Function 

'-------------------------------------------------------------------------------
'           SQLSpacer_s4p
'-------------------------------------------------------------------------------
Function SQLSpacer_s4p( _ 
   Optional ByVal pSQL As String =  "" _ 
   ) As String 
'
' SQLSpacer_s4p
' strive4peace (crystal)
' 150726 ... 150811, 230727-28
' return SQL statement with added line breaks and spacing
'  so it's easier to comprehend

   'PARAMETER
   '  pSQL = SQL statement to add line breaks and spacing to
   
   'CALLED BY:
   '  code behind form f_Menu_SQLSpacer_s4p
   '  SQLSpacer4VBA
   
   On Error GoTo Proc_Err 
 
   SQLSpacer_s4p =  ""
   If Not Len(Trim(pSQL)) > 0 Then Exit Function 
 
   Dim sSQL As String _ 
      ,sLineBreak As String _ 
      ,i As Integer 
 
   'NOTE: there could be more terms in this list
   Const iMax As Integer = 14 
   Dim aLookFor(1 To iMax) As String 
 
   sSQL = Trim(pSQL) 
 
   sLineBreak = vbCrLf 
 
   sSQL = Replace(sSQL,sLineBreak, " ") 
   
   aLookFor(1) =  " SELECT "
   aLookFor(2) =  " FROM "
   aLookFor(3) =  " IN "
   aLookFor(4) =  " INTO "
   aLookFor(5) =  " WHERE "
   aLookFor(6) =  " GROUP BY "
   aLookFor(7) =  " HAVING "
   aLookFor(8) =  " ORDER BY "
 
   aLookFor(9) =  " SET "
   aLookFor(10) =  " ON "
   aLookFor(11) =  " AND "
   aLookFor(12) =  " LEFT "
   aLookFor(13) =  " RIGHT "
   aLookFor(14) = " INNER "
 
   For i = 1 To iMax 
      If i >= 9 Then 
         sSQL = Replace(sSQL,aLookFor(i),sLineBreak & Space(3) & aLookFor(i)) 
      Else 
         sSQL = Replace(sSQL,aLookFor(i),sLineBreak &  " " & aLookFor(i)) 
      End If 
   Next i 
   'replace commas with line break, space, comma
   sSQL = Replace(sSQL, ", ",sLineBreak & Space(3) &  ", ") 
 
   SQLSpacer_s4p = sSQL 
   
   'Also print to Immediate (Debug) window -- Ctrl-G to look
   Debug.Print sSQL 

 
Proc_Exit: 
   On Error Resume Next 
   Exit Function 
 
Proc_Err: 
   MsgBox Err.Description,,_ 
        "ERROR " & Err.Number _ 
        &  "   SQLSpacer_s4p"
 
   Resume Proc_Exit 
   Resume 
 
End Function 
 
'-------------------------------------------------------------------------------
'           runSQLSpacer4VBA
'-------------------------------------------------------------------------------
Sub runSQLSpacer4VBA() 
   'Customize and then CLICK HERE and Press F5 to Run!
   'Ctrl-G to look at Immediate (Debug) window to see Results
   Dim sSQL As String 
   '------------------------ customize with your SQL statement to test
   sSQL =  "SELECT E.AtomicN, E.Symb, E.Element, E.AtomicMass, IIf([E].[Row_]<8,[E].[Row_],[E].[Row_]-2) AS Period, C.Classification FROM t_ELEMENT AS E LEFT JOIN t_Classification AS C ON E.ClassID = C.ClassID ORDER BY E.AtomicN;"
   sSQL = SQLSpacer4VBA(sSQL) 
   Debug.Print sSQL 
End Sub 
 
'-------------------------------------------------------------------------------
'           SQLSpacer4VBA
'-------------------------------------------------------------------------------
Function SQLSpacer4VBA(ByVal pSQL As String) As String 
   Dim sSQL As String _ 
   ,iPos As Integer 
 'create a string you can paste into a VBA procedure to construct an SQL statement
 ' adds quote marks and replaces CrLf with line continuation for VBA
 '    Modify to include variable as desired
   sSQL = SQLSpacer_s4p(pSQL) 
   sSQL = Trim( """" & Replace(sSQL,vbCrLf, " "" _" & vbCrLf &  " & """)) 
   iPos = InStrRev(sSQL, ";") 
   If iPos > 0 Then 
      sSQL = Trim(Left(sSQL,iPos)) &  """"
   End If 
   SQLSpacer4VBA = sSQL 
End Function 


'*************** Code End *****************************************************

Goto Top  

code behind menu form, f_Menu_SQLSpacer_s4p

image of Menu form for SQL Spacer 

Option Compare Database 
Option Explicit 

'*************** Code Start *****************************************************
' code behind form: f_Menu_SQLSpacer_s4p
'-------------------------------------------------------------------------------
' Purpose  : add line breaks and spaces to SQL statement so it's easier to comprehend
'            textbox with Original SQL and Result SQL
'            bound to a table with 2 long text fields
'              keep overwriting same record
' Author   : crystal (strive4peace)
' web site : https://msaccessgurus.com
' This code: https://msaccessgurus.com/tool/Addin_Addin_SQLSpacer.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
'TABLE:
'  s4p_Mem: one record with long text fields
'     is used over and over so strings can be longer
'-------------------------------------------------------------------------------
'           Form_Load
'-------------------------------------------------------------------------------
Private Sub Form_Load() 
'200410 strive4peace
   'clear old data
   Me.memOrig = Null 
   Me.memResult = Null 
End Sub 


'-------------------------------------------------------------------------------
'           memOrig_AfterUpdate
'-------------------------------------------------------------------------------
Private Sub memOrig_AfterUpdate() 
'230727, 28
' write results to the form
   
   'call SQLSpacer_s4p
   With Me 
      If IsNull(.memOrig.Value) Then Exit Sub 
      .memResult.Value = SQLSpacer_s4p(.memOrig.Value &  "") 
   End With 

Proc_Exit: 
      On Error Resume Next 
      Exit Sub 
Proc_Err: 
      MsgBox Err.Description _ 
          ,, "ERROR " & Err.Number _ 
           &  "   memOrig_AfterUpdate : " & Me.Name 

      Resume Proc_Exit 
      'if you BREAK MsgBox, you can set this to be next statement:
      Resume 
End Sub 

'-------------------------------------------------------------------------------
'           cmd_Copy2Clipboard_Click
'-------------------------------------------------------------------------------
Private Sub cmd_Copy2Clipboard_Click() 
'200411 strive4peace, 230727
'copy result code to the Windows clipboard
   Dim sCode As String 
   With Me.memResult 
      If Nz(.Value, "") =  "" Then Exit Sub 
      sCode = .Value 
   End With 
   'MSForms.DataObject
   With CreateObject( "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 
      .SetText sCode 
      .PutInClipboard 
   End With 
   MsgBox  "Press Ctrl-V to paste the Result where you want it",, "Done"
End Sub 

'-------------------------------------------------------------------------------
'           cmd_SaveFile_Click
'-------------------------------------------------------------------------------
Private Sub cmd_SaveFile_Click() 
'200429 strive4peace, 230728
' save SQL results to a text file. Will start with "SQL_" and end with Now as "yymmdd_hhnn_ss"
'  Prompt for optional Title to include in file name
'  Path is currently a folder called \strive4peace\ on the Desktop, which can be changed

   'CALLS
   '  SaveStringAsFile
   Dim sPathFile As String _ 
      ,sPath As String _ 
      ,sTitle As String _ 
      ,sResult As String 

   sResult =  ""
   With Me.memResult 
      If Nz(.Value, "") =  "" Then Exit Sub 
      sResult = .Value 
   End With 

   sTitle = InputBox( "Enter optional title to include in file name:" _ 
      , "Title", "") 
   
   '--- sPath
   sPath = Environ( "USERPROFILE") &  "\Desktop\strive4peace\"
   'create folder if it doesn't yet exist
   If Dir(sPath,vbDirectory) =  "" Then 
      MkDir sPath 
      DoEvents 
   End If 
   
   'add filename to path
   sPathFile = sPath &  "SQL_" _ 
      & IIf(sTitle <>  "",sTitle &  "_", "") _ 
      & Format(Now(), "yymmdd_hhnn_ss") &  ".txt"
      
   ' Call SaveStringAsFile
'   With Me.memResult
      If sResult <>  "" Then 
         Call SaveStringAsFile(sPathFile,sResult) 
      Else 
         MsgBox  "Nothing to save", "Nothing to do"
         Exit Sub 
      End If 
      
      
      
'   End With
   
   If MsgBox(sPathFile &  " was created. Open Path to file?" _ 
      ,vbYesNo, "Open Path?") = vbNo Then Exit Sub 
      
   Application.FollowHyperlink sPath 
   
End Sub 

'---------------------------------------------------------------------------------------
'                              SaveStringAsFile
'---------------------------------------------------------------------------------------
' this could be a Public procedure
Private Sub SaveStringAsFile(psPathFile As String,psFileContents As String) 
'160730 strive4peace
   Dim iFile As Integer 
   iFile = FreeFile 
   Open psPathFile For Output As iFile 
   Print #iFile,psFileContents 
   Close iFile 
End Sub 
'

'*************** Code End *******************************************************

Goto Top  

Download

Download

The download contains an ACCDA that is really an Access ACCDB with additional setup so it can be installed as an add-in. However, you can simply open it with Access and use it!

Addin_SQLSpacer_s4p_ACCDA.zip (700 kb, unzips to an Access ACCDA database file. )  

License

This add-in is a regular ACCDB file that has been renamed to have an ACCDA extension. It may be used freely, but you may not sell it in whole or in part. You may include it in applications you develop for others provided you keep attribution, mark your modifications, and share this source link.

Remember to UNBLOCK files you download to remove the Mark of the Web. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

Back Story

The code to add spacing and line breaks to SQL works best with well-named fields. More terms could be added to Find + Replace for the SQL string — and more find and fix code too — most of what you probably need is consdered. The result could possibly be an SQL statement that can't render, although I've yet to find one. If you have a comma inside a quoted value, you'll probably need to fix the Result.

The VBA code is all open, so if you modify something to make it work better for you, please let me know to share with others.

If you have lots of SQL statements to document, here's a free tool to document SQL statements to Word with spacing for queries, forms, or reports: Document SQL, RecordSource, RowSource for Queries, Forms, and Reports

... but maybe, you're focused on just one SQL statement. That's when this code is handy ~ hope you like it as much as I do.

Goto Top  

Share with others

here's the link to copy:

https://msaccessgurus.com/tool/Addin_SQLSpacer.htm

Question

What are you doing with Access?

Goto Top  

Help to develop

Are you looking for one-on-one help?

Let's connect and team-develop while we build your application together. As needed, I'll pull in code and features from my vast libraries, cutting out lots of development time, and you learn how it is done. Each step of the way, you guide from your perspective and infuse your goals and ideas. The melding of what we both can do results in a successful application.

Email me training@msAccessGurus.com ~ crystal

Goto Top