Access 2007 Bloating

[Mark344]

Hi

Hope someone can offer some advise!

I have written a database for a local charity that stores names/address,
donations, campaigns etc etc.

The database works fine but 'bloading' is starting to cause slow access over
a network. I've tried splitting the database and notice it's the FE that
bloats.

Ok, I can Compact/Repair on a regular basis (which works fine) but I would
rather resolve what is causing the bloating.

I've read somewhere that a 'recordset' close is reqiuired somewhere but
unsure what that all means!

If anyone can help, that would be great.

Database is pure access 2007 (not using SQL as a BE)

Regards
Mark

 

[Douglas J. Steele]

Front-ends bloat as they're used. Regular compacting is a recommended
activity. How much bloat are you experiencing?

 

[Mark344]

Thanks for your message and quick response.

The FE is around 5Mb (when compacted) and grows to cirica 6Mb after just
15mins of usage! The other day, it grew to 18Mb after just 2 day's; is this
normal?

Rergards
Mark

 

[Douglas J. Steele]

What kind of "usage"? Are you still developing in it? (I believe that Access
makes copies of objects when you're working on them, so that bloat is
vritually guaranteed.) Are you running make-table queries? Are you working
with large recordsets?

 

[Mark344]

Usage is just general stuff, oen/close forms, run reports, maybe edit a
record etc (nothing major).

Large recordset? Depends what you call lage, the main table (containing
Contacts Info) has 51 fields! Most of those are simple 'Check Box' fields
(Yes/No); it that too large?

I've got circa 6 queries tables running (based on the Contacts Table).

Still in development? Not really, system now being used live by the charity.
I make a few odd changes now and again but nothing major.

Thanks for your help.

Cheers
Mark

 

[Douglas J. Steele]

Afraid nothing jumps out, then, as a logical explanation for the bloat.
Still, since a database can be up to 6 Gb, 18 Mb is nothing to worry about.

One comment, though. A 51 field table consisting of mostly boolean fields is
likely poorly designed. I'm betting you're "hiding" metadata in the names of
the fields, and that most likely you should have a second related table
consisting of a FK that points back to the main table, a field that
indicates what the boolean field determines, and the boolean field. In other
words, rather than having fields named "Stage1Completed", "Stage2Completed"
and so on in a single row, you'd have multiple rows in a second table where
ParameterName would be Stage1Completed on one row, and Stage2Completed on
another row.

 

[David W. Fenton]

Usage is just general stuff, oen/close forms, run reports, maybe
edit a record etc (nothing major).

Large recordset? Depends what you call lage, the main table
(containing Contacts Info) has 51 fields! Most of those are simple
'Check Box' fields (Yes/No); it that too large?

I've got circa 6 queries tables running (based on the Contacts
Table).

Still in development? Not really, system now being used live by
the charity. I make a few odd changes now and again but nothing
major.

Are users sharing a single front end, or does each user have an
individual copy? If the former, that's the culprit.

 

[Mark344]

Many thanks for the advise.

Regards
Mark

Afraid nothing jumps out, then, as a logical explanation for the bloat.
Still, since a database can be up to 6 Gb, 18 Mb is nothing to worry about.

One comment, though. A 51 field table consisting of mostly boolean fields is
likely poorly designed. I'm betting you're "hiding" metadata in the names of
the fields, and that most likely you should have a second related table
consisting of a FK that points back to the main table, a field that
indicates what the boolean field determines, and the boolean field. In other
words, rather than having fields named "Stage1Completed", "Stage2Completed"
and so on in a single row, you'd have multiple rows in a second table where
ParameterName would be Stage1Completed on one row, and Stage2Completed on
another row.

 

[Mark344]

Cheers for the input David.

Yes, it's the former!

Once I've finished tinkering with their system I'll make sure everyone has a
local copy of the FE.

Regards
Mark