Compact and Repair & Backup Database

  • Thread starter Thread starter Té
  • Start date Start date
T

Té

What is the purpose of compact and repair? How exactly does it minimize
errors and reduce the file size?

The backup database, when you make changes to the original file, does the
backup update also?
 
Té said:
What is the purpose of compact and repair? How exactly does it minimize
errors and reduce the file size?
Click to expand...

When an access file is in use, it can't recover or delete free space.

Think of access as being a like word document, but SEVERAL people can use it
at the same time. If you were to delete a row (one line of text) in your
word document, then everything would move up.

So, for reason of performance record deletes don't actually recover disk
space. The deleted data is left "in place" in the file. (not to split hairs,
but the data is in the file, but you have no way of really seeing that
deleted data anymore).

It turns out that the same occurs for when you modify a form in design mode.
Ms-access leaves a copy of the old form, and saves the edited form by
expanding the database file (it can't delete the old one just like MS access
is unable to delete old data to fill up those holes). This means during the
day when you're doing a lot of developing a lot of modifications to forms,
you'll find that the application will grow in size VERY much. The common
term used in these newsgroups is the file will "bloat" in size very quickly.

The result of the above information understanding have how MS access works
is the following:

In your software designs , you want to avoid the use tempting tables.
Often, it is tempting for ease of reporting to send data out to a temporary
table, and then run the report on that table. However, since when you add
records to that table, the file size will grow, but when you delete records
(and even the table), the disk space and file size will NOT be recovered
until you do a compact and repair.

So, if you don't want your file size to grow like crazy, then as overall
design ideal, you thus want to avoid designs and coding practices that
create temporary tables or designs that copy/write out data and delete the
old data for no good reason at all. The less temporary tables and less
deleting of records means that your have less file growth. And, less file
growth will thus reduce the need to compact and repair on a frequent bases.

Ms-access works this way to keep performance high. If I have for example
100,000 records in a table, and you deleted the 1st record, then the moving
down the 99,999 records to fill that hole (gap) on the ms-access file would
take a LONG TIME to fill. And, during that move operation, no one else could
use the data file at the same time. (became the location of records is
changing inside of that mdb file). So, during a compact and repair
operation, no one else can be using the data file. Thus, compact and repair
is often scheduled to be done during the evening, or off hours when the
users of the application will not be impacted.

The backup database, when you make changes to the original file, does the
backup update also?
Click to expand...

There is no backup database in ms-access. So, you have one file. Because
ms-access does not have a recovery file, then you should backup the data on
a daily basis. So, the answer is no, the backup does not get updated because
there is no backup file.

You might want to recheck or try to ascertain what this other backup file
you're talking about is? MS access does not create them for you
automatically or during regular use. The moral of this lesson is simply put,
you must have a frequent backup plan here. Lkely on a daily basis is the way
to go here. If you don't have backups of your data, then you're asking for
trouble with MS access.
 
Té said:
What is the purpose of compact and repair? How exactly does it minimize
errors and reduce the file size?
Click to expand...

When an access file is in use, it can't recover or delete free space.

Think of access as being a like word document, but SEVERAL people can use it
at the same time. If you were to delete a row (one line of text) in your
word document, then everything would move up.

So, for reason of performance record deletes don't actually recover disk
space. The deleted data is left "in place" in the file. (not to split hairs,
but the data is in the file, but you have no way of really seeing that
deleted data anymore).

It turns out that the same occurs for when you modify a form in design mode.
Ms-access leaves a copy of the old form, and saves the edited form by
expanding the database file (it can't delete the old one just like MS access
is unable to delete old data to fill up those holes). This means during the
day when you're doing a lot of developing a lot of modifications to forms,
you'll find that the application will grow in size VERY much. The common
term used in these newsgroups is the file will "bloat" in size very quickly.

The result of the above information understanding have how MS access works
is the following:

In your software designs , you want to avoid the use tempting tables.
Often, it is tempting for ease of reporting to send data out to a temporary
table, and then run the report on that table. However, since when you add
records to that table, the file size will grow, but when you delete records
(and even the table), the disk space and file size will NOT be recovered
until you do a compact and repair.

