How do I find the remote DB's that link to my tables?

[Bruce Lawrence]

We use Access 97. We are in the process of migrating to 2003 along
with SQL in most cases. We have a production database that contains a
pretty important table and we would like to know which other databases
on our network link to this table.

The reason is because we plan to add/remove certain fields in this
table when we put it in SQL but we would like to know ahead of time
which databases will be affected by this.

I seem to remember a report that would show me which other databases
are linking to specific tables in my database.

Thanks

[Allen Browne]

Here's a quick'n'dirty way to get a list of the linked tables and what they
are linked to:

Function ShowConnect()
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()

For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
If tdf.Connect <> vbNullString Then
Debug.Print tdf.Name, tdf.Connect
End If
End If
Next

Set tdf = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Lawrence" <BL*****@gmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

We use Access 97. We are in the process of migrating to 2003 along
with SQL in most cases. We have a production database that contains a
pretty important table and we would like to know which other databases
on our network link to this table.

The reason is because we plan to add/remove certain fields in this
table when we put it in SQL but we would like to know ahead of time
which databases will be affected by this.

I seem to remember a report that would show me which other databases
are linking to specific tables in my database.

Thanks

[Bruce Lawrence]

What exactly does this do? From what I can tell it prints off a list
of linked tables in my database and where they go? If so... its close
but not quiiiite what I'm looking for.

I have a DB with a table in it called OPENORD. I want to know what
other databases on my network link to that OPENORD table.

[Lyle Fairfield]

The situation you describe reflects neglect or incompetence on the part
of management. It's reprehensible to have an important database without
complete documentation of everything about it. Moving to whatever you
mean by "SQL" is likely to worsen the situation..

Hire a knowledgeable and capable database administrator.

[Allen Browne]

You will need to locate all databases on the network, open them, loop
through their table defs, get the Connect property of each one, and then
resolve any logical drive letters/paths.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Lawrence" <BL*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

What exactly does this do? From what I can tell it prints off a list
of linked tables in my database and where they go? If so... its close
but not quiiiite what I'm looking for.

I have a DB with a table in it called OPENORD. I want to know what
other databases on my network link to that OPENORD table.

[Bruce Lawrence]

Lyle,

How comfortable you must feel sitting back and being a critic of
everyone else around you. A critic without one peice of information to
support his position. A position which can easily be brushed aside
because of: 1. Your obvious lack of tact. 2. You assume to know so
much about our situation yet you don't know what I mean by moving to
SQL. 3. If you knew half as much as you thought you did, you'd be
alot better off.

Hire a knowledable and capable database administrator? You mean
someone such as yourself right? Lyle, you would be hired for your
"talents" and fired for your attitude within the first week bud. Work
on that.

Thanks !

[Lyle Fairfield]

I can assure you that I would not be hired.

[DFS]

Lyle Fairfield wrote:

The situation you describe reflects neglect or incompetence on the
part of management. It's reprehensible to have an important database
without complete documentation of everything about it.

Have you never set foot inside corporate America? What you described is
*typical*.

[Lyle Fairfield]

No.
Oh!

[Tim Marshall]

Bruce Lawrence wrote:

Hire a knowledable and capable database administrator? You mean
someone such as yourself right? Lyle, you would be hired for your
"talents" and fired for your attitude within the first week bud. Work
on that.

Ultimately, what he says is true, though, Bruce.

He said nothing about the ease of managing such linking. I personally
think it's a very, very hard thing to manage, myself. I run a major
Oracle application and I've lost count of how many Access apps I have
MYSELF designed and have linking to the Oracle data. This is perhaps a
slightly different arrangement from the jet back end you describe, but
very similar in a lot of ways - if people have access to the mdw or the
mdb/e back end (for jet) or have the password to the appropriate Oracle
user they can link to the data willy nilly.

Here is what I do to manage such connections. I wonder if there are
other best practices others use that can be suggested?

