The action queries are causing the size increase. Access may not release the
previously used space until you compact the database.
The most obvious alternatives are to ensure the INSERT and UPDATE queries
don't make any unnecessary changes. For example, instead of:
UPDATE MyTable SET IsPicked = False;
there would be fewer updates if you used:
UPDATE MyTable SET Field1 = False WHERE IsPicked = True;
Similarly, if you are relying on a unique index to weed out duplicates when
you run your INSERT, use a WHERE clause so there are fewer attempts.
Other alternatives might include using the command line switch for
msaccess.exe to compact the database. Or you might create an mdb file with
the right structure but no data, and have it available as a template. Your
vb.net code could then copy the file, populate it, delete the old one, and
rename the new one so that it takes its place.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:%2***************@TK2MSFTNGP04.phx.gbl...
>
I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from
a remote db. The mdb file size is normally around 80MB but since running
the vb.net app the mdb file size grows to 1GB or so every few days. After
compact and repair the size does get back to 80MB but it is still a pain
to have to compact repair every few days. What is causing this massive
file size increase and is there any way to control it?
Thanks
Regards