Database File Growing

[Fasopus]

So I realize that Access 2007 has a file size limit of 2GB, and the database I am developing now is far from that, however I am noticing that as queries and forms are run the size of the database file grows. I was wondering if there was anyway to prevent this from happening or if there was somthing I missed in my design as this could lead to potential problems in the future.

 

[ConneXionLost]

What happens to the size of your file after you do this?

Compact & Repair.

Cheers,

 

[boblarson]

1. the database should be split with each user having a copy of the front end on their machine. (see here for more about that).

2. You can use Compact and Repair occasionally to manage the size.

 

[Fasopus]

This does shrink the database quite a bit. Is there anyway to make a button or command out of this? The person that will be administrating the database is pretty computer illiterate, and how often would you recommend this is done?

 

[ghudson]

Turn on the "compact on close" option and access will compact the database each time the user closes it.

 

[Fasopus]

Turn on the "compact on close" option and access will compact the database each time the user closes it.

Ok, this should work for the meantime but in the future the database will be split and distributed to about 10 or 15 different people on the network, what happens if multiple people are accessing the database?

 

[boblarson]

what happens if multiple people are accessing the database?

It will only compact if the last user closes out of it.

However, just so you know - the longer you delay splitting, the longer you are living in very scary time. Corruption on an unsplit database file being accessed by multiple users is very likely. It is even likely with a SINGLE user doing so over the network but much more so with multiples. If just one user suffers a small network disruption, it can have devastating results on the database file, so I hope backups are being made by your IT folk daily and that they can restore the file, if necessary. You would then need to add any data from that previous day into it again.

 

[ghudson]

You will have to manually open the backend for the compact on close option to work. Each users front end [once you split it and install their front end onto each users hard drive] will compact each time they close it, which can be annoying to some users. I instead test for how large the db is [when the user clicks my custom Exit button] and turn on the compact on close option if the database exceeds a set limit I have defined.

   If FileLen(CurrentDb.Name) > 90000000 Then  '90 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The XYZ application must be compacted, please do not interfere with the compacting process as the database is closing!")
        MsgBox "The XYZ application must be compacted, please do not interfere with the compacting process as the database is closing!", vbCritical, "Compact Database"
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

 

[Fasopus]

It will only compact if the last user closes out of it.

However, just so you know - the longer you delay splitting, the longer you are living in very scary time. Corruption on an unsplit database file being accessed by multiple users is very likely. It is even likely with a SINGLE user doing so over the network but much more so with multiples. If just one user suffers a small network disruption, it can have devastating results on the database file, so I hope backups are being made by your IT folk daily and that they can restore the file, if necessary. You would then need to add any data from that previous day into it again.

I definately have heard of the corruption problems, luckily the database isnt on the network yet, how I'm handling it at the moment is developing it locally on one machine and uploading a fresh copy (including backups) to the network drive at the end of every evening, this way my supervisors can see and play around with the database on the network while I have backups and a safe current copy on my machine.

EDIT: Good idea Ghudson, Ill be sure to implement something similar in the near future