Analyzing/Documenting a Database?

[misscrf]

I'm looking for a tool (preferably free or free trial) so I can document all objects and such in an Access database. I am looking at replacing a linked SQL view, and will need to know everywhere I use it. Whether it's a record source for a form, row source for a combo or listbox, or if it's in VBA somewhere, I don't want to miss anything.

It would be nice to have complete documentation of the application, anyway, so I can be sure I know what functions are used where, and what queries aren't used anywhere (meaning I could get rid of them).

Any tips would be greatly appreciated!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

There is a built-in database documenter in Access (Database Tools menu). You have lots of options that you can set in terms of what you want to capture in the documenting. All of the information is stored during the documenting process. The table name is doc_tblObjects and it's located in a wizard file (based on your Access version) C:\Users\[Your Login Here]\AppData\Roaming\Microsoft\Access\ACWZUSR12.ACCDU.

You can create a query based on three copies of the linked table like the following which shows all of the forms and reports with their controls, control types, control sources, and visible:

TRANSFORM First(doc_tblObjects_2.Extra1) AS FirstOfExtra1
SELECT doc_tblObjects.TypeID AS [Object Type], doc_tblObjects.Name AS FormReport, 
  doc_tblObjects_1.Extra1 AS [Control Type], doc_tblObjects_1.Name AS ControlName 
FROM doc_tblObjects AS doc_tblObjects_2 
 INNER JOIN (doc_tblObjects 
 INNER JOIN doc_tblObjects AS doc_tblObjects_1 ON doc_tblObjects.ID = doc_tblObjects_1.ParentID)
   ON doc_tblObjects_2.ParentID = doc_tblObjects_1.ID
WHERE (((doc_tblObjects.TypeID) In (2,3)) AND ((doc_tblObjects_1.TypeID)=33) AND 
 ((doc_tblObjects_2.Name) In ("ControlSource:","Visible:")) AND ((doc_tblObjects_2.Extra1) Is Not Null))
GROUP BY doc_tblObjects.TypeID, doc_tblObjects.Name, doc_tblObjects_1.Extra1, doc_tblObjects_1.Name
PIVOT doc_tblObjects_2.Name;

[pre]
Object Type FormReport Control Type ControlName ControlSource: Visible:
2 Attachments_SubFrm Command Button cmdChangeFile True
2 Attachments_SubFrm Command Button cmdpreview True
2 Attachments_SubFrm Command Button Command11 True
2 Attachments_SubFrm Label ATDateAdd_Label True
2 Attachments_SubFrm Label ATFileName_Label True
2 Attachments_SubFrm Label ATID_Label False
2 Attachments_SubFrm Label ComanID_Label False
2 Attachments_SubFrm Label Label14 True
2 Attachments_SubFrm Text Box ATArchive ATArchive False
2 Attachments_SubFrm Text Box ATDateAdd ATDateAdd True[/pre]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

Thanks, Duane! I knew about that documenter, but always hated that it just produced a formatted report. I'm wanting to be able to look up a linked table/view and find anywhere it's being used. I want to be able to convert queries to SQL views, and link them back in. But in order to do that, I need to make sure I know everywhere it's being used. I want to get rid of that function, that I don't think is being used anymore, but I don't have a way to get an index of everywhere it's called (including 0 locations, so I know it can be deleted). I can go through and document a lot of the tables, queries, forms, etc, but keeping that documentation up to date is insane.

I'll take a look at this and see how it goes. It's not everything, but it's definitely more than I had. Years ago I had found an Access addin, that let you search for the name of an object or field and you could rename it system wide. I'm wishing I could remember what it was. I want to make sure my code is all formatted well, and anything that can be a public function, is one. I want to be able to consolidate any redundant actions, so there will be less code, and it's easier to follow.

I feel like this is something a lot of Access developers have to struggle with. I'm surprised there aren't more widely used tools, for this purpose.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

When I try to run that query, I get this error:

I run the documenter, close the useless formatted report, and try to run that. It can't seem to find the table.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I found this, which is pretty helpful.

http://www.accessdependencychecker.com/

I'm thinking of writing some code that will insert info into a table.

I want to focus on getting all controls on every form, so I can be sure what events are in use, and what rowsources are calling what tables/views/queries.

