Checking for use of Table in Access objects 8

[sellert]

Greetings,
I have recently taken over a database which I need to clean up and relink to a different set of tables by the same definitions but slightly different names. I am trying to clean it up so I don't waste time re-linking tables that aren't even used. I suspect that there a few objects that are not needed. Is there a way to search for the name of objects such as queries that use a particular table or Report/form that uses a particular query?

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[NvrSaDi]

The following code will save the SQL code for each query in the database to a table named SQLCode. Set up the SQLCode field as a memo field. Once you have the SQL code for each of the queries in a table, you can do a text search by table nameas to establish relationships.


Public Sub FindSQL()
Dim db As Database
Set db = CurrentDb

Dim rst As Recordset
Set rst = db.OpenRecordset("SQLCode")

Dim f1 As Field
Set f1 = rst.Fields("qryName")

Dim f2 As Field
Set f2 = rst.Fields("SQLCode")

Dim f3 As Field
Set f3 = rst.Fields("Type")


Dim qdf As QueryDef
For Each qdf In db.QueryDefs
rst.AddNew
f1 = qdf.name
f2 = qdf.SQL
f3 = qdf.Type
rst.Update
Next

End Sub

 

[Remou]

It is possible to check MSysObjects and MSysQueries.

 

[sellert]

AccessDB,
Thanks for your help it worked!!

Remou,
I looked at those but didn't see any of the table names in it. Can you shed some light on how to use these tables?

I also need the same sort of code to find what tables or queries are used for Forms and Reports.

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[NvrSaDi]

You can modify the previous formulas with the following.
It will print the recordsource of the form to the table.
It only works for forms that are open. If the recordsource is a query or table, it will print the query name or the table name. If the recordsource is a SQL string, it will print the string.
Forms that have a blank recordsource will require that you modify the "SQLCode" table to permit "Allow Zero Length"

Dim frm As Form
For Each frm In Forms
rst.AddNew
f1 = frm.Name
f2 = frm.RecordSource
rst.Update
Next

 

[MichaelRed]

While the options provided above can, with some small bit of effort, easily return all the 'stored proceder' queries, they will not find any sql embedded in code.

I know of no way to completly locate all such items, as the codeing style of individuals can be quite diverse (challenging?). Some may be east to find. Examples sof easy ones may include a simple search for "RunSQL" or "Execute". Somewhat more difficult is a review of portions of code to see the (previous?) users favorite variable name for embedded SQL strings (e.g. sSQL or strSQL or ... ). More challenging ones exist ...



MichaelRed

 

[NvrSaDi]

MichaelRed makes an excellent point.
One additional thought is to create a list of tables and queries using code.

Then use that list to search through the SQL embedded in code for the table or query names. Any embedded code must eventually reference a table or query if it is to either save to a table or use the table as a record source.

 

[MichaelRed]

[qoute AccessDB] " ... reference a table or query if it is to either save to a table or use the table ... " [/quote]

but even this is far from the end of the (possible) trail ... dynamically referencing other (e.g. ODBC?) data sources ... and beyond.

My point was merely to illustrate that the issue(s) are more complex than shown or even likely to completly chased through to the ultimate goal herein. Ther are some third party tools (from modestly priced to quite expensive) which puport to "thoroughly" document MS. A. apps which do not go even as far as my inital comment. I know on NO existing tool (or set) which actually gets to the finish line of this.




MichaelRed

 

[NvrSaDi]

MichaelRed has another good point. For the simpler databases, I find the approach that I have suggested works. He is addressing a much more sophisticated level of programming.

 

[sellert]

Thank you all for your help. One more thing along these lines. I am now trying to enumerate through all the tables in the database and keep getting a Run-time error 424 Object required. Bellow is my code.

'******************** GENERAL NOTES *********************
'This procedure will store the following data to the SQLCode table:
'1. Store the underlying SQL statement for each of the queries in the database.
'2. Store the source for each of the open Forms in the database.
'3. Store the source for each of the Reports in the database
'
'Name: FindSources
'By: Steve Ellertson
'Date: 4/26/06
'Rev: 0 4/26/06
'==========================================================

Public Sub FindSources()
'******* SECTION NOTE *******
'Define all the variables
'used only in this procedure.
'----------------------------
Dim db As Database
Dim rst As Recordset
Dim f1 As Field
Dim f2 As Field
Dim f3 As Field
Dim f4 As Field
Dim f5 As Field
Dim f6 As Field
Dim qdf As QueryDef
Dim frm As Form
Dim rpt As Report
Dim resp1 As String

