Rapidly expanding MS Access data file size

[Informate-Rod]

I have a small Access 2000 app that whilst working is causing some concern.

The app is a VB module that is constantly running. The module imports lots
(30000+/day) of simple CSV style files, applies business rules to the raw
imported data and updates tables in an attached SQLServer 2000 database under
transactional control. The raw imported data is deleted before new data is
imported. An ODBC connection object is established at App startup and the
same connection is used to perfom all accesses to the SQLSrvr repository. The
app should run 24*7. The app is activated by calling it from an 'AutoExec'
macro that automatically runs on startup.

The app runs fine except that the size of the access file grows rather
alarmingly quickly. i.e. 3->14Mbyte in 2 hours. If you terminate and merely
'Compact & repair the database' the size goes back to the original 3 Mb.

I've doublechecked the code looking for obvious problems - making sure all
recordsets get closed etc - everything looks fine.

VB is not my normal language so I'm a little wary that I've ballsed-up;
looks and feels like a classic memory leak but I do not know memory
alloccation rules in VB.

The only other thing that might be a problem is if there are issues with
FileImporting using FileImportSpecs.

Anyone got any bright ideas??

cheers

Rod.

 

[Jesper Fjølner]

The app runs fine except that the size of the access file grows rather

alarmingly quickly. i.e. 3->14Mbyte in 2 hours. If you terminate and
merely
'Compact & repair the database' the size goes back to the original 3 Mb.

I've doublechecked the code looking for obvious problems - making sure all
recordsets get closed etc - everything looks fine.

I don't think anything is wrong. It's normal behaviour for Access increase
in size when you add and delete tables and records (the records aren't
actually deleted until you Compact and Repair - hence the increasing size).
Just compact regularly (maybe several times a day) and you should be fine.
Under Tools > Options you can set Compact on Close to True and close and
reopen the app now and then.

Jesper

 

[Klatuu]

Given what you are doing with the database, the bloat is not unexpected. One
option that may help, but would require some rework, would be to link to the
csv files rather than import and delete. Also, it would be advisable to set
you options to do a compact on close. This will keep the bloat down.

 

[Informate-Rod]

thanks for the info. Most helpful. Is it possible to programmatically invoke
the 'Compact & Repair feature' from within VB - i.e. w/o having to open and
close the app??

cheers Rod.

 

[Jesper Fjølner]

thanks for the info. Most helpful. Is it possible to programmatically

invoke
the 'Compact & Repair feature' from within VB - i.e. w/o having to open
and
close the app??

Yes it's possible.
Look in the help file for "CompactDatabase"

I've used in frontend-backend setups for compatcting the backend.
I'm unsure whether you can compact the db you have open.
In my case made a function that :
copied the BE to another location on the local drive >
compated that copy >
renamed the current BE to "nameofBE_date.bak"
move the new compacted BE to right location and let it overwrite the
existing BE.

This all requires that noone is using the file.

 

[Informate-Rod]

thx jesper - i'll try that

Yes it's possible.
Look in the help file for "CompactDatabase"

I've used in frontend-backend setups for compatcting the backend.
I'm unsure whether you can compact the db you have open.
In my case made a function that :
copied the BE to another location on the local drive >
compated that copy >
renamed the current BE to "nameofBE_date.bak"
move the new compacted BE to right location and let it overwrite the
existing BE.

This all requires that noone is using the file.

 

[John Nurick]

Hi Rod,

I suggest you use a temporary .mdb file for the temporary tables. That
way, you don't need to bother with deleting tables and compacting, just
delete the bloated .mdb file and start the next import-process-upload
cycle with a new one.

You can either store a "template" mdb with the tables you need already
in existence, and just have your code create a copy of this, or write
code that has Access/Jet create the new mdb and then the tables and
relationships needed. Either way you'd probably used linked tables in
the main .mdb to access the tables in the temporary one.

 

[Hans Mol]

I have the same problem which needs to be solved as approx 25 people use the
database and some odbc connections are made, therefore compaction is very
hard (database must not be in use)
I found 2 tables to be the reason for increasing the size (200Mb to 1000Mb
in one week)
the database is a backend so only tables are in this file nothing else.
the number of additions to the tables in not much (approx 30/day) and the
number of edditions is approx 100/day.
the tables have about 150000 records each and there are a lot of empty fields.
the increase only started a couple of weeks ago afte 1,2 years of use

 

[Dale Fye]

Guys,

The problem is that you are appending to a temp table in your local
database, and then deleting the data from that table.

My recommendation is that in addition to the SQL server database, you create
a separate Access MDB file for your temp tables, and link those tables to
the application that is managing the imports. If you have multiple front
ends, and each person is doing their own imports of data, then I would put
that Tempmdb file on their local machine. Otherwise, you could put it on
the network if you only have one person or an automated process doing the
imports. Then, when Access imports the new file, it is actually importing
into a table in Temp.mdb.

When you finish the import, and delete the data from the temp table, you
could use the CompactDatabase method to compact the temp.mdb file. You may
have to delete the link to Temp.mdb before you do the compact operation, but
I don't think so. Just make sure that you don't have any forms or
recordsets that open that are connected to that mdb file. Additionally,
since the CompactDatabase method requires that you provide a new datbase
name (I use Temp1.mdb), you will have to check to see if this file already
exists. If so, you will need to delete it before compacting. Once the
CompactDatabase method is complete, you will have to delete the old file
(Temp.mdb) and Rename the compacted version (Temp1.mdb) to Temp.mdb. Once
that is complete, you will need to refresh the link for the table that is
linked from that database.

An alternative to this is to have a copy (empty) of the temporary table in
your application database. Then, every time you need to import a file, you
could use the CreateDatabase method to create a new database (Temp.mdb)
somewhere on the users hard drive. Then, use the TransferDatbase method to
export the empty compy of the table to that database. Then, you create a
link to that table, again using the TransferDatabase method (this time with
the acLink parameter). When you are done importing that file, you can
simply delete Temp.mdb, because you will be creating it again when it is
needed.

A last consideration is that you don't really need to compact the temporary
datbase each time it is used, you could use the FileSystem object to
determine the size of the mdb and when it reaches some limit that you have
established, then do the compact or delete/create database.

HTH
Dale

 

[Hans Mol]

Thanks for your reply,
You can indeed use a temp mdb and then compact it, this is what I already
have done as follows:
all clients (approx 25) use their own mdb application and all are connected
to the backend by linked tables.
Doing large calculations, the data needed for this is copied to a temp table
and after that the application is compressed, this works fine.
But they also have to append data to the backend tables and modify the data.
even with a small number of records appended, the size increases tremendously
in one week to 1000MB (5x the original).
People allways say that access needs to be compacted once and a while but
the strange thing is that even after deletion of all the tables in the
backend mdb, the database still is 1000MB (so no contents), also compacting
it with winrar gives the same size as the original database with all the data.
This means that there must be something hidden in the mdb that uses a lot of
space.