Code to find table dependencies of queries

[Matt Williamson]

I have about 100 Access MDB's that have a reference to a table in
multiple queries that I need to replace with a new table. I've written the
following code to do so, but it keeps giving me the error about dependency
info not being updated. I thought adding 'oApp.SetOption "Track Name
AutoCorrect Info", 1' should turn it on for each instance of Access that I'm
opening. Do I need to save the database first?


Public Sub FindQueryDependsInMDBs()
Dim oApp As Access.Application
Dim oAccTable As AccessObject
Dim oAccQuery As AccessObject
Dim sPath As String, sMDB As String

sPath = GetPath(CurrentDb.Name)
sMDB = Dir$(sPath & "\*.mdb")

Do While sMDB <> ""
If sPath & sMDB <> CurrentDb.Name Then
' Debug.Print sMDB & vbCrLf & String(20, "-") & vbCrLf
Set oApp = New Access.Application
oApp.OpenCurrentDatabase (sPath & "\" & sMDB)
oApp.SetOption "Track Name AutoCorrect Info", True 'tried 1 here
as well
For Each oAccTable In oApp.CurrentData.AllTables
If Left(oAccTable.Name, 4) <> "MSys" Then
If InStr(oAccTable.Name, "VALUE_WITH_CLASS") <> 0 Then
For Each oAccQuery In oApp.CurrentData.AllQueries
If oAccQuery.IsDependentUpon(acTable,
"dbo_VALUE_WITH_CLASS_VT_DAILY") Then
Debug.Print oAccQuery.Name & "is dependent upon
dbo_VALUE_WITH_CLASS_VT_DAILY"
End If

If oAccQuery.IsDependentUpon(acTable,
"dbo_VALUE_WITH_CLASS_VT_MONTHE") Then
Debug.Print oAccQuery.Name & "is dependent upon
dbo_VALUE_WITH_CLASS_VT_MONTHE"
End If
Next oAccQuery
End If
End If
Next oAccTable
oApp.CloseCurrentDatabase
oApp.Quit acQuitSaveNone
End If
sMDB = Dir$
Loop

End Sub

TIA

Matt

 

[Allen Browne]

There are some commercial utilities such as speedferret, or ricks world, but
try this freebie by Chas Dillon:
http://allenbrowne.com/ser-41.html

 

[John W. Vinson]

multiple queries that I need to replace with a new table. I've written the
following code to do so, but it keeps giving me the error about dependency
info not being updated. I thought adding 'oApp.SetOption "Track Name
AutoCorrect Info", 1' should turn it on for each instance of Access that I'm
opening. Do I need to save the database first?

On the contrary - turn OFF Track Name AutoCorrupt... sorry, AutoCorrect...
in any database where it's on. This feature is incorrectly implemented in
2002/3 and *WILL* cause corruption and data errors, especially if you change
objects programmatically.

I'd turn off Name Autocorrect in the database first; compact the database to
discard the name autocorrect info and clean out the pointers; and only then
change the SQL of your queries. Note that queries and tablenames might appear
many other places, in VBA code, in domain functions, etc. etc. - this is
something that may be very hard to effectively automate.

John W. Vinson [MVP]

 

[Matt Williamson]

There are some commercial utilities such as speedferret, or ricks world,
but try this freebie by Chas Dillon:
http://allenbrowne.com/ser-41.html

Thanks Allen. I've played around with the code in the CodeScan db, but I
can't see how to get the tables that a query links to from it. They don't
appear to be listed in the MSysObjects or MSysQueries, which is where this
code gets the info from. Am I missing something?

 

[Matt Williamson]

multiple queries that I need to replace with a new table. I've written the

On the contrary - turn OFF Track Name AutoCorrupt... sorry,
AutoCorrect...
in any database where it's on. This feature is incorrectly implemented in
2002/3 and *WILL* cause corruption and data errors, especially if you
change
objects programmatically.

I'd turn off Name Autocorrect in the database first; compact the database
to
discard the name autocorrect info and clean out the pointers; and only
then
change the SQL of your queries. Note that queries and tablenames might
appear
many other places, in VBA code, in domain functions, etc. etc. - this is
something that may be very hard to effectively automate.

Ok, so this Name Autocorrect is a bad thing. Is there any other way to
determine what tables a query has as a dependency? I'm using Access 2003
sp2. Almost all of the queries were created with Access 97 and were created
through the GUI design view. Only the databases I have writen have any
dynamic SQL query creation and I have separated them from the ones I'll be
running this against.

TIA

Matt

 

[Stuart McCall]

Ok, so this Name Autocorrect is a bad thing. Is there any other way to
determine what tables a query has as a dependency? I'm using Access 2003
sp2. Almost all of the queries were created with Access 97 and were
created through the GUI design view. Only the databases I have writen have
any dynamic SQL query creation and I have separated them from the ones
I'll be running this against.

PMFJI. Here's a way to get the dependent tables (for a query called Query1
in the example):

Sub test()
Dim d As DAO.Database, f As DAO.Field
Dim r As String

Set d = CurrentDb
With d.QueryDefs!Query1
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
End With
Debug.Print r
Set d = Nothing
End Sub

That produces a comma-separated list of tables in the debug window.

 

[Matt Williamson]

PMFJI. Here's a way to get the dependent tables (for a query called Query1
in the example):

Sub test()
Dim d As DAO.Database, f As DAO.Field
Dim r As String

Set d = CurrentDb
With d.QueryDefs!Query1
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
End With
Debug.Print r
Set d = Nothing
End Sub

That produces a comma-separated list of tables in the debug window.

