Clients usually have this pesky need to have everything documented. As a generally lazy person, this offends my lackadaisical attitude. Nevertheless, I typically oblige, given the fact that I want them to take over the work at some point.
Over the years, I've rolled my own documentation tools to help in this endeavor. Today, I want to share a small procedure I use to document Access queries in Excel.
Copy this code into a standard module in Access.
-
Function Document_My_Queries()
-
Dim TempTable As DAO.Recordset
-
Dim QryDef As QueryDef
-
Dim QryName As String
-
Dim strSQL As String
-
-
'Step 1: Start with a fresh QueryList table
-
On Error Resume Next
-
docmd.DeleteObject acTable, "QueryList"
-
CurrentDb.Execute _
-
"CREATE TABLE QueryList (QueryName text(100), DateCreated Date, LastUpdated Date, SQL memo)", dbFailOnError
-
On Error GoTo 0
-
-
'Step 2: Open the QueryList table
-
Set TempTable = CurrentDb.OpenRecordset("QueryList")
-
-
'Step 3: Loop through all queries and fill the QueryList table with Query Def info
-
For Each QryDef In CurrentDb.QueryDefs
-
'Exclude deleted querys
-
If Left(QryDef.Name, 1) = "~" Then
-
GoTo SKIPIT
-
End If
-
TempTable.AddNew
-
TempTable!QueryName = QryDef.Name
-
TempTable!DateCreated = QryDef.DateCreated
-
TempTable!LastUpdated = QryDef.LastUpdated
-
TempTable!SQL = QryDef.SQL
-
TempTable.Update
-
SKIPIT:
-
Next
-
-
'Step 4: Use TransferSpreadsheet to export to Excel
-
docmd.TransferSpreadsheet acExport, 8, "QueryList", _
-
CurrentProject.Path & "\MyQueries.xls", True, ""
-
-
End Function
In Step 1, you create a fresh QueryList table. This table will hold the info about your queries. If a QueryList table already exists, it gets blown away and replaced by a fresh one.
In Step 2, you open the QueryList table into a recordset.
In Step 3, you loop through all the queries in the database (skipping the ghost queries that start with ~) and you log the needed info into the QueryList table.
In Step 4, you use the TransferSpreadsheet method to send the QueryList table to Excel. TransferSpreadsheet will allow the ultra-long SQL statements to survive Excel's 255 character limit for a cell. Meaning, the SQL statements won't be cut off at 255 characters.
The workbook will be saved into a file called MyQueries.xls in the same directory as the host database.
Here's what the final spreadsheet looks like.
Why send this info to Excel? I find that an Excel workbook makes for a nice documentation file, allowing me to partition different aspects of my documentation into tabs. Queries on one tab, Tables on one tab, Forms on one tab, etc.
Outside of documentation, you can also use this to essentially create a backup of the queries in your database.
This is a good suggestion Mike! Along similar lines for a non-technical person and casual user of Access is to document steps in a table in Access.
I recently created a bunch of queries and tables. I used some standard naming conventions to make the queries appear in order, but not all of the info can be logged in a query or table name.
To log my steps and criteria, I created a table called "Notes" and opened the field up to more than 50 characters. Then I simply typed into a table the steps and criteria I used to build my final table for the analysis. This allows anyone to go into the database and view the steps and criteria used without having to look at each query and table.
Along the same lines as your post, so I just wanted to share.
Ok great idea in fact it could not have come a better time. Just finished a small Access app and need to give a break down of everything I have within the database. Like the kids say today - du - could you give one more step to where to place this code (2007 Verison) Objects I no longer see modules listed or am I missing something?
Fred: In Access 2007, go to the Create tab and click the Macro dropdown. There, you can choose Modules. This will create a new module for you. Place the code there.
YES!! Mike Your A Genius It Work Perfectly That is what it all about learning from the Pro simple little things. PS in todays comics Dagwood trys Bacon Ice Cream Great Yes Yes Yes. Thank-you
You are my hero! Thank you. So much better than printing all that one-object-per-page stuff in Access' Documenter.
Hi,
In your listing of the VBA code, line 34: replace & with &
Works great. Thanks.
Hi,
Line 34 should be just the & instead of the HTML equivalent when you paste it to Microsoft Access.