How do I find references to Database Objects?

[gaffonso]

I've got a database that needs some better organization. One of the things I'd like to do is change the query name to be more standardized and descriptive but changing the name is going to break forms, reports, queries, combox boxes, etc. that rely on those queries having their current names.

Is there any easy way to get a list of all the objects that rely on a particular query? That way I could go through each of the dependent objects and change the name there as well.

Thanks for any help,

- Gary

 

[Aivars]

Hi, Gary!

I'm not sure about Ac97, but Ac2000 certainly changes itself all recordsources of reports and forms when is changed query name or table name.

Aivars

 

[gaffonso]

I have noticed that reports and forms seem to "do the right thing" when query name changes, but there are plenty of objects that dont. Two that come immediatelly to mind are other queries, and combo boxes that are using queries as Row Sources.

A "show dependent objects" feature of some sort would be VERY helpful.

- Gary

 

[MichaelRed]

Look into the MSys* Tables. MSysObjects is generally the starting place. The MSys* tables are normally hidden, so to just 'browse' them you need to set the hidden objects visible.

On the other hand, you are undertaking a daunting task. As you responed to the earllier post, Ms. Access is somewhat remiss in chasing down all references to objects whose name may be changed.

I would suggest that you take one of two approaches (or perhaps combine these).

1.[tab]Get "Speed Ferret", which is a commercial third party utility for Ms. Access. It claims to include a 'Universal' neme changer which is what you are asking for.

2.[tab]Do the changes on a COPY of the db. Make the changes in SMALL increments and TEST, TEST, TEST the incremental changes.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[VogonPoet]

You can create a form that changes all the objects that reference a query. Include a button and include the following code in the click event. You will also need two text boxes - txtOldNm and txtNewNm


Private Sub Command0_Click()
Dim db As DAO.database
Dim qdf As querydef
Dim rpt As Report
Dim frm As Form

Set db = CurrentDb

For Each qdf In db.QueryDefs
If InStr(1, qdf.SQL, Me.txtOldNm) > 0 Then
qdf.SQL = Replace(qdf.SQL, Me.txtOldNm, Me.txtNewNm)
End If
Next

For Each rpt In db.Reports
If InStr(1, rpt.RecordSource, Me.txtOldNm) > 0 Then
DoCmd.OpenReport rpt.Name, acViewDesign
rpt.RecordSource = Replace(rpt.RecordSource, Me.txtOldNm, Me.txtNewNm)
DoCmd.Close acReport, rpt.Name, acSaveYes
End If
Next

For Each frm In db.Forms
If InStr(1, frm.RecordSource, Me.txtOldNm) > 0 Then
DoCmd.OpenForm frm.Name, acDesign
frm.RecordSource = Replace(frm.RecordSource, Me.txtOldNm, Me.txtNewNm)
DoCmd.Close acForm, frm.Name, acSaveYes
End If
Next

End Sub

 

[Databaseguy]

Note to VogonPoet

The Reports and Forms collections only reference currently open objects.

Good idea, though.. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[MichaelRed]

Further note to et. al. His process does not reference macros or modules, where various 'names' may be used.

Altogether a fairly narrow ?soloution?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Databaseguy]

The macros and modules are kind of hard to get to. They're in the Documents collections of the containers object, and don't expose many properties. I'm thinking using the Msys table. Or, use the database documentor and build a query from the resulting table, which is named Object Definition by default.

We'll have to think on this one, as it could be a handy utlity to have. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[MichaelRed]

At least in the earlier versions (pre 2K), the documents collection was the correxct approach to the issue. You can get ALL of hte objects within the collection, and they include the Macros and Modules. That still DOES NOT get all of the indirect references.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over