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.
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.
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.
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.
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?
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.
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.
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.
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.
