Access 2007 - reverse of what Linked Table Manager does

[h2fcell]

Hello
I’m working in an environment where a previous Access developer using Access
2000 created several databases that share linked tables most of which were
created by a Make Table or Append query. I’m looking at a table that was
last modified 2/9/2009 but the DB does not have the query that modified it.
I’m now using Access 2007 and wanted to know if there was any way to search
for the database that has the Make Table or Append query that’s used to
modify the table. Maybe a search tool for SQL statement “INSERT INTO
tblNameâ€. Also helpful would be a way to find all the DB’s that have a link
to a table. Kind of the reverse of what Linked Table Manager does. That
shows the source of the link. I’m at the source and want to find all the
DB’s that link to that source table, if any.

 

[John Spencer MVP]

Finding all databases that link to a table in another database is impossible
(as far as I know). Well, not impossible if you can search every computer and
file server that is connected to your network. Then all you have to do is
have the needed permissions to access each of the applications and scan them.

Oh and to make it more difficult, applications created in VBA can access jet
databases (Jet is the "native" database engine for Access; Word and Excel can
query Jet databases; etc.

I think you may not understand the last modified date on a table. That is the
last time the table structure was changed (or created) - it is not the last
date and time that the contents of the table (data) was changed.

You can search for the text string by using a vba routine to step through all
the queries and look for the specific string.

=================== UNTESTED VBA ===================
Public Sub sFindInQuery(strToFind)
Dim db As DAO.Database
Dim qdef As DAO.QueryDef

Set db = CurrentDb()
For Each qdef In db.QueryDefs
If InStr(1, qdef.SQL, strToFind) > 0 Then
Debug.Print qdef.Name
End If
Next qdef
End Sub
=================== UNTESTED VBA ===================

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County