The format I'm thinking is something like:

Form | Control | Property | Property Value

Then I'll look to get every property, and it's value, but only if there is a value set (like I won't want the on enter property of a control, if there is no event being used there).

I have this function, to get me all combos, on every form, and their row source.

Public Function getCombos()

On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim ctrl As Control
Dim frm As Form

Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
    For Each ctrl In Forms(obj.Name).Controls
        If ctrl.ControlType = acComboBox Then
            Debug.Print obj.Name & "^" & ctrl.Name & "^" & ctrl.RowSource
        End If
    Next ctrl
DoCmd.Close acForm, obj.Name, acSaveNo
Next obj

End Function

I just need to expand it to all controls and all properties, and instead of debugging, inserting results into a "documentation" table.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

Some of your needs could be easily done using MZTools


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

If you plan to read code as well then you will likely need to look at vba extensibility. See article.

http://www.cpearson.com/excel/vbe.aspx

However, there seems to be a lot of free stuff already out there if you do a little Googling.

 

[dhookom]

Tony Toews has a page of third party tools that has some scan and replace. I think most can be used as just "scan".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

Thanks for the suggestions. I feel like eventually I will use some 3rd party, and some custom built, to create a complete documentation toolset. I wrote this simple function (below). I added it to a separate module, that I call Mods. It's where I keep the majority of my public functions. I'm thinking of separating some of them out into a ModsDoc module, just for documenting the status/properties of different objects in the app (I have a few of them).

Public Function docForms()
    On Error Resume Next
    Dim obj As AccessObject, dbs As Object
    Dim ctrl As Control
    Dim frm As Form
    Dim fName As String
    Dim cName As String
    Dim pName As String
    Dim pValue As String
    Dim prp As Property
    Dim ctrlPropSQL As String
    
    Set dbs = Application.CurrentProject
    
    'FOR EACH FORM
    For Each obj In dbs.AllForms
        'OPEN THE FORM IN DESIGN VIEW
        'NEEDS TO BE OPEN, TO GET AT EACH CONTROL - DESIGN VIEW SO WE DON'T INITIALIZE ANY CODE
        DoCmd.OpenForm obj.Name, acDesign
            'FOR EACH CONTROL ON THE FORM
            For Each ctrl In Forms(obj.Name).Controls
                'IF THE CONTROL TYPE IS NOT A LABEL
                If ctrl.ControlType <> acLabel Then
                    'LOOP THROUGH EACH PROPERTY ON THE CONTROL
                    For Each prp In ctrl.Properties
                        pValue = Nz(prp.Value, "")
                        'IF PROPERTY VALUE ISNT BLANK OR NULL
                        If pValue <> "" Then
                            fName = obj.Name
                            cName = ctrl.Name
                            pName = prp.Name
                            'MUST HAVE A TABLE WITH THOSE COLUMN NAMES - THIS IS IN SQL AND IS A LINKED TABLE, IN MY SET UP
                            'I ALSO HAVE AN ID COLUMN, WHICH IS AN IDENTITY, AND THE PROPERTYVALUE IS VARCHAR(MAX)
                                
                                'PREPARE SQL INSERT STATEMENT WITH EACH COLUMN VALUE
                                ctrlPropSQL = "INSERT INTO tblzzFormDocumentation ([FormName],[ControlName],[PropertyName],[PropertyValue]) VALUES ('" & fName & "','" & _
                                                cName & "','" & pName & "','" & pValue & "');"
                                'EXECUTE SQL
                                CurrentDb.Execute ctrlPropSQL, dbSeeChanges
                        End If
                    Next prp
                End If
            Next ctrl
        'CLOSE THE FORM, ONCE DONE LOOPING THROUGH ALL CONTROLS
        DoCmd.Close acForm, obj.Name, acSaveNo
    Next obj

End Function

Depending on how many forms, and how many controls, this can take some time. You'll see the forms open in design, one at a time, so once the application has no forms open and goes back to the code window, you'll know it's done. The nice thing is I can query the property values, to find all controls that use a specific view in its row source. That way, when I go to change the view name, or replace it with another table or view, I will make sure I get all the places. I know I still have to search the code, but that is simple enough.

Hope this helps someone else!

misscrf

It is never too late to become what you could have been ~ George Eliot