So, if you don't want your file size to grow like crazy, then as overall
design ideal, you thus want to avoid designs and coding practices that
create temporary tables or designs that copy/write out data and delete the
old data for no good reason at all. The less temporary tables and less
deleting of records means that your have less file growth. And, less file
growth will thus reduce the need to compact and repair on a frequent bases.

Ms-access works this way to keep performance high. If I have for example
100,000 records in a table, and you deleted the 1st record, then the moving
down the 99,999 records to fill that hole (gap) on the ms-access file would
take a LONG TIME to fill. And, during that move operation, no one else could
use the data file at the same time. (became the location of records is
changing inside of that mdb file). So, during a compact and repair
operation, no one else can be using the data file. Thus, compact and repair
is often scheduled to be done during the evening, or off hours when the
users of the application will not be impacted.

The backup database, when you make changes to the original file, does the
backup update also?
Click to expand...

There is no backup database in ms-access. So, you have one file. Because
ms-access does not have a recovery file, then you should backup the data on
a daily basis. So, the answer is no, the backup does not get updated because
there is no backup file.

You might want to recheck or try to ascertain what this other backup file
you're talking about is? MS access does not create them for you
automatically or during regular use. The moral of this lesson is simply put,
you must have a frequent backup plan here. Lkely on a daily basis is the way
to go here. If you don't have backups of your data, then you're asking for
trouble with MS access.
 
Think of access as being a like word document, but SEVERAL people
can use it at the same time. If you were to delete a row (one line
of text) in your word document, then everything would move up.
Click to expand...

Actually, Word doesn't work that way, either. It uses pointers to
mark used and un-used text. This is also how it can track changes,
as the text is not deleted, just marked unused. Periodically, I
think it internally cleans things up, but it doesn't clean up
everything. That fact is one of the reasons many distributors of
Word documents have been caught out, because there was leftover
deleted information remaining in a file that was the smoking gun
revealing something that had been removed from the document's
display text.

I believe that later versions of Word are better with cleaning up,
but there have always been tools that you can use to clean up all
the old text. I've never felt it important enough to use it in any
of my own documents.
 
Think of access as being a like word document, but SEVERAL people
can use it at the same time. If you were to delete a row (one line
of text) in your word document, then everything would move up.
Click to expand...

Actually, Word doesn't work that way, either. It uses pointers to
mark used and un-used text. This is also how it can track changes,
as the text is not deleted, just marked unused. Periodically, I
think it internally cleans things up, but it doesn't clean up
everything. That fact is one of the reasons many distributors of
Word documents have been caught out, because there was leftover
deleted information remaining in a file that was the smoking gun
revealing something that had been removed from the document's
display text.

I believe that later versions of Word are better with cleaning up,
but there have always been tools that you can use to clean up all
the old text. I've never felt it important enough to use it in any
of my own documents.
 
So, if you don't want your file size to grow like crazy, then as
overall design ideal, you thus want to avoid designs and coding
practices that create temporary tables or designs that copy/write
out data and delete the old data for no good reason at all. The
less temporary tables and less deleting of records means that your
have less file growth. And, less file growth will thus reduce the
need to compact and repair on a frequent bases.
Click to expand...

I disagree with this strenuously. There is nothing at all wrong with
temp tables.

What *is* wrong is putting them in your front end. They belong in a
temp database that is either compacted regularly, or that is
recreated from scratch when needed (either through code, or by
copying an empty template).
 
So, if you don't want your file size to grow like crazy, then as
overall design ideal, you thus want to avoid designs and coding
practices that create temporary tables or designs that copy/write
out data and delete the old data for no good reason at all. The
less temporary tables and less deleting of records means that your
have less file growth. And, less file growth will thus reduce the
need to compact and repair on a frequent bases.
Click to expand...

I disagree with this strenuously. There is nothing at all wrong with
temp tables.

What *is* wrong is putting them in your front end. They belong in a
temp database that is either compacted regularly, or that is
recreated from scratch when needed (either through code, or by
copying an empty template).
 
David W. Fenton said:
Actually, Word doesn't work that way
Click to expand...

quite true...

I used the word "like" here. I was using it in a metaphorical sense. We are
here to help people and that helping has to take into account the "skill
level" and knowledge of the person asking the queston. If that person has
an intellectual skill level and understanding of the internal workings of
word and how it uses pointers to manage things, then I doubt the user would
be here actually asking this question.