Thanks for the code Stuart. I've modified it into a Sub with parameters so I
can pass each query in from a loop, but it keeps getting hung up when the
query def goes above 0. Isn't there an ordinal for each Query in the Queries
collection? What am I doing wrong?

Public Sub FindQueryDependsInMDBs()
Dim oApp As Access.Application
Dim oAccTable As AccessObject
Dim oAccQuery As AccessObject
Dim sPath As String, sMDB As String
Dim sCurDB As Database, x As Long

sPath = GetPath(CurrentDb.Name)
sMDB = Dir$(sPath & "\*.mdb")

Do While sMDB <> ""
If sPath & sMDB <> CurrentDb.Name Then
Set oApp = New Access.Application
oApp.OpenCurrentDatabase (sPath & "\" & sMDB)
For Each oAccQuery In oApp.CurrentData.AllQueries
GetQueryDepends x, oAccQuery.FullName
x = x + 1
Next
x = 0
oApp.CloseCurrentDatabase
oApp.Quit acQuitSaveNone
End If
sMDB = Dir$
Loop

End Sub

Sub GetQueryDepends(lnum As Long, sName As String)
Dim d As DAO.Database, f As DAO.Field
Dim r As String

Set d = CurrentDb
With d.QueryDefs(lnum)
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
End With
Debug.Print sName & vbCrLf & r
Set d = Nothing
End Sub

TIA

Matt

 

[Stuart McCall]

Thanks for the code Stuart. I've modified it into a Sub with parameters so
I can pass each query in from a loop, but it keeps getting hung up when
the query def goes above 0. Isn't there an ordinal for each Query in the
Queries collection? What am I doing wrong?

Public Sub FindQueryDependsInMDBs()
Dim oApp As Access.Application
Dim oAccTable As AccessObject
Dim oAccQuery As AccessObject
Dim sPath As String, sMDB As String
Dim sCurDB As Database, x As Long

sPath = GetPath(CurrentDb.Name)
sMDB = Dir$(sPath & "\*.mdb")

Do While sMDB <> ""
If sPath & sMDB <> CurrentDb.Name Then
Set oApp = New Access.Application
oApp.OpenCurrentDatabase (sPath & "\" & sMDB)
For Each oAccQuery In oApp.CurrentData.AllQueries
GetQueryDepends x, oAccQuery.FullName
x = x + 1
Next
x = 0
oApp.CloseCurrentDatabase
oApp.Quit acQuitSaveNone
End If
sMDB = Dir$
Loop

End Sub

Sub GetQueryDepends(lnum As Long, sName As String)
Dim d As DAO.Database, f As DAO.Field
Dim r As String

Set d = CurrentDb
With d.QueryDefs(lnum)
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
End With
Debug.Print sName & vbCrLf & r
Set d = Nothing
End Sub

TIA

Matt

Well first of all it would be easier and more efficient to pass the QueryDef
object (oAccQuery) to the procedure, rather than an ordinal or a name :

Sub GetQueryDepends(qdf As DAO.QueryDef)
Dim d As DAO.Database, f As DAO.Field
Dim r As String

Set d = CurrentDb
With qdf
Debug.Print vbCrLf & "QUERY " & qdf.Name
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
End With
Debug.Print Tab(5); r
Set d = Nothing
End Sub


Secondly, in your FindQueryDependsInMDBs procedure, you don't need to
automate Access, just use the DAO library:

Public Sub FindQueryDependsInMDBs()
Dim sPath As String, sMDB As String
Dim oCurDB As DAO.Database
Dim oAccQuery As DAO.QueryDef

sPath = GetPath(CurrentDb.Name)
sMDB = Dir$(sPath & "\*.mdb")

Do While sMDB <> ""
If sPath & sMDB <> CurrentDb.Name Then
Set oCurDB = DAO.OpenDatabase (sPath & "\" & sMDB)
For Each oAccQuery In oCurDB.QueryDefs
GetQueryDepends oAccQuery
Next
oCurDB.Close
Set oCurDB = Nothing
End If
sMDB = Dir$
Loop

End Sub

 

[David W. Fenton]

I've modified it into a Sub with parameters so I
can pass each query in from a loop, but it keeps getting hung up
when the query def goes above 0. Isn't there an ordinal for each
Query in the Queries collection? What am I doing wrong?

Why are you using a counter for this? You're using a loop that has:

For Each oAccQuery In oApp.CurrentData.AllQueries

So, all you need to do inside the loop is:

GetQueryDepends x, oAccQuery.FullName

And change GetQueryDepends to use a variable of type AccessObject,
and in that code do this:

Sub GetQueryDepends(oAccQuery As AccessObject)
f As DAO.Field
Dim r As String

With oAccQuery
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
Debug.Print .Name & vbCrLf & r
End With
End Sub

Saves a lot of issues, since you don't have to look the thing up a
second time.

 

[Stephen K. Young]

Set d = CurrentDb

With d.QueryDefs!Query1
For Each f In .Fields
If InStr(1, r, f.SourceTable) = 0 Then
If Len(r) Then r = r & ","
r = r & f.SourceTable
End If
Next
End With

One difficulty with the .SourceTable property is that it does not work for
Union queries.
For Union queries it always returns "" in my experience.
You may want to parse Union queries separately to find the source tables.

- Steve

 

[Stuart McCall]

One difficulty with the .SourceTable property is that it does not work for
Union queries.
For Union queries it always returns "" in my experience.
You may want to parse Union queries separately to find the source tables.

- Steve

Good point. And one I hadn't realised. Thanks!