Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 886
  • Last Modified:

Bloating Access 2007 accdb file

Hi,

We have a Access database we use for logging tasks in our schedule (developed internally but not by me - I'm networking & OS not a DB engineer).

This was first written under 2003 and we have recently had to make certain changes so it will work under 2007.

It seems to work fine but we are experiencing a 'bloating' issue. The DB is about 20-25Mb after a compact & repair but if left to its own devices it can grow significantly in just a few days (to the order of a half gigabyte!)

I am aware of "Compact on close" but the DB will usually be open by a minimum of 10-15 users at any one time, sometimes as many as 50 and i'd have to kick each and every one of them out to do this (and ensure that other don't open it in the meantime!)

Can anyone provide any ideas as to what may be causing this? Is it a potential bug in 2007, do we have a bug in the code of the DB etc etc

Thanks all!

B
0
bheroniphr
Asked:
bheroniphr
4 Solutions
 
Patrick MatthewsCommented:
Hello bheroniphr,

1) Make sure that you are using a split architecture for this app.  In split Access apps,
it is typical to have 1 "back-end" file as the permanent data store, and then each
user gets his/her own "front-end" MDB or MDE file with the forms, queries, reports,
and code.  If your reports require creating "temp tables" (sometimes useful to
facilitate complex reporting), put those temp tables in the front-ends, or in a separate
"temp MDB" file.

2) Do set the back-end to compact on close.  The compact/repair should occur when
the last user is out.

3) If the app has a lot of INSERT and DELETE operations, the file size can grow
enormously but then shrink back down on a compact.  Just part and parcel of how
Jet manages disk space.  Similar things can happen on other db platforms.

Regards,

Patrick
0
 
mmconsultantCommented:
If you have a time when you know that there will be no users in the database, you can schedule a task to do the compaction.

Also the split architecture is a great idea. If the DB is not too complex you may want to think about using SQL server for your back-end storage. There is an upgrade wizard in Access for SQL server, but be warned that in my experience it is not a one-click experience for all but the most simple DB.

As matthewspatrick said, inserts and deletes in Access cause this bloat and there's no way (that I know of) to stop that behavior in and MDB.

0
 
peter57rCommented:
'2) Do set the back-end to compact on close.  The compact/repair should occur when
the last user is out.'

Sorry Matthew, I don't think this is so.
The backend is never open in Access UI terms, and the compact on close is a UI feature not a Jet feature, as I understand it.  
You would have to issue a compact command from the frontend but you have to take account of the fact that there might still be other users connected and this would cause the compact to fail.
And you would need to sort out the fle names after the compact.

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Are you storing any images?
If so >>> http://www.ammara.com/  DBPix ... will fix the bloat.

"2) Do set the back-end to compact on close.  The compact/repair should occur when
the last user is out."
If it's already split,  that would happen only if the BE is opened and closed manually.

mx


0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
See these links I am accumulating for A2007 - a good reference in general:

http://msdn2.microsoft.com/en-us/library/bb203849.aspx  'Transisitioning to A2007

http://support.microsoft.com/ph/11265    ' Various A2007 Issues  

http://blogs.msdn.com/access/archive/2006/06/05/618366.aspx    'A2007 Limitations list

http://allenbrowne.com/Access2007.html   ' A bunch of good stuff

http://msdn2.microsoft.com/en-us/library/aa722523.aspx    'Ribbon
0
 
Patrick MatthewsCommented:
DatabaseMX said (and Pete said something similar):
>>If it's already split,  that would happen only if the BE is opened and closed manually.

For some reason I thought it was happening as I described.  Thanks for that bit of
enlightenment :)
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now