I only allow people access to my Oracle data on a READ ONLY basis - only
I can write apps that change the BE data (this is an additional ability
not present in a user profile in a Jet mdw if the "lnker" is using
his/her own user id and that user id has write access on some or all
tables). I don't believe this can be done via the Jet mdw route.

If you have uncooperative staff, it is very difficult, I think, to
restrict who links to what. It behooves one to put in some control
measures - these can either by a standard operating procedure in your
department which insists that people are free to write their own apps,
but must let the administrator know what apps they create that do this
or by insisting users plop in a module supplied by you that checks for
back end version as part of the start up procedures for the app. The
consequence for not following either of these is to be left completely
out of the loop when it comes time to change the structure or even, as
in your case, the database engine of the back end.

For anyone else, are there other measures that can be taken?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DFS]

Lyle Fairfield wrote:

No. Why?
Oh!

Sigh

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:

Here's a quick'n'dirty way to get a list of the linked tables and
what they are linked to:

That doesn't answer his question.

The real answer is: there is no way to do it from the data file.

The only thing you can do is scan the network for MDBs and then
process each of them with the code Alan gave, suitably modified to
not use CurrentDB.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:ds**********@coranto.ucs.mun.ca:

I only allow people access to my Oracle data on a READ ONLY basis
- only I can write apps that change the BE data (this is an
additional ability not present in a user profile in a Jet mdw if
the "lnker" is using his/her own user id and that user id has
write access on some or all tables). I don't believe this can be
done via the Jet mdw route.

Sure it is.

But it requires never giving write access directly on the tables,
and only ever providing it through RWOP queries.

This would be, it seems to me, exactly analogous to the way you'd do
it with a server back end, so it seems the obvious approach to use.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"DFS" <nospam@dfs_.com> wrote in
news:Ck******************@bignews3.bellsouth.net:

Lyle Fairfield wrote:

The situation you describe reflects neglect or incompetence on
the part of management. It's reprehensible to have an important
database without complete documentation of everything about it.

Have you never set foot inside corporate America? What you
described is *typical*.

It may be, but just because it's common doesn't make it acceptable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

I have worked with Corporate America but only remotely.

The Canadian Corporations and Organizations with which I have worked
have seemed to be assiduous to the extreme with respect to
documentation. All had rooms of shelves of black binders documenting
everything about their IT, Network, Computers, Software etc. They were
cross-referenced and kept up-to-date by the strategem of "No
Documentation = No Implementation and No Change".

Contracting with these organizations required/requires an ageement to
document one's application in a manner consistent with their rules.
Before implementation, documentation must be complete and submitted. (I
found this onerous.)

Of course, the documentation also exists in computer files. The binders
are a last resort precaution.

One organization required that developers agree to send all e-mail
about technical matters to a 'vetting person who would determine how
threatening any suggestion, instruction might be and if she approved,
she would forward the e-mail to the intended recipient and also, make
notes in the documentation.

[Anthony England]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:

Here's a quick'n'dirty way to get a list of the linked tables and
what they are linked to:

That doesn't answer his question.

The real answer is: there is no way to do it from the data file.

The only thing you can do is scan the network for MDBs and then
process each of them with the code Alan gave, suitably modified to
not use CurrentDB.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Agreed - if the question is 'which files have linked tables to this
database?'. However, you may be able to answer other questions easily
enough, such as: 'give me a list of the machines which have accessed the
database in the last 24hrs'. There are a number of ways you could do this,
but the idea would basically be to get a periodic snapshot of the users in
the database by writing a bit of code. This obviously not infallible as it
is based on snapshots, but you could do them every minute or so.

However you could also consider the typical IT Helpdesk approach: re-name
or move the file and keep a paper list of who phones up to complain.

[David W. Fenton]

"Anthony England" <ae******@oops.co.uk> wrote in
news:ds**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:

However, you may be able to answer other questions easily
enough, such as: 'give me a list of the machines which have
accessed the database in the last 24hrs'. There are a number of
ways you could do this, but the idea would basically be to get a
periodic snapshot of the users in the database by writing a bit of
code. This obviously not infallible as it is based on snapshots,
but you could do them every minute or so.
I'm not understanding how you'd get this snapshot. Through the LDB
file? Via the ADO UserRoster?
However you could also consider the typical IT Helpdesk approach:
re-name or move the file and keep a paper list of who phones up to
complain.

I hadn't thought of that, but it really is the easiest. I did just
that in a situation like this recently, where techs in a very large
organization with thousands of PCs had lapsed into bad habits and
were setting up people to have multiple users open a single MDB. I
deleted the file and that flushed out all the users who had
incorrect setups so they could be fixed (it was fixable without a
visit to the PCs -- all they had to do was navigate to an
easily-accessible folder and doubleclick a shortcut that ran Tony
Toews' AutoUpdater, which is how things were supposed to have been
working all along).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[david epsom dot com dot au]

Look in the folder where the data is.

Make a copy of all the ldb files.

Open Each ldb File in WordPad.

A computer name will be shown in each file. This is the
computer that has the corresponding MDB file open right
now.

There is a tool (ldbViewer) that does the same thing.

If there is more than one computer name shown, then
more than one computer has the file open right now,
or some one is using Access 2000/2002/2003.

Access 97 always erases the user when the user disconnects,
so you will only see the user using the file right now.
If the file is on a server, you can see the same
information by looking at current connections. In
Server 2003, start with Admin Tools/ Computer Management

If you remove delete permission from the folder, the
LDB will not be deleted when the last user exits.
This may capture the computer name of the last
user. (I can't easily test this)

Next time you write an application, capture the user
network name and computer, and write it to an audit
table.

(david)
"Bruce Lawrence" <BL*****@gmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

We use Access 97. We are in the process of migrating to 2003 along
with SQL in most cases. We have a production database that contains a
pretty important table and we would like to know which other databases
on our network link to this table.

The reason is because we plan to add/remove certain fields in this
table when we put it in SQL but we would like to know ahead of time
which databases will be affected by this.

I seem to remember a report that would show me which other databases
are linking to specific tables in my database.

Thanks

[Anthony England]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Anthony England" <ae******@oops.co.uk> wrote in
news:ds**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:

However, you may be able to answer other questions easily
enough, such as: 'give me a list of the machines which have
accessed the database in the last 24hrs'. There are a number of
ways you could do this, but the idea would basically be to get a
periodic snapshot of the users in the database by writing a bit of
code. This obviously not infallible as it is based on snapshots,
but you could do them every minute or so.
I'm not understanding how you'd get this snapshot. Through the LDB
file? Via the ADO UserRoster?

I'd find ADO UserRoster the easiest. Get the recordset it returns and put
any new users/machines into a table of distinct user/machine combinations.
This could be a scheduled task run on the server using a vbs/exe file (no
need to start ms-access) which runs every x minutes or you could leave a
separate front end open with a timer event in the form. It would very
briefly open a connection, get the list, then close the connection. After
this it could write out the user list to another database.
And there is the obvious problem. What if another application such as an
ASP webpage accessed the data like this. If a connection was opened and
closed quickly, your snapshot of who's in may well miss this user. However,
it would be much more likely to pick up users of access front ends with
straight-forward bound forms.

However you could also consider the typical IT Helpdesk approach:
re-name or move the file and keep a paper list of who phones up to
complain.

I hadn't thought of that, but it really is the easiest. I did just
that in a situation like this recently, where techs in a very large
organization with thousands of PCs had lapsed into bad habits and
were setting up people to have multiple users open a single MDB. I
deleted the file and that flushed out all the users who had
incorrect setups so they could be fixed (it was fixable without a
visit to the PCs -- all they had to do was navigate to an
easily-accessible folder and doubleclick a shortcut that ran Tony
Toews' AutoUpdater, which is how things were supposed to have been
working all along).

Cool.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/