However, I probably should have said notepad. I could never really be sure
that the users ever used notepad. As I was creating this response I also
considered using excel and was going to mention deleting a row in excel.
However, I did NOT want to bring up the term excel in any way at all in this
conversation.
 
David W. Fenton said:
Actually, Word doesn't work that way
Click to expand...

quite true...

I used the word "like" here. I was using it in a metaphorical sense. We are
here to help people and that helping has to take into account the "skill
level" and knowledge of the person asking the queston. If that person has
an intellectual skill level and understanding of the internal workings of
word and how it uses pointers to manage things, then I doubt the user would
be here actually asking this question.

However, I probably should have said notepad. I could never really be sure
that the users ever used notepad. As I was creating this response I also
considered using excel and was going to mention deleting a row in excel.
However, I did NOT want to bring up the term excel in any way at all in this
conversation.
 
Té said:
In 2003 and 2007 there is an option back up database. It is under
database
utilities.
Click to expand...

Ah...ok....

All that feature does is make a copy of the database. It's possible at the
same time it might do a compact and repair. However these are still separate
issues.

At the end of the day, a backup is not done automatically for you. At the
end of the day when you do a compact and repair, a backup is not made.

When you do a compact and repair, the backup file has no relationship to
this issue, nor is the backup touched or updated in any way shape or form.

So if you need a backup of your database, you simply need to make a copy of
the database file. You can go into the windows file explorer and copy the
file, or as an convenience you can use the backup option inside of access
(they both just make a copy of the file). So the backup option is really
just making a copy of your database.

That backup copy once made is never touched nor is it updated by any other
operation in access, and if you want to make another backup, you'll have to
back the file up again.

So, either way, the back option and the compact/repair option are separate
issues.
 
Té said:
In 2003 and 2007 there is an option back up database. It is under
database
utilities.
Click to expand...

Ah...ok....

All that feature does is make a copy of the database. It's possible at the
same time it might do a compact and repair. However these are still separate
issues.

At the end of the day, a backup is not done automatically for you. At the
end of the day when you do a compact and repair, a backup is not made.

When you do a compact and repair, the backup file has no relationship to
this issue, nor is the backup touched or updated in any way shape or form.

So if you need a backup of your database, you simply need to make a copy of
the database file. You can go into the windows file explorer and copy the
file, or as an convenience you can use the backup option inside of access
(they both just make a copy of the file). So the backup option is really
just making a copy of your database.

That backup copy once made is never touched nor is it updated by any other
operation in access, and if you want to make another backup, you'll have to
back the file up again.

So, either way, the back option and the compact/repair option are separate
issues.
 
David W. Fenton said:
I disagree with this strenuously. There is nothing at all wrong with
temp tables.

What *is* wrong is putting them in your front end. They belong in a
temp database that is either compacted regularly, or that is
recreated from scratch when needed (either through code, or by
copying an empty template).
Click to expand...

You are not making a distinction between temporary tables, and designs that
avoid copying + writing of data. If you look at the above I mention both
issues:
thus want to avoid designs and coding
Click to expand...
practices that create temporary tables or designs that copy/write
out data and delete the old data for no good reason at all.

In the case of temporary tables, yes you can (should) use external temp
tables. However you can't use temp tables in designs that needlessly copy
and rewrite data. So moving data back and forth between a history table and
an active table, or other such designs that copy data and delete the
previous data still should be avoided.

You simply can't use a temp table in these cases because the data is not
temporary. So what you're saying does not apply nor is even possible when
designs are such that they copy data needlessly. It's good to point out that
"external" temporary tables can be used for temporary data, but you're
still only telling half the story and you're missing half of the point here
by not explaining that designs that copy data needlessly still need to be
avoided when possible.
 
David W. Fenton said:
I disagree with this strenuously. There is nothing at all wrong with
temp tables.

What *is* wrong is putting them in your front end. They belong in a
temp database that is either compacted regularly, or that is
recreated from scratch when needed (either through code, or by
copying an empty template).
Click to expand...

You are not making a distinction between temporary tables, and designs that
avoid copying + writing of data. If you look at the above I mention both
issues:
thus want to avoid designs and coding
Click to expand...
practices that create temporary tables or designs that copy/write
out data and delete the old data for no good reason at all.

