|< Day Day Up >|
Access objects can depend on other objects. For example, a form might use a query as its data source, and the query in turn might draw its information from two or more tables. In this case, the form is directly dependent on the query, and indirectly dependent on the tables. Starting with Access 2003, this information is available through the Access user interface, and also programmatically.
If you haven't looked at this information in the Access user interface, it's easy enough to find. Right-click on any object in the Database window and select Object Dependencies. Doing so opens the Object Dependencies task pane, as shown in Figure 15.5. You can switch between displaying the objects that depend on this object, and the object that this object depends on, by using the radio buttons at the top of the task pane.
Figure 15.5. Viewing object dependencies in the user interface.
For the object dependencies feature to work, Name AutoCorrect must be on for Access. You can turn on this option using the General tab of the Tools, Options dialog box. You must save and close your objects before Access can generate their dependency information.
To discover object dependencies in VBA, you go through the AccessObject object to the DependencyInfo object. Here's some code that shows you how this works:
Public Sub ShowDependencies(intType As AcObjectType, _ strName As String) ' Show dependency information for the specified object Dim AO As AccessObject Dim AO2 As AccessObject Dim DI As DependencyInfo On Error GoTo HandleErr ' Get the AccessObject Select Case intType Case acTable Set AO = CurrentData.AllTables(strName) Debug.Print "Table: "; Case acQuery Set AO = CurrentData.AllQueries(strName) Debug.Print "Query: "; Case acForm Set AO = CurrentProject.AllForms(strName) Debug.Print "Form: "; Case acReport Set AO = CurrentProject.AllReports(strName) Debug.Print "Report: "; End Select Debug.Print strName ' Get the dependency info Set DI = AO.GetDependencyInfo() ' Print results If DI.Dependencies.Count = 0 Then Debug.Print "This object does not depend on any objects" Else Debug.Print "This object depends on these objects:" For Each AO2 In DI.Dependencies Select Case AO2.Type Case acTable Debug.Print " Table: "; Case acQuery Debug.Print " Query: "; Case acForm Debug.Print " Form: "; Case acReport Debug.Print " Report: "; End Select Debug.Print AO2.Name Next AO2 End If If DI.Dependants.Count = 0 Then Debug.Print "No objects depend on this object" Else Debug.Print "These objects depend on this object:" For Each AO2 In DI.Dependants Select Case AO2.Type Case acTable Debug.Print " Table: "; Case acQuery Debug.Print " Query: "; Case acForm Debug.Print " Form: "; Case acReport Debug.Print " Report: "; End Select Debug.Print AO2.Name Next AO2 End If ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description, vbCritical Resume ExitHere End Sub
That's a big chunk of code, but if you take it one step at a time it will be pretty simple to understand by now. The first order of business is to retrieve the AccessObject about which the user requested information. The procedure requires two arguments: the type of object and the name of the object. Because Access already provides an enumeration (acObjectType) for the possible object types, you can use that same enumeration here; that's simpler than defining your own. The first Select Case statement does two things with this constant. First, it determines which object collection to use to return the correct AccessObject object. Second, it prints a message to the Immediate window with the object's name and type.
The next step is to retrieve the DependencyInfo object, which you do with the GetDependencyInfo method of the AccessObject. The DependencyInfo object in turn has two collections of its own, each of which also contains AccessObject objects. The Dependencies collection contains one AccessObject object for each object that the current object depends on. The Dependants collection contains one AccessObject object for each object that depends on the current object.
The remaining code in the procedure simply loops through these two collections and prints their contents to the Immediate window. Here's what the result is for one object in the TimeTrack database:
ShowDependencies acQuery, "Schedule" Query: Schedule This object depends on these objects: Table: Clients Table: Projects Table: Tasks These objects depend on this object: Report: Schedule
|< Day Day Up >|