Database size

[Dale Matt]

I have an Access 2002 database system with a front end mdb linked to a
backend mdb containing the data files. The contents of the data files are
very stable although the mdb file itself is .5 GB.

The front end system is approx. 20MB in size, but in the course of an hour
of use it can grow to 50MB or larger. Most of the use involves running code
that generates queries and produces reports. Compacting returns the file to
20MB. However, frequent compacting is inconvenient for users.

What are some tips for keeping the front end size from having to be
compacted frequently, i.e., how can I keep the size from doubling during
normal operations?

Thanks in advance!

 

[Klatuu]

Although you have a split database, some things in a front end can cause
that bloating.

First, each user should have their own copy on their own computer and not be
sharing a single copy on a network.

If you have any make table queires, this is a major contributer to bloat.

One very easy way to solve this problem without having to compact at all is
to put a copy on a shared folder to which all users have read access. Then,
rather than open the local copy directly, use a .bat file that copies the
fresh copy from the shared folder to the user's working folder and then
starts the application. This servers two purposes. First, they are always
working with a copy that is not bloated and second, it simplifies
distributing modifications to the front end. All you need to do is put the
new version in the folder the users load from. And, no, there really is no
preceived delay in loading the application this way.

 

[Dale Matt]

Thanks, Klatuu...

The front end is on local desktops. The local copies are updated from a
network copy -- but only when changes are made. Updating the local copy
every time a user signs on is a great idea.