Database documentation

ryetee

Registered User.
Local time
Today, 19:14
Joined
Jul 30, 2013
Messages
952
Not sure what the title of this should have been but I guess it does come under documentation.

I have taken over a system that has been developed in a weird and wonderful, where maybe not wonderful, way.

There are some tables that I have no idea how they get updated.

I have a table, for example, that purports to show end of day figures. It has data in with recent dates. How they get there I have no idea.

What's the best, ie easiest, way to approach this.
 
hi ryetee

First step, use the Database Tools>Analyse tools and see what that tells you about the table - Analyze table for example, will show you what forms, queries & reports are connected to it.

If the table is not in the database (eg linked to another source - the table name has a little blue arrow next to it in the navigation pane) use the Linked Table Manager in External Data to see the path to the originating source.
 
hi ryetee

First step, use the Database Tools>Analyse tools and see what that tells you about the table - Analyze table for example, will show you what forms, queries & reports are connected to it.

If the table is not in the database (eg linked to another source - the table name has a little blue arrow next to it in the navigation pane) use the Linked Table Manager in External Data to see the path to the originating source.

System has split database -> front end and back end database so all tabes are linked

Analyze table seems to want to split my table so as not to duplicate data.
I'm looking at database documenter but not holding my breath
 
You have a couple of choices for this update.

Using the database dependencies, you should be able to identify all static queries and forms that handle the table in question.

In any code window as a starting point, you should be able to search for ANY OF text references to the actual table name OR the names (one at a time, sadly) of any query that JOINs, UNIONs, or otherwise relates to the actual table name.

Between the dependencies search for static references and the Find Text search for the code window, that should cover most of the bases. What this WON'T cover is if you have any macros that perform some type of import to the table. Nor would it cover any case in which a second special-case database ALSO references the back-end.
 
This really is difficult.

Re-engineering something that may not have been written well in the first place can be enormously complex and time consuming.

It may be easier to "freeze" the development, rewrite bits that need rewriting and so on. Over time you will become more familiar with it, and will probably be able to work out how it works.

The biggest problem is if you determine the data structure itself needs changing, as you might not be able to do this while retaining the existing code.
 
with regard to searching a database to find out how a field gets updated ...

- this code will search all queries for a particular field name, and list all queries. It will optionally output the report to a text file, and is easily amended to serve other uses.

eg. You could search for multiple field names. You could check the query type to filter update or append queries.


Code:
 Sub search_queries()
 
Const searchfor = "some text"
 Dim db As Database
Dim QDF As QueryDef
Dim s As String
Dim f As Long
Dim fname As String
  
 s = ""
 Set db = CurrentDb
For Each QDF In db.QueryDefs
     If InStr(QDF.SQL, searchfor) > 0 Then
            s = s & QDF.Name & vbCrLf
    End If
Next
 If s = "" Then
    MsgBox ("Search String not found. ")
Else
    If MsgBox(s & vbCrLf & vbCrLf & "Output to File?", vbQuestion + vbYesNo) = vbNo Then
        Exit Sub
    Else
        f = freefile
        fname = CurrentProject.path & "\" & "log-" & Format(Date, "yyyy-mm-dd") & ".txt"
        Open fname For Output As #f
        Print #f, s
        Close #f
        FollowHyperlink fname
    End If
End If
 End Sub

check that the field you are searching for does not have a "caption" property. Captions are horrible!

You might want to search your entire code for the field name also., in case it doesn't use stored queries.
 
You have a couple of choices for this update.

Using the database dependencies, you should be able to identify all static queries and forms that handle the table in question.

In any code window as a starting point, you should be able to search for ANY OF text references to the actual table name OR the names (one at a time, sadly) of any query that JOINs, UNIONs, or otherwise relates to the actual table name.

Between the dependencies search for static references and the Find Text search for the code window, that should cover most of the bases. What this WON'T cover is if you have any macros that perform some type of import to the table. Nor would it cover any case in which a second special-case database ALSO references the back-end.

Not sure what you mean by database dependencies. Is that the btton on database tools, in which case I don't really know what it's showing me. Is it by form?
 
