Finding Outgoing Connections from Home Table

[atest01]

Hi everyone,



I have very basic access skills, and I have been tasked with finding all of the outgoing connections our home table/database has. In other words, we are trying to see what other tables and data sets are using the home table. Is there way of seeing outgoing connections?

Thanks in advance for your help. I'm super stuck.

[aytee111]

Unfortunately there is no such thing as outgoing connections. Are you trying to find all Access/Excel/Word files on your entire system that use a certain table in a certain database?

[orange]

More info please.
What specifically were the specifications given to you?
Desktop or web based application?
What is home table?
Show us a jpg of your relationships window.

[ranman256]

paste this code into a module , run it via a button or in the immediate window.
it will write all connections in the immediate window.

Public Sub dpConnections()
Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
   If tdf.Connect <> "" Then Debug.Print tdf.Name, tdf.Connect 
next
Set tdf = Nothing
End Sub

[atest01]

So in other words, you know how we can link data to the source by creating a linked table? I am wondering if we can see it from the other side, and see what tables the given "master" or
"source" table is linked to.
By home table, I mean our Master Reference table, which is used for numerous access files.

Does this clarify? Thanks again for your help.

[orange]

How about giving readers a 5-6 sentence description of the application (what does this represent in your business?)?
Master reference table? Like Countries of the world? Locations of branch offices? Readers are having to guess what you're talking about.

[ranman256]

can you tell who is watching you as you drive down the street?
no.

[atest01]

Sure, here is the application of this issue. This "master reference" holds information about insurance companies and subsequent payers. We are attempting use one table as a reference for all patients. However, we would like to tell what Microsoft Access tables are using this "Master Reference Table" and which tables are not, in hopes of tracing where information is coming from. Otherwise, we will have to go through hundreds of Tables and make sure they are linked to the Master Reference, instead of older and more dispersed files.

[aytee111]

Rename the table and wait for things to blow up.

[atest01]

Although I like that idea, I don't think my supervisor would be too happy with me.

Are there any other ideas as to how to see these connections? Just hoping I don't have to manually look through each database to see what each table is linked to.

Thanks again!

[aytee111]

There is a query which will read all linked tables:

SELECT DISTINCTROW msysobjects.Name, msysobjects.Database, msysobjects.Connect
 FROM msysobjects IN 'pathname\databasename' WHERE (((msysobjects.Type)=6 Or (msysobjects.Type) Like "dsn*")) ORDER BY msysobjects.Database;

You can create a list of all your databases in a table and read thru the list, running this query for each one. Or you can loop thru folders looking for database files and for each one run this query.

Note: if the name of the table has been renamed in that database it doesn't show the original name.

[orange]

we would like to tell what Microsoft Access tables are using this "Master Reference Table"

It is not clear (to me) exactly what your expectations/requirements are. Others are interpreting this as "linked tables" and that may be the fact involved. You mention tables using tables --which is not typical database terminology -and that may be the source of a lack of clarity.
We often speak of relationships between/among tables - based upon business rules.
Can you give us a sample of your tables using tables? Specific tables, not an abstraction.

Queries can use reference tables as described here:

You might have a people (tblPeople) in your database, and that table could house information related to
--lawyer, accused, witness, judge, police officer.... depending on the "business set up".

Or another example, using CountryReference table:
ShipperCountry, PurchaserCountry, SupplierCountry, SellerCountry

So an example could be very helpful.

Good luck. An interesting assignment.
Here is a link to Barry Williams' site showing a number of free data models related to the Insurance industry.

PS:
What documentation exists for this application?
Who maintains it? What do they use for reference materials?
Is there any sort of technical overview, data model, problem reporting system related to the application?
When was the system designed, redesigned, modified?
Are there training manuals for new personnel?