Object dependencies using VBA

[Alc]

I'm trying to make sense of some old databases. Each has a large number of tables and forms I'm sure aren't needed any more and I want to get rid of them.
The Object Dependencies tool is useful but, given the hundreds of items I'm checking, slower than I'd like.

I found some code to search for all queries that use a certain table, this works very well.

What I can't find or figure out is a way to use VBA to check if a table is used by a form. This could be either as the form's data source or to populate a combo box/list box on the form.

Is anyone aware of anything like this? I'm assuming it's possible and just beyond my abilities.

In case it helps, following is the code that checks queries for a given table.

Function Check_Queries(strName As String, Db As Database)
    Dim strSQL As String
    Dim Rst As Recordset

          strSQL = "SELECT DISTINCT " & _
                   "MSysObjects.Name AS QueryName " & _
                   "From MSysQueries INNER Join MSysObjects " & _
                   "ON MSysQueries.ObjectId = MSysObjects.Id " & _
                   "WHERE " & _
                   "(MSysQueries.Name1 Like '*" & strName & "*' " & _
                   "OR MSysQueries.Name2 Like '*" & strName & "*' " & _
                   "OR MSysQueries.Expression Like '*" & strName & "*') " & _
                   "AND MSysObjects.Name Not Like '*sq_CF*' " & _
                   "AND MSysObjects.Name Not Like '*MSys*'" & _
                   "AND MSysObjects.Name Not Like '*~*'"
          Set Rst = Db.OpenRecordset(strSQL)
          If Not Rst.EOF Then
              Rst.MoveFirst
              Do While Not Rst.EOF
                With txtResults
                    .SetFocus
                    .Value = txtResults & "[" & strName & "] is used by [" & Rst!QueryName & "] <BR>"
                    .SelStart = Len(.Value)
                End With
                Rst.MoveNext
              Loop
            Else
                With txtResults
                    .SetFocus
                    .Value = txtResults & "No queries reference table [" & strName & "] <BR>"
                    .SelStart = Len(.Value)
                End With
            End If
End Function

 

[arnelgp]

you need to loop through each Forms/Reports and check the
Rowsource if it contains the table/query.
then loop through each controls if the ControlSource contains the table/query name.

 

[Alc]

you need to loop through each Forms/Reports and check the
Rowsource if it contains the table/query.
then loop through each controls if the ControlSource contains the table/query name.

D'oh, that makes perfect sense.
Thanks a lot.

 

[Minty]

V-Tools Deep search will I think find that.

 

[Isaac]

you need to loop through each Forms/Reports and check the
Rowsource recordsource if it contains the table/query.

 

[bastanu]

Have a look at Allen's find field module, you might be able to adapt it for your use:

Microsoft Access tips: Where is a field used?

VBA code to identify tables, queries, forms, or reports that use a particular field name.

allenbrowne.com

Cheers,

 

[NauticalGent]

Give this a try: https://github.com/strive4peace/Analyzer

 

[gemma-the-husky]

This will search all your queries for any given fieldname, or string in general, and save the search to a text file.
I've had versions of this to search for more than 1 string, just to search action queries, or even to search for 1 string, but not another, but this is the basic code. Just change the searchfor to whatever you want. It doesn't use msys tables, just uses the dao querydef object.

Sub search_queries()

Const searchfor = "anystring"


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

 

[gemma-the-husky]

@Alc

You will spend a long while trying to tidy a database up. If it's working OK and there is no particular need, I don't think I would bother, personally.

 

[Isaac]

This will search all your queries for any given fieldname, or string in general, and save the search to a text file.
I've had versions of this to search for more than 1 string, just to search action queries, or even to search for 1 string, but not another, but this is the basic code. Just change the searchfor to whatever you want. It doesn't use msys tables, just uses the dao querydef object.

Sub search_queries()

Const searchfor = "anystring"


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

That's great, I've done similar as well. Just a tip for the OP when you get output of query names that start with squigglies, ~, those are generally rowsources of controls.

 

[gemma-the-husky]

That's great, I've done similar as well. Just a tip for the OP when you get output of query names that start with squigglies, ~, those are generally rowsources of controls.

I thought the squigglies (tildes) were deleted queries waiting for a C&R.

 

[Isaac]

I thought the squigglies (tildes) were deleted queries waiting for a C&R.

in my experience they have been actual form controls' rowsources, in cases where the rowsource was saved by going through the ellipsis button (not a reference to a saved object)

 

[Pat Hartman]

I would look at Total Access Analyzer by www.fmsinc.com. I've used this tool for years. It isn't free but it is reasonably priced and your employer/client will most likely be willing to pay for it. It has a huge number of reports that identify things like unused objects and various errors as well as producing useful documentation.

Whatever tool you choose, the process of clean up should be the same.
1. IMPORTANT - turn off the Name Auto Correct feature if it is currently on in your database. Although this tool can be helpful if you know what you are doing, in this particular case, it is downright dangerous because it will work as cross purposes with what you are trying to do which is to isolate and eventually get rid of unused items.
2. Once you think an object is not needed, rename it with some prefix like "zzzz". That drops everything to the bottom of the list and at some point, you will delete these objects or move them to an archive if you prefer. If you haven't disabled Name Auto "Corrupt", it will help you by changing other objects that reference this one to reference the "new" name. That is why it is imperative to have this feature off. I leave it off permanently and only turn it on when I actually want to rename objects and I want it to help me.
3. After a few weeks or months of normal operation with no errors, you can feel comfortable throwing out the trash.

For the future, you might want to clean up you style. When I create objects that I know are temporary or have nothing to do with the application but I'm creating them in this database for some reason, I use a specific prefix so I can always identify them and know to never use them for anything relating to the real work of the app. The other best practice is when you replace some code or object with a different one, rename the object immediately to zzzz or xxxxx so you won't have to figure out later if the object is useful or not. With code, I usually just replace it. But, sometimes I'm not sure what the best solution is going to be. In that case I comment the code. I use the block commenting tool which lets me use dozens of single quotes to shift the code off to the right so it is visually out of the way and doesn't interfere with reading the remainder. Then later if the code is actually useful but just not here, I move the code to my utilities database and add it to a relevant category. Otherwise I delete the commented code. There is no useful purpose to keeping unused code in your modules. It is just clutter. Get rid of it as soon as it is safe to do so.

 

[isladogs]

For info...
1. Objects with names starting ~TMPCLP were are objects deleted before a program crash. Normally these are removed automatically when Access closes but not when Access crashes
2. Objects with names starting ~sq... are row sources in forms/reports:
~sq_f in a form, ~sq_c in a form control, ~sq_r in a report, ~sq_d in a report control

For more info, see Remove Deleted Objects - Mendip Data Systems

 

[Isaac]

Ahh, yes! I forgot the "sq" portion of significance. Thanks for the add
Last time I used this was a year or two ago, inheriting an old monster of an access database from someone else. It was like picking through the ruins of an ancient civilization.

 

[Uncle Gizmo]

It was like picking through the ruins of an ancient civilization

I have had a similar experience looking back through some of my first attempts!