Re: Rapidly expanding MS Access data file size

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.




On Mon, 28 Nov 2005 06:19:03 -0800, "Informate-Rod"
<InformateRod@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Loading