'******* SECTION NOTE *******
'Set all the variables
'used in this procedure.
'----------------------------
Set db = CurrentDb
Set rst = db.OpenRecordset("~ObjectSources")
Set f1 = rst.Fields("qryName")
Set f2 = rst.Fields("SQLCode")
Set f3 = rst.Fields("Type")
Set f4 = rst.Fields("FormName")
Set f5 = rst.Fields("Recordsource")
Set f6 = rst.Fields("rptName")

'*************************** SECTION NOTE **************
'Count the records, if there is at least one give the opportunity for the user to decide to empty
'out the table or add to it. If Yes to empty then loop through each record in the table and delete it.
'--------------------------------------------------------
If rst.RecordCount > 0 Then
resp1 = MsgBox("Do you want to clear the table first?", vbYesNo + vbQuestion, "CLEAR OLD DATA")
If resp1 = vbYes Then
rst.MoveFirst
Do While Not rst.EOF
rst.Delete
rst.MoveNext
Loop
End If
End If

'******************** SECTION NOTE ******************
'Does the user want to check for the source of Queries? If so, collect the underlying SQL code for
'each query object in the database and store it into the table. It can then be searched for a specific
'table name later.
'--------------------------------------------------------
resp1 = MsgBox("Do you want to gather source tables for Queries?", vbYesNo + vbQuestion, _
"QUERY SOURCES")
If resp1 = vbYes Then
For Each qdf In db.QueryDefs
rst.AddNew
f1 = qdf.Name
f2 = qdf.SQL
f3 = qdf.Type
rst.Update
Next
End If

'********************* SECTION NOTE ********************
'Does the user want to check for the source of Forms? If so, collect the name of the source for
'each open form object in the database and store it into the table. It can then be searched for a
'specific table name later.
'--------------------------------------------------------
resp1 = MsgBox("Do you want to gather source tables/queries for Forms?", vbYesNo + vbQuestion, _
"FORM SOURCES")
If resp1 = vbYes Then
For Each frm In Forms
rst.AddNew
f4 = frm.Name
f5 = frm.RecordSource
rst.Update
Next
End If

'********************** SECTION NOTE *******************
'Does the user want to check for the source of Reports? If so, collect the name of the source for
'each open report object in the database and store it into the table. It can then be searched for a
'specific table name later.
'--------------------------------------------------------
resp1 = MsgBox("Do you want to gather source tables/queries for Reports?", vbYesNo + vbQuestion, _
"REPORT SOURCES")
If resp1 = vbYes Then
For Each rpt In Reports
rst.AddNew
f6 = rpt.Name
f5 = rpt.RecordSource
rst.Update
Next
End If

MsgBox "Source data has been updated", vbInformation, "UPDATE DONE"

'********************* SECTION NOTE ********************
'Update table "~TableObjects" with the current list of tables in the database. Do not include the
'two tables used in this utility "~TableObjects" and "~ObjectSources".
'--------------------------------------------------------
Set db = CurrentDb
Set rst = db.OpenRecordset("~TableObjects")
Set f1 = rst.Fields("TableName")

'***************** SECTION NOTE ************************
'Update table "~TableObjects" with the current list of tables in the database. Do not include the
'two tables used in this utility "~TableObjects" and "~ObjectSources".
'-------------------------------------------------------
If rst.RecordCount > 0 Then
resp1 = MsgBox("Do you want to clear the table first?", vbYesNo + vbQuestion, "CLEAR OLD DATA")
If resp1 = vbYes Then
rst.MoveFirst
Do While Not rst.EOF
rst.Delete
rst.MoveNext
Loop
End If
End If

'******************** SECTION NOTE ********************
'Update table "~TableObjects" with the current list of tables in the database. Do not include the
'two tables used in this utility "~TableObjects" and "~ObjectSources".
'--------------------------------------------------------
resp1 = MsgBox("Do you want to build the list of tables in the database?", vbYesNo + vbQuestion, _
"BUILD TABLE LIST")
If resp1 = vbYes Then
Dim tdfloop As TableDef
For Each tdfloop In TableDefs 'This is where the error is.
rst.AddNew
f1 = tdfloop.Name
rst.Update
Next tdfloop
MsgBox "The table List has been updated", vbInformation, "UPDATE DONE"
End If

'************************ SECTION NOTE *****************
'Clean up the variables that were used to release the memory space. Then provide a message to the
'user that the operation is completed.
'--------------------------------------------------------
Set db = Nothing
Set rst = Nothing
Set f1 = Nothing
Set f2 = Nothing
Set f3 = Nothing
Set f4 = Nothing
Set f5 = Nothing
Set f6 = Nothing
resp1 = Empty
End Sub


Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[NvrSaDi]

