Maintenance Recomendations -Compact?

[Touche'' Techie]

What are the maint. recs for an Access DB?

It seems that setting the compact on close is a good idea, but perhaps a bit
of overkill on a small DB (now only 10mb but 25-40mb est. in 2 years). Is
there added risk with the frequent compacting? Is the compact on close also a
repair or just compact?
It also seems the rec is to back-up and then compact from what I've
read....True?
I was hoping to program a form button to be a backup button for the DB, but
reading the posts I'm not sure that is a good idea or how to do it. Advice?

An ERP DB will be handed off to some senior citizens and they do not intend
to have regular techie check-ups so anything I can do to keep it running on
its own would be good. Obviously simple solutions like big buttons work best
too.
Thanks

 

[Linq Adams via AccessMonster.com]

By all accounts Compact on Close (and yes, it is actually Compact and Repair)
is a very bad idea! The general consensus is that compacting is a common
cause of corruption and therefore (as you've mentioned) should never be done
without first backing up your database!

The first reaction to corruption is to do a Compact and Repair, and once
again it should only be tried after a backup has been done. The C & R can
actually make later data recovery efforts more difficult or even impossible.

With the kind of growth you've mentioned and Access' 2 GB size limit I
wouldn't even worry about it. Even if you're running A97, which only has a 1
GB limit, it should be OK. The chances are that way before there's a problem,
size-wise, something else will need to be done to it, anyone coming in to
work on it will compact just as a matter of routine.

 

[Ron2006]

Besides that, Compact and repair on close will compact and repair the
FE mdb file and NOT the BE file which is what you would want to be
compacted since that is where the data is being stored.

Ron

 

[Larry Daugherty]

If by "maint. recs" you actually mean Maintenance Requirements, please
type it that way. We don't "text" and we're not big on abbreviations
nor jargon that lies far afield from Access. That allows us to
quickly understand your question and, just possibly, to address it.


Most likely, your database is still monolithic; meaning that you
haven't split it into FrontEnd and BackEnd. If that is true then
setting "Compact on Close" is a very good idea. The only overhead is
the time it takes; not much at all. That frequent Compact and Repair
will go a long ways toward preventing your database becoming corrupt
in the first place. Compact and Repair doesn't cause problems, it
solves those it can solve and reports those it can't. That early
warning might be sufficient that you can save all of your data and
design.

The important things are your design and your data. The size of
either is never the issue except when you lose it. The computer works
exactly as hard in the idle state as it does when doing meaningful
work. So, in addition to the above, take frequent backups of your
application. In this case you need to name and save the backups
separately so that you can step backward through backups in the event
of loss of data.

You can't backup the current database while it is running so the
button on a form question doesn't apply.

I was surprised to see that you intend to deliver a commercial
application. Yes, we senior citizens appreciate clear, understandable
buttons and other elements of the user interface. In my years of
developing applications, I've found that users of all ages do better
when things are clear and unambiguous at all levels.

Given that you intend to flog your application to other users then I
recommend that you go through the process of splitting the database.
There are instructions in Access. If you don't split it then
maintenance becomes a nightmare. There are only two reasons for not
splitting an Access database: 1) It is so badly done that there will
never be users who request additional features and there is no intent
to fix outright bugs. 2) It is so perfectly done that it has
anticipated all future needs and there are no bugs at all. I've seen
lots of #1s and absolutely no #2s.

Now, the sad news is that once you do split your database into a
FrontEnd (the Forms, Queries, Reports) and BackEnd (just the tables),
the Compact on Close feature is now useless to the Data in the tables
which is really the most important part of the User's installation.

To provide the required coverage you need to either program a solution
or to get one of the available solutions.

Good luck with your application.

HTH

 

[Tony Toews [MVP]]

It seems that setting the compact on close is a good idea, but perhaps a bit
of overkill on a small DB (now only 10mb but 25-40mb est. in 2 years).

In a split FE/BE environment compact on close will only perform on the
FE.

It's also not recommended as, occasionally when folder permissions
aren't set properly it won't compact or you end up with db1, db2, etc.
databases.

Is
there added risk with the frequent compacting? Is the compact on close also a
repair or just compact?

Both in A2000 and newer.

It also seems the rec is to back-up and then compact from what I've
read....True?
Correct.

I was hoping to program a form button to be a backup button for the DB, but
reading the posts I'm not sure that is a good idea or how to do it. Advice?

Yes, I do a rename to a different folder and the file name ends with
the date in yyyy-mm-dd format. Then I compact back.

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/

 

