Determine which databases on network a table is linked to?

[RD]

I have a table that is referenced in many other databases out on our
network. We need to update and move the location of the source table,
but before we do so we need to understand which other databases will
be impacted. Is there a way to determine which databases are linking
to this specific table? Like a Linked Table Manager in reverse? Help
is appreciated.

RJK

 

[Douglas J. Steele]

No, there's no equivalent of an inverse Linked Table Manager. It would be
impossible to build one, since realistically the "linked to" database has no
idea what's accessing the data inside it.

Even if you were to open every database in your corporation to check whether
there are any tables linked to database in question, that still wouldn't
guarantee you caught all usage of the database.

 

[aaron.kempf]

if u were using SQL Server; then you could use profiler to do this.

Thanks

-Aaron

 

[Larry Linson]

Even if you were to open every database in your
corporation to check whether there are any tables
linked to database in question, that still wouldn't
guarantee you caught all usage of the database.

You can examine the .Connect property of each linked database. Unless the
linked-from database's linkage is frequently changed over time, that'll give
you a pretty good idea -- and, ymmv, but in my experience, links do not
change just "willy-nilly". Not very often, at any rate.

The "trick" here, of course, is "How do you know you have, in fact, examined
ALL the databases in your environment?"

Best regards,

Larry Linson
Microsoft Office Access MVP

 

[Douglas J. Steele]

You can examine the .Connect property of each linked database. Unless the
linked-from database's linkage is frequently changed over time, that'll
give you a pretty good idea -- and, ymmv, but in my experience, links do
not change just "willy-nilly". Not very often, at any rate.

The "trick" here, of course, is "How do you know you have, in fact,
examined ALL the databases in your environment?"

And all the Excel spreadsheets that use the data, and all the VB programs
that access the data, and all the .....

 

[Douglas J. Steele]

But that would only tell you what accessed the SQL Server data while you
were running the Profiler. If a particular application only runs once a
year, and you didn't happen to be profiling when it ran, you'd never know
about it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


if u were using SQL Server; then you could use profiler to do this.

Thanks

-Aaron