Linking Tables

[Jim Pockmire]

How can I find out the name of a mdb to link tables to?
e.g. Mdb1 uses "Call Shell" to create a second instance of
MS-Access and open Mdb2. Mdb2 then needs to create links
to tables in Mdb1 using "CreateTabledef". The issue is
there are multiple Mdb1's. How can I know which Mdb1 has
opened Mdb2 so that I can create the links?

 

[Guest]

The mdb file that is open should have an associated ldb file in the same
directory that keeps track of who is in the database.

 

[Jim Pockmire]

Thanks, but I am not sure this solves the problem. I am
trying to identify the mdb name (not user name) of the
first mdb that opens the second mdb. It is possible that
there could be multiple ldbs in the same folder.

One option is to export a table to the 2nd mdb that has
the first mdb's name, but I was hoping for something
simpler.

 

[Tim Ferguson]

How can I find out the name of a mdb to link tables to?
e.g. Mdb1 uses "Call Shell" to create a second instance of
MS-Access and open Mdb2. Mdb2 then needs to create links
to tables in Mdb1 using "CreateTabledef". The issue is
there are multiple Mdb1's. How can I know which Mdb1 has
opened Mdb2 so that I can create the links?

This is a bizarre method of managing data, and I cannot imagine what you
hope to achieve by it.

If the various MDB1s need to use some common code, put it in a mda put in
references to it. Otherwise, why not just run all the code in the MDB1s?

OLE Automation is all very well in its place, but the idea of one Access
app controlling another instance of itself gives me the heeby-jeebies.

B Wishes


Tim F

 

[Jim Pockmire]

Thanks for the reply. This a large app with over 9,000 objects (yes 9,000,
3,000 reports and 5,000 queries) 600mb without any data (only ODBC links). I
didn't create it, but I am trying to help make it more manageable. Many of
the objects are seldomly used and the idea was to put them in a separate mdb
which would only be opened as necessary. Any thoughts?

 

[Tim Ferguson]

Many of
the objects are seldomly used and the idea was to put them in a
separate mdb which would only be opened as necessary. Any thoughts?

By instinct, I'd have phrased that as, "several mdbs"... Try to think of
one mdb for one process -- you'd probably aim for one for the data entry
clerks, one for sales admin, one for customer relations, one for stores and
inventory, one for senior management, etc. etc.

There is bound to be some common code which can be chunked off into a mda
(add-in), but the individual users will be happier with their own small,
speedy, appropriate mdb than they were with one megalithic (please don't
say shared) monster that breaks the Invoicing Report whenever you update
the Salesforce Regions form.

Best of luck!

Tim F