×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Analyzing/Documenting a Database?

Analyzing/Documenting a Database?

Analyzing/Documenting a Database?

(OP)
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

RE: Analyzing/Documenting a Database?

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:

CODE --> sql

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; 

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 

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

RE: Analyzing/Documenting a Database?

(OP)
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

RE: Analyzing/Documenting a Database?

(OP)
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.

CODE

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

RE: Analyzing/Documenting a Database?

Some of your needs could be easily done using MZTools


---- Andy

There is a great need for a sarcasm font.

RE: Analyzing/Documenting a Database?

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.

RE: Analyzing/Documenting a Database?

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

RE: Analyzing/Documenting a Database?

(OP)
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).

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close