Select BE when DB opens

[ant1983]

Hiya,

I have two different back-ends; my back-end containing the live data and
then a dummy BE that i use to when working on the system so as to ensure i
dont change the live data.

Firstly; is this the correct way of doing it?
Secondly; i find it a drag to switch between the two BE's each time i finish
developing and deploy the FE's to the different machines so would like this
"automated".

I was thinking of a pop-up when opening the database prompting you to select
a BE from a drop-down box.

Any suggestions how to do this or alternatively better idea?

Ta!

 

[Daniel Pineault]

If your db is secured you could use the CurrentUser() and only make such an
option appear for yourself. I definitely would not give this type of
confusion to your end users. Do you have end-users or is it only for
yourself? Anyways,...

You could create a form with a combo box listing the 2 back-end file names
and a button to initialize the db. Have it launch at startup as part of an
autoexec macro and use the code found at
http://www.mvps.org/access/tables/tbl0009.htm to relink your db as necessary
(you will need to mod the code slightly you meet your needs but 90%+ of the
work is already done for you)
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

 

[pddxxx]

Our TableLinker freeware might help: http://www.peterssoftware.com/tlk.htm

With it you can easily relink your tables to another db. See the
"Connect Tables to Access Database ..." examples on the main menu.

Hope this helps,

Peter De Baets
http://www.peterssoftware.com

 

[Albert D. Kallal]

Hiya,

I have two different back-ends; my back-end containing the live data and
then a dummy BE that i use to when working on the system so as to ensure i
dont change the live data.

Firstly; is this the correct way of doing it?

Yes, this is how virtual in all of the seasoned developers do this.

Secondly; i find it a drag to switch between the two BE's each time i
finish
developing and deploy the FE's to the different machines so would like
this
"automated".

the general approach that most mistaken this case, is we the simply link the
database to the back end, converted to a mde, and then simply distribute
that all of the desktops. in this type of scenario, I see little if any
reason to re-lnk. if you use UNC path names to the server, then you'll not
have to worry about drive mapping on each individual's computer.

Yu can use code here to re-link.

http://www.mvps.org/access/tables/tbl0009.htm

You'll find that most of us developers have set up some type of re-link
system (based on the above code). but for some of my applications, I
actually assume there'll be a text file in the same directory that I can
open and read the location of the backend currently in use. You really only
need this text file approach if you're going to be deploying applications to
people on a network or location that you'll never physically have access to.

The best suggestion is to pre-link the front end before you deploy it to the
users machines. However, having said this, virtually all developers have had
to write some type of re-link system for their software. This is more
importantly so if you're going to deploy using the runtime, as then they
won't have the wizards and MS access interface available to do the
re-linking anyway...

I was thinking of a pop-up when opening the database prompting you to
select

a BE from a drop-down box.

Actually, that's too complicated for a lot of my users, and they'll always
pick the wrong one. I would take this choice away from them, and have my
code automatically re-link, or simply pre-link the database before you
distributed to the users.

 

[Steve Sanford]

I do the same thing. I have a test BE and two production BEs (the owner wants
each client in separate BEs). I also use two computers: work and home.

At first I had a form with buttons that were hadr coded to relink the
different BEs, but moving between work and home caused me problems.

Now I have one button on the form that opens the OPEN FILE dialog, that
allows me to select any location for any (mdb) file (like the "Linked Table
Manager" command in Access). And I have a large text box that shows the
current BE that each table is linked to.

Then I added a text box on the main form to show which BE I am linked to.

 

[Frank]

Here's another idea.

You should never give your operators a .mdb file (your source code) ... you
should give them a compiled (faster, smaller) .mde file.

With the suggested research you'l find simple code to un-link tables. Put
that in a "developer's tools" module in your database and run it manually
every time you publish a new version.

In your .mdb, in code which runs as the user logs on, put a function to link
to the back-end tables (others have pointed you to where you'll find code to
use). The code should look for an "IsMDE" property of the database (with a
little further research you'll find out about this property of an Access
database). If "IsMDE" is True (that is the database IS a .mde file) link to
the live data otherise if "IsMDE" is False (the database is NOT a .mde file)
link to your test data.

Cheers ...

 

[Tony Toews [MVP]]

I have two different back-ends; my back-end containing the live data and
then a dummy BE that i use to when working on the system so as to ensure i
dont change the live data.

Firstly; is this the correct way of doing it?

Yes, I do that all the time myself. You've got some good answers as
to code.

However I would suggest that you have a very obvious method of
determining if you are in the live database. For example I've chosen
to make the background colour of the main menu a deep red when running
code from an MDB, rather than an MDE, and the connect property shows
that the tables are linked to a network drive. I've also chosen to
display the Connect string at all times on the main menu when running
from an MDB as a double check.

Trust me on this one! <smile>

I once accidentally deleted 10,000 records from a table. I forgot the
WHERE clause when deleting what was supposed to be one record.
Thankfully it was a test database. Other times I've almost made
updates to live data and then decided to check that menu.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

if you used ADP; it would be _QUITE_ simple to do this-- you'd just
need to change either:

a) server/instance name
b) database name.

either way-- it wouldn't require any code in SQL Server/ ADP.

so which is it-- a kludge? a bunch of custom coding?
or presto-chango-- it already works like you want?

-Aaron

 

[Tony Toews [MVP]]

if you used ADP;

However

1) ADPs have their own quirks. Many quirks
2) ADPs have not been enhanced in A2007. If you persist in stating
such please post URLs.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/