Update BE Split Databases

I've been reading how to split a database into a FE/BE scenario which sounds
great! I understand how easy it would be for me to update the front end,
let's say, on my local machine then send out the updated version to all users
(with the proper links of course). But does anyone have a good idea of how
to make changes to the back end of the database without making all users exit
out of their front end copy? Let's say I wanted to change or add a field to
a table or add a completely new table. Access won't let me update the back
end unless all users are "out". I thought about copying the BE and doing the
changes, but while I would be updating the BE, the users would be constantly
adding new records, which wouldn't be in my working copy if I copy my updated
BE over the old one. My first guess is to make sure I have all my tables set
up fairly well before deployment. One question/comment about FE changes-I
assume that once I do an update on the FE, I could very simply email the new
FE to all users (with correct UNC links) and instruct them to save and
overwrite the old version.

But does anyone have a good idea of how
to make changes to the back end of the database without making all users
exit
out of their front end copy?

Well, even before, when things where un-split, you could not modify the
application. (and, before you could not even work on a "copy" of the
forms+code part). So, you are a lot better off.

There is several approaches I use, and which one depends on the client, and
the particular situation.

The first case is that I ask everyone to exit the application (or, if off
site, have them email me the applications, and tell them not to work until I
return to them the new updated back end). However, this "time" lag is
usually quite small in most cases. The reason for this is the following:

When I work on a copy of the FE, I also of course grab a copy of the BE. If
I add a new field to a existing table, then I will open up a small text
document (or a word document), and make a note of this change

table : Contacts, add new field "CityLocation"

newtable: tblLocatons
(set relation from tblContacts to tblLocations)

So, as I update, and add new fields, or tables, I make a little list of
notes. Usually, that list is only going to be 3 or perhaps 5 changes. When I
am 100% happy with the new fields, ad changes, I THEN TELL the client the
update is ready, and I need a copy of their back end. They zip/email it to
me, and then I simply open up that "list" of changes, and work on the new
back end..

Hum...ok...oh yes, I added that new field...ok..done
Hum, a new table...ah, I just import that table from the development back
end ...ok done...

When I am all happy, I then ship back the new FE, and the new BE. The "down
time" is not going to be too large here, since I ONLY keep them shut down
for the time it takes me to "update" that back end, and that is typically
less then 15 minutes.

It is obvious by now that you should write, and implement some type of
re-link code that lets you "specify" the new location to link to, or at
least something that checks the link, and pops up the 'dialog' box to do
this. You can use the linked table manager, but from a customer service
point of view, you really should have something a bit better...

So, the above is a typical solution.

