Switching 1 fe between 3 be is this possible?

[Guest]

Here is the scenario I had 1be with 3 different fe one customized for each
department. Do to a power struggle within management each manager wanted
their database separate. I have quite a few users that use all 3 record
sources, and are now using 3 different database. I am asking this to before
i let the users know they will have to stick with the 3 separate database.
My questions is it possible to link to all the b/e and have a combo box on
form at startup that will give them the option to select which record source
they would like to connect or have a toggle button or option group on the
main from that will allow them to select which source to connect?

 

[Joseph Meehan]

Here is the scenario I had 1be with 3 different fe one customized for
each department. Do to a power struggle within management each
manager wanted their database separate. I have quite a few users
that use all 3 record sources, and are now using 3 different
database. I am asking this to before i let the users know they will
have to stick with the 3 separate database. My questions is it
possible to link to all the b/e and have a combo box on form at
startup that will give them the option to select which record source
they would like to connect or have a toggle button or option group on
the main from that will allow them to select which source to connect?

I am sorry. I am a bit confused. What exactly do the managers want?

Do they want their own database so their data can not be edited by
someone from another department? Do they want to hide their data from
other departments?

I don't see any advantage of linking backends. That would work the same
as having a single backend.

Using user level security you can control what any one user can see or
edit. Assuming the different departments share data you don't want to split
up that data. Splitting it up will only assure that in a short time it will
no longer be the same data. Updates to addresses phone numbers names of
people and where they work etc all be out of sync in no time.

 

[Jeff Boyce]

Raj

There would be no reason why you couldn't link to the tables in 10 backends,
if that were needed.

However, if each backend is an identical copy (structure), with overlapping
data (all three departments use the same list of customers), then you could
consider creating a single database, with one additional column in whatever
appropriate tables, indicating which department the data belongs to.

Just a thought...

Jeff Boyce
<Access MVP>

 

[George Nicholson]

is it possible to link to all the b/e and have a combo box on

form at startup that will give them the option to select which record
source
they would like to connect or have a toggle button or option group on the
main from that will allow them to select which source to connect?

Yes

http://www.mvps.org/access/tables/tbl0009.htm
contains code that relinks tables when a db opens. Consider it a
springboard for creating something that asks "which of 3 possible data
sources do you want to link to?".

In addition to using a variation of this code for an automatic link check on
opening, I have also used it as the basis for an "on demand" relink in most
of my apps, allowing the user to specify a different backend (useful while
in design).

HTH,

 

[Guest]

Joseph,
Sorry, that i confused you and after rethinking the matter it still would
not work for this situation. What it comes down to is a power struggle
between the managers over who is going to have control over the database, a
very childish and unprofessional situation and the users are caught in the
middle. I think I have come up with a solution for this problem, Thank you
for the reply.
But, I am still wondering if it is possible to have one frontend and 2
backend and select what recordsouce you want to connect: Let say I have a
backend that i use for production data and one for test data and when i open
my database I could select which to connect production or test. This is
something we have on one of out corporate systems and i was wondering if it
could be done with Access.
Thanks Raj

 

[Guest]

Jeff,
Excellent ideal!Thanks you..
But, I am still wondering if it is possible to have one frontend and 2
backend and select what recordsouce you want to connect: Let say I have a
backend that i use for producation data and one for test data and when i open
my database I could select which to connet production or test. This is
something we have on one of out corporate systems and i was wondering if it
could be done with Access.
Thanks
Raj

 

[Guest]

George,
THANK YOU!!!!

Yes

http://www.mvps.org/access/tables/tbl0009.htm
contains code that relinks tables when a db opens. Consider it a
springboard for creating something that asks "which of 3 possible data
sources do you want to link to?".

In addition to using a variation of this code for an automatic link check on
opening, I have also used it as the basis for an "on demand" relink in most
of my apps, allowing the user to specify a different backend (useful while
in design).

HTH,

 

[Joseph Meehan]

Joseph,
Sorry, that i confused you and after rethinking the matter it still
would not work for this situation. What it comes down to is a power
struggle between the managers over who is going to have control over
the database, a very childish and unprofessional situation and the
users are caught in the middle. I think I have come up with a
solution for this problem, Thank you for the reply.
But, I am still wondering if it is possible to have one frontend and 2
backend and select what recordsouce you want to connect: Let say I
have a backend that i use for production data and one for test data
and when i open my database I could select which to connect
production or test. This is something we have on one of out
corporate systems and i was wondering if it could be done with Access.
Thanks Raj

I would normally suggest that you have only one database for both test
and production data. Depending on the situation you may have it all in a
single table with a binary field defining which it is or two individual
tables.

However linking to both databases should do as you ask.

 

[Joseph Meehan]

Jeff,
Excellent ideal!Thanks you..
But, I am still wondering if it is possible to have one frontend and 2
backend and select what recordsouce you want to connect: Let say I
have a backend that i use for producation data and one for test data
and when i open my database I could select which to connet production
or test. This is something we have on one of out corporate systems
and i was wondering if it could be done with Access.
Thanks
Raj

You can link to both at the same time and they will appear as two
tables. Just use the one you want.

However it still sounds like the whole thing should be in a single back
end.

 

[John Vinson]

But, I am still wondering if it is possible to have one frontend and 2
backend and select what recordsouce you want to connect: Let say I have a
backend that i use for production data and one for test data and when i open
my database I could select which to connect production or test. This is
something we have on one of out corporate systems and i was wondering if it
could be done with Access.

Yes. What you would need to do is get one of the many "relinking" VBA
routines and adapt it for the purpose. Essentially, it would loop
through the tabledef objects, resetting their Connect strings to the
selected backend.

See http://www.mvps.org/access/tables/tbl0009.htm for Dev Ashish's
relinking software; you'll need to adapt it but it will give you the
bulk of the code you need.

John W. Vinson[MVP]