Modify For Each tdfloop In TableDefs to For Each tdfloop In DB.TableDefs For forms and reports, not required. for QueryDefs and TableDefs you need to append the DB.

 

[MichaelRed]

with apologies.

If you MUST do this, (see above, I tried to hint about some of the pitfalls) you should do it more completly. The reports and forms in particular only return the currnetly open objects using your method.

I (with apologies -again) discarded the 'fluf' in your code and converted the forms and reports sections to reference the containers, so the output is more complete. Further liking 'simplicity', I also consolidated the info into a single table of three fields. Apparently you have some more elaborate intents, considerations, so the routine is certainly NOT ment as a replacement of your efforts, but meerly serves to illustrate the use of the "containers" object.

Personally, I think the MSys Objects and MSysQueries (system) tables provide a much clearer and cleaner access to the information you are (so far) collecting.

Delving into the actual db schema would, of course, also provide an excellent introduction to this same information.

Public Function FindSources()

    '******* SECTION NOTE ******* _
     Define all the variables used only in this procedure. _

    Dim dbs As Database
    Dim rst As Recordset
    Dim Cntnr As Container
    Dim Doc As Document
    Dim qdf As QueryDef
    Dim frm As Form
    Dim rpt As Report
    Dim tdf As TableDef
    Dim strResp As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblObjDefs")

    'Clear previous
    DoCmd.SetWarnings False
        DoCmd.RunSQL "Delete * from tblObjDefs;"
    DoCmd.SetWarnings True

    With rst

        '******************** SECTION NOTE ****************** _
         Collect the SQL for each query object in the database _

        For Each qdf In dbs.QueryDefs
            .AddNew
                    !objName = qdf.Name
                    !objSource = qdf.SQL
                    !ObjType = basQueryType(qdf.Type) & " Query"
            .Update
        Next

        '********************* SECTION NOTE ******************** _
         Collect the source for each form object _

        Set Cntnr = dbs.Containers("Forms")
        For Each Doc In Cntnr.Documents
            .AddNew
                !objName = Doc.Name
                DoCmd.OpenForm Doc.Name, acDesign, , , , acHidden
                Set frm = Forms(Doc.Name)
                !objSource = frm.RecordSource
                DoCmd.Close acForm, Doc.Name
                !ObjType = "Form"
            .Update
        Next

        '********************** SECTION NOTE ******************* _
         Collect the source for each report object _

        Set Cntnr = dbs.Containers("Reports")
        For Each Doc In Cntnr.Documents
            .AddNew
                !objName = Doc.Name
                DoCmd.OpenReport Doc.Name, acViewDesign, , , acHidden
                Set rpt = Reports(Doc.Name)
                !objSource = rpt.RecordSource
                !ObjType = "Report"
            .Update
        Next

        '******************** SECTION NOTE ******************** _
         Collect the Name of each tabledef object. _

         '*** I did not see the following implemented *** _
         Do not include the two tables used in this utility _
        "~TableObjects" and "~ObjectSources". _

        Dim tdfloop As TableDef
        For Each tdfloop In dbs.TableDefs
            If (Left(tdfloop.Name, 4) <> "MSys") Then
                .AddNew
                    !objName = tdfloop.Name
                    !ObjType = "Table"
                .Update
            End If
        Next tdfloop

    End With

    '************************ SECTION NOTE ***************** _
     Clean up the variables that were used to release the memory space. _

    Set dbs = Nothing
    Set rst = Nothing

End Function

MichaelRed

 

[jedraw]

I have tried the FindSources() function from MichaelRed.

I had a couple of errors, but it could be I don't have references to some files he uses.

I have made some slight mods to his function which I have included below.
Where I have changed his code I have included a ' =====>
I also made a small mod to identify the query type
ie. APPEND
SELECT etc. There may be other query types that are not in my database.

I have included a small sub called CreateTheTable()
which will create the Table to hold the outputs from the FindSources () function.

