Access/VBA runtime for txt conversion

[peace77]

I have designed an application in Access 2003 that imports and converts a text file (which usually is about 14 MB) and then parses the data (analyze each record one by one) into the desired output. I have used VBA for this purpose and I am novice both in Access and VBA. After the data is imported and converted and compacted the size of the access data base comes to about 30 MB

The problem is that this whole process takes really long time, about half an hour. I searched over the internet and forums where it was suggested that if we go under Tools ---> Options -à General tab and disable the “Name Auto Correct Option” then the speed increases.

I did the same and the running time has come down from 35 minutes to about 10 minutes. But the glitch is that the size of the database explodes to 450 MB. And when I compact it comes back to the 30MB size.

I am not sure if this a normal Access behaviour or if something is wrong in the way I have coded the VBA modules.

I was hoping if some one can suggest some options to cut down on the running time.

I have to now design an excel application that will run the query on this database to extract some information. And this excel application will be run daily and I do not want the users to wait for that long or experience that humungus database size.

Any help is really appreciated.

Thanks very much

Peace

 

[Dennisk]

it is probally the imports that is causing the bloat.

You can either have an import file where you delete the records before importing or use a make table query. Either way the original data is deleted but the space in the .mdb is not recovered. Have you tried compacting on exit. Or move you import file to another db and just llink the table.

 

[Ziggy1]

to get a full compact you need to empty the tables and then compact.

how many records on average are in the DB after import.

For the text import are you using a specification to break up the columns?