In the case of temporary tables, yes you can (should) use external temp
tables. However you can't use temp tables in designs that needlessly copy
and rewrite data. So moving data back and forth between a history table and
an active table, or other such designs that copy data and delete the
previous data still should be avoided.

You simply can't use a temp table in these cases because the data is not
temporary. So what you're saying does not apply nor is even possible when
designs are such that they copy data needlessly. It's good to point out that
"external" temporary tables can be used for temporary data, but you're
still only telling half the story and you're missing half of the point here
by not explaining that designs that copy data needlessly still need to be
avoided when possible.
 
You are not making a distinction between temporary tables, and
designs that avoid copying + writing of data.
Click to expand...

Anything can be used wrongly. Your comments condemned temp tables
across the board, without specifying how they can be used properly.

Sturgeon's Law applies everywhere, and is no reason to reject temp
tables per se.
 
You are not making a distinction between temporary tables, and
designs that avoid copying + writing of data.
Click to expand...

Anything can be used wrongly. Your comments condemned temp tables
across the board, without specifying how they can be used properly.

Sturgeon's Law applies everywhere, and is no reason to reject temp
tables per se.
 
David W. Fenton said:
Anything can be used wrongly. Your comments condemned temp tables
across the board, without specifying how they can be used properly.
Click to expand...

Well if you have a choice to avoid temp tables, then the fact that you can
use temp tables without bloating still does not make any sense.
Sturgeon's Law applies everywhere, and is no reason to reject temp
tables per se.
Click to expand...

So are you now telling me it is a good idea to use External temp tables
when you can choose a design that avoids temp tables?

Yes, the law does apply here and making an across the board statement that
temp tables are ok when they can be avoided is the same thing here. It
makes sense to avoid temp tables if you can come up with a design that does
not need them.
Your comments condemned temp tables
across the board, without specifying how they can be used properly.
Click to expand...

Your comments approved temp tables across the board without specifying
that it is better to avoid them when you can!
 
David W. Fenton said:
Anything can be used wrongly. Your comments condemned temp tables
across the board, without specifying how they can be used properly.
Click to expand...

Well if you have a choice to avoid temp tables, then the fact that you can
use temp tables without bloating still does not make any sense.
Sturgeon's Law applies everywhere, and is no reason to reject temp
tables per se.
Click to expand...

So are you now telling me it is a good idea to use External temp tables
when you can choose a design that avoids temp tables?

Yes, the law does apply here and making an across the board statement that
temp tables are ok when they can be avoided is the same thing here. It
makes sense to avoid temp tables if you can come up with a design that does
not need them.
Your comments condemned temp tables
across the board, without specifying how they can be used properly.
Click to expand...

Your comments approved temp tables across the board without specifying
that it is better to avoid them when you can!
 
So are you now telling me it is a good idea to use External temp
tables when you can choose a design that avoids temp tables?
Click to expand...

Of course not, and you know perfectly well I'm not making any such
ludicrous recommendation. Your original statement was unqualified by
any exceptions to your rule to not use temp tables, and that's why I
pointed out that temp tables are OK if you don't put them in the
front end, with the implied assumption behind that statement that
someone isn't using them stupidly.
Yes, the law does apply here and making an across the board
statement that temp tables are ok when they can be avoided is the
same thing here.
Click to expand...

I did not say that. So far as I'm concerned, any advice given in
this newsgroup contains the tacit assumption you shouldn't use the
wrong method to accomplish a task. When recommending a course of
action, I think it's safe to assume there's no "in all cases"
implied, but instead an "where it makes sense".
It
makes sense to avoid temp tables if you can come up with a design
that does not need them.
Click to expand...

No disagreement there.

That's very different from what you originally posted.
Your comments approved temp tables across the board without
specifying that it is better to avoid them when you can!
Click to expand...

Pot.Kettle.Black.

Temp tables *are* valuable in some circumstances. Your original post
did not allow for that. My post didn't specify that they could be
used wrongly.

This is not a symmetrical situation. You condemned them all. I
failed to specify that there are cases where they shouldn't be used.
That is only a valid criticism if my advice implied somehow that
they were always the best solution, and that's complete nonsense.
 
Back
Top