I have tried this on a copy of a test database I have. It contains a few forms, a report, several queries and a number of tables including some that are usually linked to Oracle (but I'm not connected to Oracle at this time, these tables are showing no objSource).

--- Seems to work fine and should be a good tool to review table usage---

'---------------------------------------------------------------------------------------
' Procedure : FindSources
' DateTime  : 2006-04-26 20:02
' Author    :
' Purpose   : From TekTips - people trying
'             to find table usage in Access Objects
'
' Procedure is a slight modification to the
'  FindSources by MichaelRed
'           @ 26 Apr 06 19:50
'
' This function requires a table
'   name: tblObjDefs
'
'   columns:
'               objName text(50)
'               objType text(50)
'               objSource memo  
'
'
' **  This proc will create the table and Index **
'
'Sub CreateTheTable()
'DoCmd.RunSQL ("CREATE TABLE tblObjDefs " _
'            & " ( objName text(50), " _
'            & "   objType text(50), " _
'            & "   objSource memo, " _
'            & " CONSTRAINT MyKeyZ " _
'            & " PRIMARY KEY (objName, objType) );")
'End Sub
'
'
'
'---------------------------------------------------------------------------------------
'
Public Function FindSources()

    '******* SECTION NOTE ******* _
     Define all the variables used only in this procedure. _

    Dim dbs As Database
    Dim rst As Recordset
    Dim Cntnr As Container
    Dim Doc As Document
    Dim qdf As QueryDef
    Dim frm As Form
    Dim rpt As Report
    Dim tdf As TableDef
    Dim strResp As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblObjDefs")

    'Clear previous
    DoCmd.SetWarnings False
        DoCmd.RunSQL "Delete * from tblObjDefs;"
    DoCmd.SetWarnings True

    With rst

        '******************** SECTION NOTE ****************** _
         Collect the SQL for each query object in the database _

        For Each qdf In dbs.QueryDefs
            .AddNew
                    !objName = qdf.Name
                    !objSource = qdf.sql
                    
    '=======>     ' !ObjType = basQueryType(qdf.Type) & " Query"
                    
    ' *******************************************
    ' **          Identify Query ObjTypes      **
    ' *******************************************
                    !objType = "Query -" _
                      & IIf(qdf.Type = 0, "SELECT", ( _
                       IIf(qdf.Type = 32, "DELETE", ( _
                       IIf(qdf.Type = 48, "UPDATE", ( _
                       IIf(qdf.Type = 64, "APPEND", ( _
                       IIf(qdf.Type = 80, "MK TBL", qdf.Type)))))))))
            .Update
        Next

        '********************* SECTION NOTE ******************** _
         Collect the source for each form object _

        Set Cntnr = dbs.Containers("Forms")
        For Each Doc In Cntnr.Documents
            .AddNew
                !objName = Doc.Name
                DoCmd.OpenForm Doc.Name, acDesign, , , , acHidden
                Set frm = Forms(Doc.Name)
                !objSource = frm.RecordSource
                DoCmd.Close acForm, Doc.Name
                !objType = "Form"
            .Update
        Next

        '********************** SECTION NOTE ******************* _
         Collect the source for each report object _

        Set Cntnr = dbs.Containers("Reports")
        For Each Doc In Cntnr.Documents
            .AddNew
                !objName = Doc.Name
   '=====>      DoCmd.OpenReport Doc.Name, acViewDesign , , , acHidden
                DoCmd.OpenReport Doc.Name, acViewDesign ', , , acHidden
                
                Set rpt = Reports(Doc.Name)
                !objSource = rpt.RecordSource
                !objType = "Report"
            .Update
            DoCmd.Close acReport, Doc.Name '<===close report
        Next

        '******************** SECTION NOTE ******************** _
         Collect the Name of each tabledef object. _

         '*** I did not see the following implemented *** _
         Do not include the two tables used in this utility _
        "~TableObjects" and "~ObjectSources". _

        Dim tdfloop As TableDef
        For Each tdfloop In dbs.TableDefs
            If (Left(tdfloop.Name, 4) <> "MSys") Then
                .AddNew
                    !objName = tdfloop.Name
                    !objType = "Table"
                .Update
            End If
        Next tdfloop

    End With

    '************************ SECTION NOTE ***************** _
     Clean up the variables that were used to release the memory space.
    Set dbs = Nothing
    Set rst = Nothing
    

End Function

 

[VicRauch]

I have found the SPEED Ferret from Black Moshannon Systems will do what you are looking for. Their website:

http://www.moshannon.com/

This product has saved me many, many hours over the last few years and by this time saving has more than paid for itself.

 

[mp9]

I agree with MichaelRed that MSysObjects and MSysQueries (system) tables provide a better way of doing this.

Take a look at

http://www22.brinkster.com/accessory/queries/003.shtml

for more on querying MSysObjects.

For MSysQueries your best bet is to take a look at the entries in that table for a simple query and you'll start to see what the different rows therein represent.

 

[NvrSaDi]

Gentlemen - terrific feedback - a mind expanding experience - which is why I joined Tek-Tips.

 

[sellert]

Fellow Tek-Tips Users,
Thank you all for your feed back. I completely agree with AccessDB's last comments. It has indeed been an educational thread.

Again, thank you and happy coding.

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.