File size bloats when importing text files

[wincey]

I am using a access 2000 database which was written by someone else which uses mostly vb code rather than macros. It uses a import procedure to import data from 7 text files and then append the data to different tables, and also moves the oldest data into a history table. When it creates the history table, it is deleted and recreated with the new data. When I ran this procedure in access 97 it increased the file size a little due to deleting and recreating the tables, but nothing to severe. However when i converted the database into access 2000 and ran it, the file size increased from 60 to over 300MB! When i compact it it decreases again, but during the compaction it creates a copy, and my IT department will be a little upset if i keep taking up 600MB on the server with one file! The microsoft site suggests it is something to do with access 2000 using unicode which takes up more space but there solution didn't seem to work. This is a very important database so if anyone has any suggestions on why i only get this problem in access 2000 and / or how to fix it i would be very grateful!

 

[Harpz]

When you compact a system, Access takes a copy of the current database and then compacts that one. Once the copy has been completed the compaction, then the copy becomes the live version and the old version is deleed. So, when compacting, there will only be a duplicate of data on the network for a short period of time. I dont really think you should worry about it unless the shared area starts to get really full and there isn't even space for the 300mb !

Harpz

 

[wincey]

thanks i did realise that is was only taking up the extra space while it was compacting, but the total 600MB was actually completely filling up the shared area as it is only small, and it was taking ages to compact it. Last time I tried to compact it had grown so much there wasn't enough space to create the copy for the compaction, so i had to get it to compact it to a different location. I have actually moved the file to the C drive now and the problem does not occur when running it from there. It only takes 2 minutes to run the procedure instead of 20 and the file size barely changes. Not really sure why this is happening but at least its a solution!

 

[Ed2020]

Be extremely careful when compacting databases on a drive with limited space. Things can go *very* pear-shaped if Access runs out of drive space - loss of data or corrupt files.

Ed Metcalfe.