This really is difficult.

Re-engineering something that may not have been written well in the first place can be enormously complex and time consuming.

It may be easier to "freeze" the development, rewrite bits that need rewriting and so on. Over time you will become more familiar with it, and will probably be able to work out how it works.

The biggest problem is if you determine the data structure itself needs changing, as you might not be able to do this while retaining the existing code.

I know. This systems seems to have everything. It's taking me ages just to work out the things that are obvious.I'm sure there are redundant tables/queries/forms etc but it's knowing how to find out!
 
with regard to searching a database to find out how a field gets updated ...

- this code will search all queries for a particular field name, and list all queries. It will optionally output the report to a text file, and is easily amended to serve other uses.

eg. You could search for multiple field names. You could check the query type to filter update or append queries.


Code:
 Sub search_queries()
 
Const searchfor = "some text"
 Dim db As Database
Dim QDF As QueryDef
Dim s As String
Dim f As Long
Dim fname As String
  
 s = ""
 Set db = CurrentDb
For Each QDF In db.QueryDefs
     If InStr(QDF.SQL, searchfor) > 0 Then
            s = s & QDF.Name & vbCrLf
    End If
Next
 If s = "" Then
    MsgBox ("Search String not found. ")
Else
    If MsgBox(s & vbCrLf & vbCrLf & "Output to File?", vbQuestion + vbYesNo) = vbNo Then
        Exit Sub
    Else
        f = freefile
        fname = CurrentProject.path & "\" & "log-" & Format(Date, "yyyy-mm-dd") & ".txt"
        Open fname For Output As #f
        Print #f, s
        Close #f
        FollowHyperlink fname
    End If
End If
 End Sub

check that the field you are searching for does not have a "caption" property. Captions are horrible!

You might want to search your entire code for the field name also., in case it doesn't use stored queries.


thanks for this. i'll let you know how i got on.
 
A few more thoughts for consideration:

Where does this database fit with respect to your business? Is business dependent on this database, or is it a nice to have but not critical....?
What is your own skill level? (with over 500 posts, I don't think you would be considered a novice)
To sort out the logic of underlying functions/subs, you can include Debug.print statements to identify "entering function X" or similar. You can include a boolean and set it to Show or not Show DEBUG.Print statements. Analyze the outputs and update function comments accordingly, and/or build "flow diagrams" based on your findings.

You can set up routines and tables, form and queries to identify the SQL of each and every query. Then using a query or form identify which queries use which tables and/or fields.

If one of your tasks is to create user documentation, or operations documentation then getting the broad overview of the database, its purpose, the tables/subject matter involved, relationships and a current data model/ERD can be useful. It can serve as a communications tool for users, planners, technicians and developers. You can start with the higher level model and add/modify details as the intricacies are discovered.

I have found that from time to time - you remember doing something but forget the context or name - you write a few routines to identify things that can serve as "utilities".

For example, here is one that identifies queries, their source tables and fields. It doesn't catch everything, but is much better than "hunt and seek" manual efforts. It outputs to the immediate window, but could easily be modified to create a table; or altered to look for specific tables.....

Code:
'---------------------------------------------------------------------------------------
' Procedure : shamilQdfs
' Author    : mellon..from....shamil
' Date      : 10/02/2016
' Purpose   : List Queries and the fields within them
'---------------------------------------------------------------------------------------
'
Sub shamilQdfs()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim qdfIndex As Integer
10  On Error GoTo shamilQdfs_Error

20  Set dbs = Access.Application.CurrentDb
30  qdfIndex = 1
40  For Each qdf In dbs.QueryDefs
50      Debug.Print qdfIndex & ". Query: " & qdf.name
        Dim fld As DAO.Field

60      For Each fld In qdf.Fields

70          Debug.Print " " & (fld.OrdinalPosition + 1) & _
                        ". " & fld.SourceTable & "." & fld.SourceField

80      Next fld

90      Debug.Print ""
100     qdfIndex = qdfIndex + 1
110 Next qdf

120 On Error GoTo 0
130 Exit Sub

shamilQdfs_Error:

140 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure shamilQdfs of Module AccessD_KillText"
End Sub


Sometimes it's a good learning exercise to do things like
-find and list the Record source of each Form and Report (can be a handy utility to support documentation)
-create a Form with a button and a combo where the rowsource of the combo is a list of all Forms, or all reports); select a form(or report) name, click the button to display the Form(or report).