A 2nd approach is to development above way, but then write CODE to update
the changes you made. For new tables, in place of a whole whack of "ddl", I
simply include the new table in the front end, and have code to "transfer"
this table to the back end, and am done (the link is already there!!!). For
new fields, most of the time I can get away simply issuing some "alter
table" commands in sql, and I got new fields. So, you can use some code to
"update" the back end, but that code can be quite delicate to write. If you
got 5+ clients in another city in different locations running the software
and they are going to get the update, then this is the approach I use. (I
don't want to call up, and deal with 5+ clients etc...). Note that in this
case, all users have to exit anyway, and the "first" user to run will run
this "update" code. (oh, yea...in that on reocrd "config" table in the
FE...I also got a version number, and also a table in the BE has that
number).

One question/comment about FE changes-I
assume that once I do an update on the FE, I could very simply email the
new
FE to all users (with correct UNC links) and instruct them to save and
overwrite the old version.

Yes, but do note that you can't set the UNC links unless you got the same
path names on your computer. I do often use virtual pc, and do make the same
path name (thus, you *can* send pre-linked talbes...and you should if you
can). I also got a conig table with one record in the FE, and in there I can
put in a path name to "link to " on startup (this only runs once, and then
another field is set to indicate this was done).

Further, in place of a whole bunch of instructions, why not use winzip
(www.winzip.com), and create a self extracting .exe file that copies the
file to the correct place. And, if you don't have a copy registered copy of
winzip to make self extracting .exe, you can still make a zip file with the
trial edition. I used a registered copy of winzip for years to do this for
clients. And, I throw the copy up on a web site. (lots of email clients
don't allow .exe files anymore, and lots more don't even allow mdb
extensions).

Now, I have a much more polished approach, as I use the free "Inno"
installer, and that does a zip + allows things like shortcuts etc. So, the
best approach is to adopt and start using inno installer, and then you don't
need to "send" clients some instructions that they can mess up, but simply a
file that does all the work...

At the end of the day, you are still miles ahead in terms of flexibility.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

So aside from the coding possibilities, whenever I need to update the BE, all
users need to exit. As far as the Zip idea, sounds good, but our company has
all .zip files deleted from emails automatically.

"Albert D.Kallal" wrote:

But does anyone have a good idea of how
to make changes to the back end of the database without making all users
exit
out of their front end copy?

Well, even before, when things where un-split, you could not modify the
application. (and, before you could not even work on a "copy" of the
forms+code part). So, you are a lot better off.

There is several approaches I use, and which one depends on the client, and
the particular situation.

The first case is that I ask everyone to exit the application (or, if off
site, have them email me the applications, and tell them not to work until I
return to them the new updated back end). However, this "time" lag is
usually quite small in most cases. The reason for this is the following:

When I work on a copy of the FE, I also of course grab a copy of the BE. If
I add a new field to a existing table, then I will open up a small text
document (or a word document), and make a note of this change

table : Contacts, add new field "CityLocation"

newtable: tblLocatons
(set relation from tblContacts to tblLocations)

So, as I update, and add new fields, or tables, I make a little list of
notes. Usually, that list is only going to be 3 or perhaps 5 changes. When I
am 100% happy with the new fields, ad changes, I THEN TELL the client the
update is ready, and I need a copy of their back end. They zip/email it to
me, and then I simply open up that "list" of changes, and work on the new
back end..

Hum...ok...oh yes, I added that new field...ok..done
Hum, a new table...ah, I just import that table from the development back
end ...ok done...

When I am all happy, I then ship back the new FE, and the new BE. The "down
time" is not going to be too large here, since I ONLY keep them shut down
for the time it takes me to "update" that back end, and that is typically
less then 15 minutes.

It is obvious by now that you should write, and implement some type of
re-link code that lets you "specify" the new location to link to, or at
least something that checks the link, and pops up the 'dialog' box to do
this. You can use the linked table manager, but from a customer service
point of view, you really should have something a bit better...

So, the above is a typical solution.

A 2nd approach is to development above way, but then write CODE to update
the changes you made. For new tables, in place of a whole whack of "ddl", I
simply include the new table in the front end, and have code to "transfer"
this table to the back end, and am done (the link is already there!!!). For
new fields, most of the time I can get away simply issuing some "alter
table" commands in sql, and I got new fields. So, you can use some code to
"update" the back end, but that code can be quite delicate to write. If you
got 5+ clients in another city in different locations running the software
and they are going to get the update, then this is the approach I use. (I
don't want to call up, and deal with 5+ clients etc...). Note that in this
case, all users have to exit anyway, and the "first" user to run will run
this "update" code. (oh, yea...in that on reocrd "config" table in the
FE...I also got a version number, and also a table in the BE has that
number).

One question/comment about FE changes-I
assume that once I do an update on the FE, I could very simply email the
new
FE to all users (with correct UNC links) and instruct them to save and
overwrite the old version.

Yes, but do note that you can't set the UNC links unless you got the same
path names on your computer. I do often use virtual pc, and do make the same
path name (thus, you *can* send pre-linked talbes...and you should if you
can). I also got a conig table with one record in the FE, and in there I can
put in a path name to "link to " on startup (this only runs once, and then
another field is set to indicate this was done).

Further, in place of a whole bunch of instructions, why not use winzip
(www.winzip.com), and create a self extracting .exe file that copies the
file to the correct place. And, if you don't have a copy registered copy of
winzip to make self extracting .exe, you can still make a zip file with the
trial edition. I used a registered copy of winzip for years to do this for
clients. And, I throw the copy up on a web site. (lots of email clients
don't allow .exe files anymore, and lots more don't even allow mdb
extensions).

Now, I have a much more polished approach, as I use the free "Inno"
installer, and that does a zip + allows things like shortcuts etc. So, the
best approach is to adopt and start using inno installer, and then you don't
need to "send" clients some instructions that they can mess up, but simply a
file that does all the work...

At the end of the day, you are still miles ahead in terms of flexibility.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Another way is to "Replicate the backend" and you will end up with a Design
Master and one or more replicas-these are the files the users actually
connect to. You can make your design changes to the tables in the Design
Master and then do a one-way synchronization to push the changes out to the
production back-end. This approach can be very powerful, AND very
complicated, so if this sounds like it might be useful, do your homework
first, read the Replication FAQ from the MSKB and visit the
www.trigeminal.com site for more info about replication.

"bondtk" wrote in message
...
So aside from the coding possibilities, whenever I need to update the BE,
all
users need to exit. As far as the Zip idea, sounds good, but our company
has
all .zip files deleted from emails automatically.

"Albert D.Kallal" wrote:

But does anyone have a good idea of how
to make changes to the back end of the database without making all
users
exit
out of their front end copy?

Well, even before, when things where un-split, you could not modify the
application. (and, before you could not even work on a "copy" of the
forms+code part). So, you are a lot better off.

There is several approaches I use, and which one depends on the client,
and
the particular situation.

The first case is that I ask everyone to exit the application (or, if off
site, have them email me the applications, and tell them not to work
until I
return to them the new updated back end). However, this "time" lag is
usually quite small in most cases. The reason for this is the following:

When I work on a copy of the FE, I also of course grab a copy of the BE.
If
I add a new field to a existing table, then I will open up a small text
document (or a word document), and make a note of this change

table : Contacts, add new field "CityLocation"

newtable: tblLocatons
(set relation from tblContacts to tblLocations)

So, as I update, and add new fields, or tables, I make a little list of
notes. Usually, that list is only going to be 3 or perhaps 5 changes.
When I
am 100% happy with the new fields, ad changes, I THEN TELL the client the
update is ready, and I need a copy of their back end. They zip/email it
to
me, and then I simply open up that "list" of changes, and work on the new
back end..

Hum...ok...oh yes, I added that new field...ok..done
Hum, a new table...ah, I just import that table from the development back
end ...ok done...

When I am all happy, I then ship back the new FE, and the new BE. The
"down
time" is not going to be too large here, since I ONLY keep them shut down
for the time it takes me to "update" that back end, and that is typically
less then 15 minutes.

It is obvious by now that you should write, and implement some type of
re-link code that lets you "specify" the new location to link to, or at
least something that checks the link, and pops up the 'dialog' box to do
this. You can use the linked table manager, but from a customer service
point of view, you really should have something a bit better...

So, the above is a typical solution.

A 2nd approach is to development above way, but then write CODE to update
the changes you made. For new tables, in place of a whole whack of "ddl",
I
simply include the new table in the front end, and have code to
"transfer"
this table to the back end, and am done (the link is already there!!!).
For
new fields, most of the time I can get away simply issuing some "alter
table" commands in sql, and I got new fields. So, you can use some code
to
"update" the back end, but that code can be quite delicate to write. If
you
got 5+ clients in another city in different locations running the
software
and they are going to get the update, then this is the approach I use. (I
don't want to call up, and deal with 5+ clients etc...). Note that in
this
case, all users have to exit anyway, and the "first" user to run will run
this "update" code. (oh, yea...in that on reocrd "config" table in the
FE...I also got a version number, and also a table in the BE has that
number).

One question/comment about FE changes-I
assume that once I do an update on the FE, I could very simply email
the
new
FE to all users (with correct UNC links) and instruct them to save and
overwrite the old version.

Yes, but do note that you can't set the UNC links unless you got the same
path names on your computer. I do often use virtual pc, and do make the
same
path name (thus, you *can* send pre-linked talbes...and you should if you
can). I also got a conig table with one record in the FE, and in there I
can
put in a path name to "link to " on startup (this only runs once, and
then
another field is set to indicate this was done).

Further, in place of a whole bunch of instructions, why not use winzip
(www.winzip.com), and create a self extracting .exe file that copies the
file to the correct place. And, if you don't have a copy registered copy
of
winzip to make self extracting .exe, you can still make a zip file with
the
trial edition. I used a registered copy of winzip for years to do this
for
clients. And, I throw the copy up on a web site. (lots of email clients
don't allow .exe files anymore, and lots more don't even allow mdb
extensions).

Now, I have a much more polished approach, as I use the free "Inno"
installer, and that does a zip + allows things like shortcuts etc. So,
the
best approach is to adopt and start using inno installer, and then you
don't
need to "send" clients some instructions that they can mess up, but
simply a
file that does all the work...

At the end of the day, you are still miles ahead in terms of flexibility.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

If you can use a third-party product, take a look at FMS's Total Access
Admin. http://www.fmsinc.com/products/Admin/index.asp

This product lets you alert users so that you can make the changes you need
to the BE.

There are only a few components to install into your application, with a
minimum amount of modifications to those components.

I've recently used it at a client site and their local part-time database
admin is thrilled with the ablity to find out who is in the database, send
the notice that it is time to log off, and have the product force users out
if they left their workstations on overnight.

Also - and I found this out first-hand - FMS provides really top-notch
service and technical support!

Larry Kahm
Heliotropic Systems, Inc.

"bondtk" wrote in message
news
I've been reading how to split a database into a FE/BE scenario which
sounds
great! I understand how easy it would be for me to update the front end,
let's say, on my local machine then send out the updated version to all
users
(with the proper links of course). But does anyone have a good idea of
how
to make changes to the back end of the database without making all users
exit
out of their front end copy? Let's say I wanted to change or add a field
to
a table or add a completely new table. Access won't let me update the back
end unless all users are "out". I thought about copying the BE and doing
the
changes, but while I would be updating the BE, the users would be
constantly
adding new records, which wouldn't be in my working copy if I copy my
updated
BE over the old one. My first guess is to make sure I have all my tables
set
up fairly well before deployment. One question/comment about FE changes-I
assume that once I do an update on the FE, I could very simply email the
new
FE to all users (with correct UNC links) and instruct them to save and
overwrite the old version.