[Tony Toews [MVP]]

By all accounts Compact on Close (and yes, it is actually Compact and Repair)
is a very bad idea! The general consensus is that compacting is a common
cause of corruption and therefore (as you've mentioned) should never be done
without first backing up your database!

Well, not quite. I would not say that compacting is a common cause of
corruption. It is a occasional cause of trouble or irritation if the
folder permissions aren't set properly.

And yes, you should always do the compact and repair in such a fashion
that you have a backup.

The first reaction to corruption is to do a Compact and Repair, and once
again it should only be tried after a backup has been done. The C & R can
actually make later data recovery efforts more difficult or even impossible.
Agreed.

With the kind of growth you've mentioned and Access' 2 GB size limit I
wouldn't even worry about it. Even if you're running A97, which only has a 1
GB limit, it should be OK. The chances are that way before there's a problem,
size-wise, something else will need to be done to it, anyone coming in to
work on it will compact just as a matter of routine.

However compacting on a monthly or so basis is a good thing. More
often if there is a lot of updates and inserts. Lot meaning tens of
thousands of records.

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/

 

[Touche'' Techie]

I was surprised to see that you intend to deliver a commercial
application. Yes, we senior citizens appreciate clear, understandable
buttons and other elements of the user interface. In my years of
developing applications, I've found that users of all ages do better
when things are clear and unambiguous at all levels.

Surprised because of my level of expertise or because of the stability and
limitations of Access? I'm actually a 25 year + veteran to programming and
technology. I just never had reason to use Access until it was a requirement
for a client. I wanted them to buy a commercial product for less than my
fees, but some won't listen to reason. At least their data is out of Excel
and into Access so they can go commercial later.

Given that you intend to flog your application to other users then I
recommend that you go through the process of splitting the database.
There are instructions in Access. If you don't split it then
maintenance becomes a nightmare. There are only two reasons for not
splitting an Access database: 1) It is so badly done that there will
never be users who request additional features and there is no intent
to fix outright bugs. 2) It is so perfectly done that it has
anticipated all future needs and there are no bugs at all. I've seen
lots of #1s and absolutely no #2s.

I am not an expert in Access by any means, and from what I have read, it
seems it is prone to corruption. Are there really that many problems with
Access? From what I understand, it is good for single user use and it becomes
less than ideal with multiuser, network access use. It seems that it is then
that it is more likely to become corrupt. The ERP DB design is a good, clean
design but it is not split. Right now I have just hidden all toolbars etc. to
limit command accessibility. I intend to lock things down some more when it
is ‘done’ and improve some efficiencies… I’ll consider a split.

My concerns are that once they have all the features they want, they will
not have anyone look at or maintain the system until it breaks. So whatever I
can do to increase its lifespan is what I am looking for. I am in the habit
of creating products or environments that don’t fail…ever… and don’t expect
to change because of Access’ limitations.

And I also believe in developing simple and clean on the front so that
anyone, senior or not, can use it. This office just has the youngest checking
in at 68 so the situation is a little more highlighted than usual.

They actually already have the beta version and haven’t managed to break it
yet.
Sorry for being too chatty.

 

[Tony Toews [MVP]]

I am not an expert in Access by any means, and from what I have read, it
seems it is prone to corruption. Are there really that many problems with
Access? From what I understand, it is good for single user use and it becomes
less than ideal with multiuser, network access use. It seems that it is then
that it is more likely to become corrupt.

Absolutely Access is more prone to corrupt on a network. But then
it's next to impossible to corrupt on a single machine unless you
manage to hit the power switch at the exact millisecond that Access is
updating the database.

However what you are seeing are the, arguably relatively few, postings
from people who are having corruptions. One client had approximately
five corruptions in the seven or so years that I was doing work for
them. Two of those were because of a slightly flaky Terminal Server
system. The others we have no idea. But we also never lost any data.

And that was with 25 users on the system, some via a 10 mbps radio
link five miles away, others a few thousand miles away coming in via
Terminal Server, some on Access 97 and some on Access 2000.

There are basic things you should be careful about..

The ERP DB design is a good, clean
design but it is not split.

And that's one of them. You must split the database to reduce the
chance of corruptions. Access was not designed for users to share
objects other than tables.

Right now I have just hidden all toolbars etc. to
limit command accessibility. I intend to lock things down some more when it
is ‘done’ and improve some efficiencies… I’ll consider a split.

Are they using the system now? Then split it.

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