Also, if the database is not critical, then, as others have suggested, rewrite a particular function/set of functions from scratch. You'll certainly understand the process once completed. Now, add lots of comments to the code --because they're clear in your head. It may save you or others who come after you, to decipher the logic of the process.

Good luck with your documentation project.
 
Not sure what you mean by database dependencies.

On the ribbon at least as recently as AC2007 (and don't remember for sure on AC2003), there is a thing in database tools that you click on your object, then click on the database dependencies icon, and it gives you one of two lists. One of them is a list of things your object references in the database. The other is a list of things that reference your object.

The "Dependencies" icon doesn't catch code references like you would make with, say, a DoCmd.OpenForm ... "formname" ... call, which is why I said you would have to search your entire project's general and class modules for text matching the name of the object in question.
 
A few more thoughts for consideration:

Where does this database fit with respect to your business? Is business dependent on this database, or is it a nice to have but not critical....?
What is your own skill level? (with over 500 posts, I don't think you would be considered a novice)
To sort out the logic of underlying functions/subs, you can include Debug.print statements to identify "entering function X" or similar. You can include a boolean and set it to Show or not Show DEBUG.Print statements. Analyze the outputs and update function comments accordingly, and/or build "flow diagrams" based on your findings.

You can set up routines and tables, form and queries to identify the SQL of each and every query. Then using a query or form identify which queries use which tables and/or fields.

If one of your tasks is to create user documentation, or operations documentation then getting the broad overview of the database, its purpose, the tables/subject matter involved, relationships and a current data model/ERD can be useful. It can serve as a communications tool for users, planners, technicians and developers. You can start with the higher level model and add/modify details as the intricacies are discovered.

I have found that from time to time - you remember doing something but forget the context or name - you write a few routines to identify things that can serve as "utilities".

For example, here is one that identifies queries, their source tables and fields. It doesn't catch everything, but is much better than "hunt and seek" manual efforts. It outputs to the immediate window, but could easily be modified to create a table; or altered to look for specific tables.....

Code:
'---------------------------------------------------------------------------------------
' Procedure : shamilQdfs
' Author    : mellon..from....shamil
' Date      : 10/02/2016
' Purpose   : List Queries and the fields within them
'---------------------------------------------------------------------------------------
'
Sub shamilQdfs()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim qdfIndex As Integer
10  On Error GoTo shamilQdfs_Error

20  Set dbs = Access.Application.CurrentDb
30  qdfIndex = 1
40  For Each qdf In dbs.QueryDefs
50      Debug.Print qdfIndex & ". Query: " & qdf.name
        Dim fld As DAO.Field

60      For Each fld In qdf.Fields

70          Debug.Print " " & (fld.OrdinalPosition + 1) & _
                        ". " & fld.SourceTable & "." & fld.SourceField

80      Next fld

90      Debug.Print ""
100     qdfIndex = qdfIndex + 1
110 Next qdf

120 On Error GoTo 0
130 Exit Sub

shamilQdfs_Error:

140 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure shamilQdfs of Module AccessD_KillText"
End Sub


Sometimes it's a good learning exercise to do things like
-find and list the Record source of each Form and Report (can be a handy utility to support documentation)
-create a Form with a button and a combo where the rowsource of the combo is a list of all Forms, or all reports); select a form(or report) name, click the button to display the Form(or report).

Also, if the database is not critical, then, as others have suggested, rewrite a particular function/set of functions from scratch. You'll certainly understand the process once completed. Now, add lots of comments to the code --because they're clear in your head. It may save you or others who come after you, to decipher the logic of the process.

Good luck with your documentation project.

hanks. Lots of points for me to follow up!
 

Users who are viewing this thread

Back
Top Bottom