Compact and Repair & Backup Database

[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?

 

[Albert D. Kallal]

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

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?

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.

 

[KenSheridan via AccessMonster.com]

I'm wondering whether there might be some confusion of terms here. As Albert
has pointed out there is no 'backup' file in Access per se. Are you by any
chance thinking of a 'back end' file? A 'back end' file is when a database
is split into a 'front end' and 'back end', the former containing the form's,
reports , queries etc and links to the tables; the latter containing only the
tables. A back end file might be shared by several users on a network, each
user opening a separate copy of the front end on their local machine. With
this set-up any changes to data you make via the front end are stored in the
back end as the front end stores no data at all, only links to the tables in
the back end.


For backing up Access files you can of course use any standard back-up
application which backs up your Access files along with all others. On a
network the back end will normally be backed up by whatever system the
network administrator has put in place, on a standalone system you can
install your own back-up software of choice and schedule it to back up your
files regularly (my home machine is scheduled to do an incremental back-up
daily). This will not compact and repair the Access file, however, so
separate provision should be made for this. If you wish you can include a
routine in your front end file to back-up the back end by compacting and
repairing it. The following procedure will do this and is called by passing
the full paths to the back end file and back-up file to be created into it
as string expressions:

Public Sub BackUp(strBackEnd As String, strBackUp As String)

' accepts: full path to back end file used
' : full path to back-up file to be created

Const FILEINUSE = 3356
Dim strMessage As String
Dim strBackUpTemp As String

' if back up file exists get user confirmation
' to delete it
If Dir(strBackUp) <> "" Then
strMessage = "Delete existing file " & strBackUp & "?"
If MsgBox(strMessage, vbQuestion + vbYesNo, "Confirm") = vbNo Then
strMessage = "Back up aborted."
MsgBox strMessage, vbInformation, "Back up"
Exit Sub
Else
' make temporary copy of backend file and then delete current
back up
strBackUpTemp = Left(strBackUp, InStr(strBackUp, ".")) & "bak"
MakeFileCopy strBackUp, strBackUpTemp, False
Kill strBackUp
End If
End If

On Error Resume Next
' attempt to open backend exclusively
OpenDatabase Name:=strBackEnd, Options:=True

Select Case Err.Number
Case 0
' no error so proceed
Application.CompactRepair strBackEnd, strBackUp
If Err.Number = FILEINUSE Then
' file in use by current user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" You may have a table in it open."
MsgBox strMessage
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
Exit Sub
Else
On Error GoTo 0
' ensure back up file created
If Dir(strBackUp) = Mid(strBackUp, InStrRev(strBackUp, "\") + 1)
Then
strMessage = "Back up successfully carried out."
' delete temporary copy of back up file if exists
On Error Resume Next
Kill strBackUpTemp
On Error GoTo 0
Else
strMessage = "Back up failed."
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
End If
MsgBox strMessage, vbInformation, "Back up"
End If
Case FILEINUSE
' file in use - inform user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" It may be in use by another user."
MsgBox strMessage
' rename temporary copy of back up file,
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
Case Else
' unknown error - inform user
MsgBox Err.Description, vbExclamation, "Error"
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
End Select

End Sub

Ken Sheridan
Stafford, England

 

[David W. Fenton]

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.

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.

 

[David W. Fenton]

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.

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).

 

[Té]

In 2003 and 2007 there is an option back up database. It is under database
utilities.

 

[KenSheridan via AccessMonster.com]

I'm using 2002, so don't have that menu item. But the answer to your
original question will doubtless be that a back-up is not updated
automatically with changes to the data, only when you create the back-up.
One thing you should look into is what is being backed up. If the database
is split into front and back ends, as all but trivial applications will be
(or, if not, should be), what is backed up? I would guess it’s the front end
only. If so the data is not being backed up, only the front end file.
Usually it’s the back end(s) you want to back up more regularly of course.
You can use a separate back-up application or the routine in my first post
for this.

Ken Sheridan
Stafford, England

In 2003 and 2007 there is an option back up database. It is under database
utilities.
[quoted text clipped - 14 lines]

the old text. I've never felt it important enough to use it in any
of my own documents.

 

[Albert D. Kallal]

Actually, Word doesn't work that way

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.

 

[Albert D. Kallal]

In 2003 and 2007 there is an option back up database. It is under
database
utilities.

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.

 

[Albert D. Kallal]

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).

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

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]

You are not making a distinction between temporary tables, and
designs that avoid copying + writing of data.

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.

 

[Albert D. Kallal]

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

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.

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.

Your comments approved temp tables across the board without specifying
that it is better to avoid them when you can!

 

[David W. Fenton]

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?

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.

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.

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!

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.