Distributed Access app

[PatientCoder]

I just got through reading a very informative thread entitled "The
Next Step". I am in a similar situation and I am a bit nervous as
well especially if the popularity of my app grows rapidly. My
application is a standalone developed in Access 2007 and will run on
home computers (no network involved). The app is split into BE and
FE. I have a couple of specific questions:

1. The BE database does not have excessive bloat but I imagine that I
should still run the R/C maybe once a month. I am worried about power
outages or someone turning of the computer during a Repair/Compact.
Could that become a support nightmare?
2. Does it make sense to deploy without a Repair/Compact process
built-in?
3. Are there any best practices for Repair/Compact and for recovering
from Repair/Compact aborts?
4. I've seem statements in some forums about Access 2007 runtime
causing problems if older version of Access is installed on the local
machine. Any thoughts on this? I want to distribute the app in
Access 2007 only...

Any suggestions would be appreciated.

 

[chemo]

I just got through reading a very informative thread entitled "The
Next Step". I am in a similar situation and I am a bit nervous as
well especially if the popularity of my app grows rapidly. My
application is a standalone developed in Access 2007 and will run on
home computers (no network involved). The app is split into BE and
FE. I have a couple of specific questions:

1. The BE database does not have excessive bloat but I imagine that I
should still run the R/C maybe once a month. I am worried about power
outages or someone turning of the computer during a Repair/Compact.
Could that become a support nightmare?
2. Does it make sense to deploy without a Repair/Compact process
built-in?
3. Are there any best practices for Repair/Compact and for recovering
from Repair/Compact aborts?
4. I've seem statements in some forums about Access 2007 runtime
causing problems if older version of Access is installed on the local
machine. Any thoughts on this? I want to distribute the app in
Access 2007 only...

Any suggestions would be appreciated.

 

[david]

Every year might be a good time to compact/repair, unless
you have a design problem which is causing bloat. If you
are getting bloat, you have a design problem which can be
fixed.

Compact/repair is done to a new file, then the old file is
deleted, then the new file is renamed. Power outages or
computer outages are not a problem -- if it happens in the
middle of a compact, your old file is still there, the damaged
file is the new temp file which is not in use.

On the other hand, a system which includes deleting the old
file sometimes fails for other reasons, and it's not something
to do unless you have a proper backup.

It makes no sense to deploy with a compact/repair built in.
It's not something users should be doing.

Recover from compact/repair aborts by restoring to backup.

Access causes problems to naive users if installed on a machine
with other Access versions. Developers often have multiple versions
installed, but we aren't naive users. Best practice is to not install
the runtime if Access is already installed. Other options are to
leave it to the user to buy Access, provide multiple versions
of your program, provide multiple versions of the runtime for
the user to install, not support users who have different versions
of Access, provide help and support for users with multiple
versions installed... One common approach is to provide your
program in your minimum supported version, say 2000, and
only install the runtime if no version of Access is already installed.

(david)

 

[banem2]

I just got through reading a very informative thread entitled "The
Next Step". I am in a similar situation and I am a bit nervous as
well especially if the popularity of my app grows rapidly. My
application is a standalone developed in Access 2007 and will run on
home computers (no network involved). The app is split into BE and
FE. I have a couple of specific questions:

1. The BE database does not have excessive bloat but I imagine that I
should still run the R/C maybe once a month. I am worried about power
outages or someone turning of the computer during a Repair/Compact.
Could that become a support nightmare?
2. Does it make sense to deploy without a Repair/Compact process
built-in?
3. Are there any best practices for Repair/Compact and for recovering
from Repair/Compact aborts?
4. I've seem statements in some forums about Access 2007 runtime
causing problems if older version of Access is installed on the local
machine. Any thoughts on this? I want to distribute the app in
Access 2007 only...

Any suggestions would be appreciated.

Hello,

Access, during compact & repair, creates copy of database and if this
process succeed it replace original DB with compacted copy. There can
be an issue if the power fails during copy which may damage DB. If you
need to make sure that power fail won't hurt DB you can create copy of
DB before compacting. There are many advices how to copy open database
here. Another solution is to add UPS which are not so expensive these
days but still it cannot prevent PC power supply to fail. So, maybe
the best advice is to combine these two.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[PatientCoder]

Hello,

Access, during compact & repair, creates copy of database and if this
process succeed it replace original DB with compacted copy. There can
be an issue if the power fails during copy which may damage DB. If you
need to make sure that power fail won't hurt DB you can create copy of
DB before compacting. There are many advices how to copy open database
here. Another solution is to add UPS which are not so expensive these
days but still it cannot prevent PC power supply to fail. So, maybe
the best advice is to combine these two.

