Avoid Bloat in linked DBF

[accesskastle]

Hi. I have a linked dbf to my Access 2003 database. I have some code which
I want to clear the dbf's records (Delete all records) and then rewrite them
using ADO's Execute method using the currentproject connection object with
some INSERT query.

Unfortunately, I've noticed that my dbf bloats like crazy, up to 35MB and
lists an excessive number of records (141,000+). I manually deleted the
records in another environment and ran the sub once, and it dropped to 132Kb,
and 542 records (which sounds more reasonable to me). I'm thinking this
could be for one of two reasons:

1) The Delete has not fully executed (Deleted all records) before starting
to append. If this is the case, how can I get around this? I'm not really
familiar with transactions, so if someone can help me do this with the code
I've posted below?

2) The syntax for the delete string is not correct or won't work for a
linked dbf, or some provider related issue. If this is the case, or I'm
going about it incorrectly, I'd really appreciate some suggestions how I can
proceed.

The code for the Sub is posted below. Thanks!

AK
------------------------


Public Sub RefillTable(strTableName As String, strAppend As String)

Dim cnn As ADODB.Connection
Dim strSQLDelete As String

On Error GoTo Err_RefillTables

Set cnn = CurrentProject.Connection

'Clear Table
strSQLDelete = "DELETE * FROM [" & strTableName & "];"
cnn.Execute strSQLDelete
'Append records
cnn.Execute strAppend
Exit_RefillTables:
Exit Sub

Err_RefillTables:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Exit_RefillTables

End Sub

 

[Albert D. Kallal]

1) The Delete has not fully executed (Deleted all records) before starting
to append. If this is the case, how can I get around this? I'm not
really
familiar with transactions, so if someone can help me do this with the
code
I've posted below?

Unfortunately, for about the last 20 years of the personal computing
industry, when in we delete records in a database, the file space is not
return to the available pool. In other words, going back to the old original
dbase II on the original IBM PC, there was a command called "pack". Of
course in MS access we have something called a compact and repair.

In a nutshell because the jet database engine is what we call a file share,
two users could be physically accessing your database **file** at the same
time. Because more than one user can have this file open, it is impossible
to return space to the disk drive and reduce the size of the file while
other users (other pc's in the office) have the file opened. Those other
PC's would have no way of keeping track that the files are being moved, or
the position of the file on the disk drive is being moved..

Also, the other reason why the file does not shrink during normal usage is
for reasons of performance. You mean to tell me that if I have a 500MB file,
and I delete one little tiny record at the beginning of the file, now we
have to move 500MB of data down to remove that one little tiny hole at the
start of the file? I can remember when 500MB was the size of my whole
computer disk drive, and copying 500 megs of data to delete one tiny reocrd,
is well to say....really dumb!!!

I'm not sure how much thought you given to the above problems, but if you
start thinking about these typical problems, you can rapidly see why for the
last twenty years MOST most of the pc "file" systems do not recover deleted
records.

Note that when you use a true database server, the database server can
manage this problem far better, because you might have twenty users
connected to the database server, but there's only ONE physical program that
is accessing the file sitting on the disk drive directly at one time.

Since MS access is used with "JET" the database engine, and this engine
*can* be used as a file share system, then recovery of deleted space does
not occur during normal operations.

The practical conclusion of the above information is that for about the last
twenty years, any software developer doing data base operations on a pc file
system will do absolute handstands, and take considerable of steps to avoid
desing that delete records.

There are few possible workarounds and solutions to your problem:

The first thing you can do, (at least in ms-access) is ensure some settings
that will reduce the bloat. For example, make sure you don't have the row
level locking turned off in jet, because jet accomplishes this task by
actually padding and bloating file on purpose! (it expands the records to
the point in which two users will never be using the same spot on the disk
drive!).

Another common approach is to create a temp mdb, link to that file, and then
send the data to this temp mdb file. This is then deleted after you're done
with it. This approach is typically best used when you have temporary data
that you have to crank out for a report. What this means is that we don't
use a local table in our mdb fle, but create a external mdb file to hold
this temporary data. We then send the data to that file, run the report.
Afterward we delete this external file.

For example I had a design in which I had a table that had a very high churn
rate very during the day (it was a custom record locking scheme that I
built). However, I never actually deleted records from this table, but had a
field that marked the reocrd as active, or not, and simply was able to
recycle the records over and over). Once again, the above show's you just
have to be creative in your designs once you have the above knowledge.

I would suggest perhaps in this case, that you create a new blank file from
scratch into which to put your data into.

There is a sample db here that allows you to create temp mdb

http://www.granite.ab.ca/access/temptables.htm

and, more info on bloat here:

http://www.granite.ab.ca/access/bloatfe.htm

 

[accesskastle]

Thank you, Albert, for the resources and the input about returned disk space.
I'll look into the temporary table solution.

AK

 

[Klatuu]

Great description, Albert. It does point out one thing I miss from the xBase
days. A delete did not actually delete a record. It marked it as deleted
and you chould choose whether to include or exclude *deleted* records. A
common technique was to temporarily show the deleted records, grap the first
one, empty the fiels and undelete it and reuse it. It is pretty much as you
described your method of doing this.