Single Front End with multiple Back Ends

[dch3]

Oh Great Guru of the Access Database, I come you to beseeching you to
enlighten me on the all things concerning Life, the Universe and well
everything concerning Access...

I'm going to be going one step further on the advice of a MVP and place
several temp tables into an external database linked to the Front End. (Both
the Front End and the *.MDB with the temp tables will be located on the
user's PC.) Since I'm dealing with two separate *.MDB's in two separate
locations, I'm trying to come with a simple way to keep track as to which
tables are located where to aid in relinking them if need by.

I was thinking about going with two additional tables to self-document
what's where and then use a re-linker that looks at the the data in the
tables to relink the actual tables to the correct backend.

USysBackendDatabases
Primary ID
Full File Path
Description

USysBackendTableLocations
Primary Key
Foreign Key
Table Name
Description

Thoughts? Question to the Ultimate Answer?
As they say...

Mariska Hargitay
(bowing with hands folded)

 

[Alex Dybenko]

Hi,
if you really need several BE location - then this is a right way to do. but
keep in mind that users can forget what BE they linked to. I normally store
BE path in user's registry hive, and if he need to have 2 BE (for example
actual and archive) - then I make 2 copies of FE, link each to appropriate
BE and name it accordingly

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Klatuu]

It is pretty much self documenting. the Connect property of a tabledef will
tell you where it is connected to. For example, here is the results of
looking at the property in the immediate window for one of my experimental
mdbs:
?currentdb.TableDefs("tblclient").Connect
;DATABASE=T:\Tricks_be.mdb

So, the path to the be will alwasy be right after DATABASE=
So

strloc = currentdb.TableDefs("tblclient").Connect
?mid(strloc, instr(strloc,"DATABASE=")+9)
T:\Tricks_be.mdb

If the table is a local table Connect will return vbNullString or ""

 

[dch3]

1) By self-documenting, I was referring to the ability of the database to
indicate which tables are expected to be where independent of whether or not
the tables are actually linked.

2) For my good Russian comrade citizen who needs to find a good Pub, or an
even better Taverna, I'm not looking at connecting to live data and archive
at the same time. Rather, live data and a disposable *.mdb file with temp
files.

Sign me a Decadent Western Bourgeois Capitalist Pig.
David H

 

[Klatuu]

Then create a table with the the locations you need.
Set the Connect proprty of the tables to the stored location to link or
relink your tables.

As to your comment number 2, I am going to assume you did not intend to be
insulting and rude to a very nice gentleman who is world class in Access and
like everyone else here a volunteer giving his time and knowledge at his own
expense to help others with their problems and leaning and advance the Access
community. And I would caution you to be careful of your statements. In an
international environment such as this, not everyone understands the same
attempts at humor.

 

[david]

For historical reasons, our database re-connects links to the same
table in the same database in a new location. Our table only lists
the exceptions, where the table names have changed or the database
has changed or the location is not the main location. That means
the table only has a dozen lines: the hundreds of normal links are
handled by the default algorithm.

(david)