Database Growth

[Jeff]

I created a multi-user access 2000 database program.
I split the database into data tables and application. The
application portion reside on the users local drives and
the data tables reside on a server, which the users'
application databasaes are linked to. I have over 40 +
users adding records daily.

I used VBA (DAO) to create recordsets when the users are
adding records. Records are added through forms. I
automated the adding of records using DAO so that records
could be added very easily by the users. One example, when
a user scans a serial number, a record is automatically
added to the table, along with a date stamp, time stamp
and some other info.

I have noticed lately the size of the database has grown
out of control. The database was over 2 GB earlier today.
I compacted the database and it returned to 18 MB. I found
an article on the microsoft site (197953) that mentioned a
similar problem. However I did not use DAO to create table
defs, I use DAO to create recordsets, and to manipulate
forms.

Any suggestions would be welcome.

 

[Tim Ferguson]

I have over 40 +
users adding records daily.

This is very heavy use for an Access database - it may be time to consider
upsizing. I hope you have an enthusiastic back up policy!

I have noticed lately the size of the database has grown
out of control. The database was over 2 GB earlier today.
I compacted the database and it returned to 18 MB.

Hmmm: 2GB is the absolute limit for a jet database, so it's very close to
death. As for the bloat, Jet is very lazy about recovering deleted space,
and with that many users interacting, it's not surprising that it gets
blown up. It all sounds very nervous though!



B Wishes


Tim F