Link to new database

  • Thread starter Thread starter Bill Sturdevant
  • Start date Start date
B

Bill Sturdevant

I have a pair of databases that I link to when I open my
controlling database.

Now I need an additional pair of the same database
structures that contain data for a different account.

I know I can switch my links programatically, but what
things do I need to look out for? Should I close the
original pair of linked databases before linking to the
new set?

I actually have this already set up (without the close)
but it is very slow and seems "klunky".

Any suggestions?
 
Why are you storing the data for different accounts, in different databases?

Surely they should all be in one database, with the records identified by
Account Number, so you can process whchever account(s) you need at the time?

HTH,
TC
 
The application was designed to handle complete
information about a single organization (account). It was
never intended to be used to handle more than one
organization at a time. According to the design specs, we
would use a different instance to handle each
organization. And, a different person would be running
each instance.

Now, we have come up with a short duration emergency
situation where we need a single person to handle the
databases for multiple organizations. I currently plan to
have multiple copies of the application running on the
same machine and being used by the same person, with each
copy linked to its own databases.

But, I am exploring ways to have the user use a single
copy of the software to manage the data for different
organizations. I could redesign the application to
include an "account number", but at this date that would
be a very large undertaking. So, I thought if I could use
a dropdown to select the organization, I could repoint the
app to a different organization.

As I said, the relinking works, but it is very slow. I am
sure there are considerations involved here that I do have
not thought of, and that is what I am looking for help
with.

WES
 
Bill,
I see nothing wrong this approach at all. For example, I have a TimeCard
database that supports 6 contractors, all using the same front end and back
end structures, but all are different databases for company private
information.

I only relink when I update the front end.

Would you be interested in a database switchboard (switchboard to open
different databases)? I have one written in A97 that I rely on heavily. I
consider it Freeware.


Andy
 
Andy,

I would like very much to see it. Plase send it to my
email (remove "getthespamout").

Bill
 
Ok, that all sounds fair & reasonable.

You asked what you need to be careful about when you relink your
tables to a different BE data store.

(1) There must be no open tables, queries, recordsets or similar on
the current BE database. This means no bound forms open, no
data-accessing procedures part way through, & so on. So the best place
to do it is often in an unbound "top level" menu form, where you know
that absolutely nothing is accessing data when you are in that form.

(2) Then you just relink using the linked table manager, or any of the
code that is freely available on the web; or maybe using the backend
db switchboatd the the other respondent offered to send you.

(3) If the relinking is slooooow, and you are on a networked system,
this is usually because Access is having to open the be db, create an
LDB file, relink the table, close the be db, then delete the LDB file,
*repeatededly* - ie. for each individual table linked! This can be
very time consuming. You can avoid it by holding a reference open, to
the *new* be database, during the re-linking process. Then Access
opens the be database & creates the LDB file *once*, regardless of how
many tables are relinked. Use something like:

dim db as database
set db = dbengine.opendatabase ("path to new be db")
' relink tables here.
db.close
set db = nothing

HTH,
TC
 
Back
Top