My concerns are that once they have all the features they want, they will
not have anyone look at or maintain the system until it breaks.

So once a week or month or so have the IT department make a copy of
the BE and compact it.

Or put in some code that automatically compacts it when the last user
exits the FE.

So whatever I
can do to increase its lifespan is what I am looking for. I am in the habit
of creating products or environments that don’t fail…ever… and don’t expect
to change because of Access’ limitations.

Not a lot of limitations and it's a great RAD database tool.

They actually already have the beta version and haven’t managed to break it
yet.

I actually don't give users betas. I give them enough for them to
start using the system as is. Then I add more functionality and let
them use it more. When working on site I'll sometimes update the FE
every few hours or every day at least. But then my Auto FE Updater
made that exceedingly convenient.

Sorry for being too chatty.

Nah, chatty is good.

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/

 

[Touche'' Techie]

Thanks to alll for all the great advice!
This was done in Access 2003.

So once a week or month or so have the IT department make a copy of
the BE and compact it.

There is no IT dept. for this place. No tech savy users. No tech support...
nothing. Using MS Word is an accomplishment.

I actually don't give users betas. I give them enough for them to
start using the system as is. Then I add more functionality and let
them use it more. When working on site I'll sometimes update the FE
every few hours or every day at least. But then my Auto FE Updater
made that exceedingly convenient.

They didn't get the system until it was fully functional but if was not
fully featured. But this project, as you may have guessed, was not your
normal project. They gave me no specs, no requirements, no guidelines or
needs. I was told to make an ERP system primarily for their inventory
tracking. Every time I tried to ask any questions to define the needs better
they literally ran away and said they knew nothing about computers and to
just make something that works. So the product was really my creative writing
on what I thought they may want to do and formulating it around what
commercial products deliver. They kept saying to not do anything fancy so
I've made a concious effort to try to make it look as simple as possible
while giving it the flexibility on the backside to do much more. I did add
some "fancy" features and reports as teasers to show the power of the product
and did quickly get requests like "that's great... is there any way it can do
this too?" I consider it a beta if I am still doing weekly feature updates or
don't feel that I have finished locking it down and finding all the ways they
can break it. I personally like to get a product to users before it is
completely done, usually to a small test group, because the end user is the
person who is most likely to break it or dislike a feature. It is a method
that generally results in a bit more work, but better client satisfaction (I
beleive).

I happen to also be a Six Sigma Master Black Belt too, so this "process" has
been rather painfull, knowing full well that no guidelines generally equals
failed delivery. There was a reason why no one would touch this job. I'll be
spending some time with them and do some simple business process training too
to try to get them going in the right direction. In all honesty, I'll
probably volunteer to drop in once and a while and check up on the solution
an do some maintenance.

Thanks again,

 

[Larry Daugherty]

Surprised because of the venue as much as anything. most often,
getting started with Access means getting started in other areas as
well. Welcome to the world of Access.

Tony has given you lots of great input regarding the care and feeding
of Access installations. He has amassed lots of Access lore on his
site. I believe it was also he who characterized Access as "the
canary in the coal mine". If there is a problem anywhere in the
network between the FrontEnd(s) and the BackEnd then it may first show
up as access corruption.

Access is multi-user right out of the box. Tony has characterized
some of its real world performance. It can be impressive. The
undeniable problem that persists with a "pure" Access/Jet installation
is that it's file/server versus client/server. That means that
potentially huge amounts of network traffic can be generated. OK on a
LAN but not on a WAN.

Another great site for Access lore is
www.mvps.org/access

HTH

 

[Touche'' Techie]

Thanks,

I did feel that getting started would be appropriate as the question seemed
very basic or beginner level and based on my inexperience with Access I know
it is more likely that I overlook some simple or basic thing that is very
Access specific which could hose the entire system... even though everything
else is 'perfect'. It also was a question that did not seem to fall into
other categories. Perhaps a maintenance or repair category would be good.

This system is installed on one computer, not even connected to a network,
and one user will be on at a time. But who knows what they may do in the
future....

I must say that Access can do more than I expected and I think I will play
with it some more after this to see what else it can do.

 

[Tony Toews [MVP]]

I must say that Access can do more than I expected and I think I will play
with it some more after this to see what else it can do.

Welcome to the dark side. <smile>

Seriously though there are a lot of IT and dev bigots who have seen
some utter cr*p in Access. And they've made assumptions about how
good it works.

I, of course, argue that it's better to have the cr*p in Access than
Excel or, and yes it has happened, tables in Word.

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/