Tool > Add-in > Document SQL, RecordSource, RowSource for Queries, Forms, and Reports

Quick Jump

Logic

SQL (and values) for Queries, Forms, or Reports

• Loop though whatever is being analyzed and store information in tables. Forms and Reports store both RecordSource and all the RowSources.
• Test SQL statements. Count records. Mark BAD! ones.
• If the RowSource is a value list, its entries are counted ... now that I think about it, I forgot to divide by the ColumnCount ...
• SQL statements are formatted with spaces and line breaks to make them easier to read.

Write to Word

• Set an object variable to a new Word application using CreateObject("Word.Application")
• Make a new document and write information. Use a table with 2 columns for the results.
• This is not my best Word automation code, so I'd rather wait to explain more about the logic.

Data Tables

To avoid bloat in the current database, an external database is created to hold the tables that store information.

• DBEngine.CreateDatabase to create an external database
• DAO.TableDef.CreateTableDef to make new tables in that external database, then CreateField, set Attributes if it is an autonumber, and DefaultValue if desired for other fields.
• When needed, Append Fields and TableDefs. Make Primary Key index in each table.
• Descriptions: It used to be that you could create descriptions when a field is defined. Now it seems that field descriptions can only be created once a table definition is saved. Therefore, the code to set field descriptions is later.
• Once the tables are made in the external database, DAO.TableDef.CreateTableDef is used to link to them each to the current database so they can be used to store and retrieve data.

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.

Document SQL is now on the Add-ins menu. When you run it, reports are written to Microsoft Word. The screen shows what is happening. When done, the folder with the Word report(s) is opened.

Download

Download

Addin_DocumentSQL.zip (152 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 pass on the download link and share the source code and designs with your modifications.

Example

These are partial results of a Form SQL report in Microsoft Word. SQL is formatted to be easier to read. The number of records is counted if the statement runs successfully. Bad SQL is marked.

Videos

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

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

Experts-Exchange (EE): How to Make and Install an Access Add-In (11:40)

Access DevCon 2020, hosted by Karl Donaubauer and Peter Doering

YouTube: CalendarMaker and Document SQL at Access DevCon 2020 (15:03)

Back Story

I recently changed a bunch of field names in the Analyzer, and am using this tool to help me fix the bad SQL statements, RecordSources, and RowSources.

Share with others

here's the link to copy:

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

Share your comments

Let's communicate, collaborate, and appreciate ... we all get better by sharing. Email me anytime at info@msAccessGurus.com. I enjoy hearing from Access users and developers.

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.

Email me at info@msAccessGurus.com
~ crystal