L
LarryP
A need is coming at me shortly that will involve a lot of dynamic table
re-linking.
SCENARIO: my company has locations in several cities. In Tucson we have
lots of people using several MSAccess databases, made available via an
MSAccess "master switchboard" database. The switchboard itself and each of
the databases has many linked tables.
People in other cities can't use these databases now because data transfer
across our WAN won't support it. We're just about to set up Other City A (if
it works we'll move on to the rest) with its own set of the back end files,
refreshed weekly via FTP or whatever. When people fire up the switchboard or
a specific database there, our front ends will be "tweaked" to know that for
them, the needed back end stuff is at server location Y rather than server
location X.
I'm thinking this agility will simply require a table that says for location
A, server path is xxxxxx, for location B it's yyyyyyy, etc. Each time a
front end (including the switchboard) is opened, it will somehow (???)
determine WHERE it has been opened, and will look up the corresponding server
path in the table and use it to reset all the table links.
Any thoughts on whether this seems to be a viable idea are welcome. Also,
is there a best single source anyone can suggest for info on setting and
re-setting table links dynamically -- a KB, a web site, a book? If there's
no such thing I'll just graze around here and elsewhere until I accumulate
all the how-to information.
Secondarily, about the weekly file transfer: Assuming I can map to the
other server, I'm thinking I could move the files with FTP, with DOS copy
commands in a batch file, or with MSAccess filecopy. Any thoughts about
which would be best -- faster, more reliable, etc? We're talking maybe 800mb
worth of .mdb, .xls, .txt, and .bat files, and we don't want to tie up their
WAN bandwidth any longer than necessary each week.
re-linking.
SCENARIO: my company has locations in several cities. In Tucson we have
lots of people using several MSAccess databases, made available via an
MSAccess "master switchboard" database. The switchboard itself and each of
the databases has many linked tables.
People in other cities can't use these databases now because data transfer
across our WAN won't support it. We're just about to set up Other City A (if
it works we'll move on to the rest) with its own set of the back end files,
refreshed weekly via FTP or whatever. When people fire up the switchboard or
a specific database there, our front ends will be "tweaked" to know that for
them, the needed back end stuff is at server location Y rather than server
location X.
I'm thinking this agility will simply require a table that says for location
A, server path is xxxxxx, for location B it's yyyyyyy, etc. Each time a
front end (including the switchboard) is opened, it will somehow (???)
determine WHERE it has been opened, and will look up the corresponding server
path in the table and use it to reset all the table links.
Any thoughts on whether this seems to be a viable idea are welcome. Also,
is there a best single source anyone can suggest for info on setting and
re-setting table links dynamically -- a KB, a web site, a book? If there's
no such thing I'll just graze around here and elsewhere until I accumulate
all the how-to information.
Secondarily, about the weekly file transfer: Assuming I can map to the
other server, I'm thinking I could move the files with FTP, with DOS copy
commands in a batch file, or with MSAccess filecopy. Any thoughts about
which would be best -- faster, more reliable, etc? We're talking maybe 800mb
worth of .mdb, .xls, .txt, and .bat files, and we don't want to tie up their
WAN bandwidth any longer than necessary each week.