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)

 

[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!