Access file expanding to over 1 Gb

[Guest]

We're using an Access 97 database. Every few months it expands to over 1
gigabyte and stops working. Realistically, it should normally be about 20 -
50 MB in size. Any idea what might be causing this extreme bloating?
Running it over a network. Compact and Repair sorts it out, I just don't
want it to keep happening.

 

[Joseph Meehan]

We're using an Access 97 database. Every few months it expands to
over 1 gigabyte and stops working. Realistically, it should normally
be about 20 - 50 MB in size. Any idea what might be causing this
extreme bloating? Running it over a network. Compact and Repair
sorts it out, I just don't want it to keep happening.

Are you adding and deleting records regally? If so that is likely it.
When you delete a record in Access like most databases it is not deleted
until a second step is taken (compact for Access). Until then it is just
marked as deleted. I suggest you arrange a regular schedule to do a repair
and compact.

BTW 1 gig is the max for the older (97) Access databases.

 

[Larry Linson]

BTW 1 gig is the max for the older
(97) Access databases.

And that is why it stops working when it exceeds 1GB.

One good suggestion is: If you use temporary tables, create them in
temporary databases, and when you are done, unlink the tables in the
temporary database and use the KILL statement to erase it. There's an
example at MVP Tony Toews' site, http://www.granite.ab.ca /accsmstr.htm.

Larry Linson
Microsoft Access MVP