SQL Spacer VBA and Access Add-in
Channel: LearnAccessByCrystal
Date: 18/08/2023

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. 

*Links*

SQL Spacer VBA and Access Add-in
https://msaccessgurus.com/tool/Addin_SQLSpacer.htm

MyContacts, free Access database with source code
https://msaccessgurus.com/tool/MyContacts.htm

SQL to list object names in Access database
https://msaccessgurus.com/VBA/Query_SQL_ListObjects.htm

00:03 introduction to SQL Spacer
00:49 This database keeps track of contacts.
00:54 combobox to find records
01:07 design view of the form, select control
01:12 Data tab of the Property Sheet, zoom the RowSource
01:33 Ctrl-A to Select all, and Copy.
01:38 run SQL Spacer add-in
02:52 What if you want to make a new query with the SQL statement?
03:30 Error running the SQL -- what? why?
03:47 Jack Drawbridge told me about this problem.
04:14 SQL that shows the names of objects
04:58 On the forums, Jack is jdraw or orange999,
05:08 So, how DO you make a new query in the CurrentDb?
06:01 This demo database is MyContacts
06:39 Please comment, like, and subscribe
06:53 Thanks for joining me.

Contact me for training ... Access, Excel, Word, PowerPoint ... and more.

kind regards,
crystal