Regards,
Branislav Mihaljev
Microsoft Access MVP- Hide quoted text -

- Show quoted text -

Thanks for the reply. The problem is that my app will be distributed
to many different people whom I do not know. I have logic that will
repair and compact behind the scenes but I was curious to know what
other people do to address the issue of power supply failure. Do I
just assume that the BE backup that was created before the start of
the compact was good and restore it? What about the frontend? Do I
just have them go to my website and get another copy of the FE?

 

[david]

PatientCoder wrote:

Access, during compact & repair, creates copy of database and if this
process succeed it replace original DB with compacted copy. There can
be an issue if the power fails during copy which may damage DB. If you
need to make sure that power fail won't hurt DB you can create copy of
DB before compacting. There are many advices how to copy open database
here. Another solution is to add UPS which are not so expensive these
days but still it cannot prevent PC power supply to fail. So, maybe
the best advice is to combine these two.

Regards,
Branislav Mihaljev
Microsoft Access MVP

If the power fails during 'copy' there is a damaged 'copy' with a different
name. Your original database is untouched.

After the new database is compacted and repaired, the original database
is deleted. Now you have a new database with the wrong name.

After the old database is deleted, the new database is renamed to
the original name.

Power failure cannot damage a database during compact/repair.

Power failure might possibly damage a database while it is being used.
You can't use a database while it is being compacted and repaired.

'Compact and repair' is the one time that a database is safe from damage
from power failure.

(david)

 

[Tony Toews [MVP]]

1. The BE database does not have excessive bloat but I imagine that I
should still run the R/C maybe once a month. I am worried about power
outages or someone turning of the computer during a Repair/Compact.
Could that become a support nightmare?
No.

2. Does it make sense to deploy without a Repair/Compact process
built-in?
No.

3. Are there any best practices for Repair/Compact and for recovering
from Repair/Compact aborts?

When the user exits the FE attempt to rename the backend MDB
preferably with todays date in the name in yyyy-mm-dd format. Ensure
you close all bound forms, including hidden forms, and reports before
doing this. If you get an error message, oops, its busy so don't
bother. If it is successful then compact it back.

See my Backup, do you trust the users or sysadmins? tips page for more
info.
http://www.granite.ab.ca/access/backup.htm

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/

 

[PatientCoder]

When the user exits the FE attempt to rename the backend MDB
preferably with todays date in the name in yyyy-mm-dd format.  Ensure
you close all bound forms, including hidden forms, and reports before
doing this.   If you get an error message, oops, its busy so don't
bother.  If it is successful then compact it back.  

See my Backup, do you trust the users or sysadmins? tips page for more
info.http://www.granite.ab.ca/access/backup.htm

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 athttp://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Thanks for the responses, the feedback is very insightful.
Tony: since this software is geared for home use, I don't expect there
to be an "admin". I am also not expecting highly technical users. So
everything would have to be behinde the scenes in vba procedures.
Although I am comfortable implement a repair & compact and backup, it
is still unclear the best approach to building some kind of automatic
recovery or is that even recommended? In other words, should a
recovery be designed to automatically choose the most recent backup if
the BE database is damaged?

 

[Tony Toews [MVP]]

Thanks for the responses, the feedback is very insightful.
Tony: since this software is geared for home use, I don't expect there
to be an "admin". I am also not expecting highly technical users. So
everything would have to be behinde the scenes in vba procedures.
Although I am comfortable implement a repair & compact and backup, it
is still unclear the best approach to building some kind of automatic
recovery or is that even recommended? In other words, should a
recovery be designed to automatically choose the most recent backup if
the BE database is damaged?

I have about 20 or so clients using software on stand alone systems
for the past ten years or so. They've never had corruptions. So I
wouldn't automatically put any such code.

If that's ever required some time spent with the user in renaming and
moving files using Windows Explorer would work.

However I also put in a button to backup the MDB to a removable memory
stick. (Used to be a floppy drive. <smile>)

And I tell them about multiple generations of backups and keeping
several copies of the backup offsite in another building, etc, etc.
But they don't get that part much usually.

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/

 

[PatientCoder]

I have about 20 or so clients using software on stand alone systems
for the past ten years or so. They've never had corruptions.   So I
wouldn't automatically put any such code.

If that's ever required some time spent with the user in renaming and
moving files using Windows Explorer would work.

However I also put in a button to backup the MDB to a removable memory
stick. (Used to be a floppy drive.  <smile>)    

And I tell them about multiple generations of backups and keeping
several copies of the backup offsite in another building, etc, etc.
But they don't get that part much usually.

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 athttp://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

You have answered my questions and now I get to put it all into
practice. Thanks so much to everyone who took the time to respond.