Avoiding database bloat, etc - should I leave DAO recordsets open?

[David Anderson]

I have an Access 2003 application that makes a lot of use of DAO for
handling the data. My primary form is designed so that all the required
recordsets are created when the form is opened, i.e. the Form_Open sub
contains the following code:

Set MyDB = DBEngine.Workspaces(0).OpenDatabase(strDBName)

Set rstUsers = MyDB.OpenRecordset("Users")
Set rstEntrants = MyDB.OpenRecordset("Entrants")
Set rstEntrySummary = MyDB.OpenRecordset("EntrySummary")
Set rstEntries = MyDB.OpenRecordset("Entries")
Set rstPayments = MyDB.OpenRecordset("Payments")
etc, etc

Rightly or wrongly, none of these recordsets are closed until the form
closes. My Form_Close sub contains the following code:

rstUsers.Close
rstEntrants.Close
rstEntrySummary.Close
rstEntries.Close
rstPayments.Close
etc, etc

Unfortunately, my MDB file steadily grows in size as this form is being
used. Even worse, the app occasionally gets really confused and starts to
present the data of one person when viewing another. This is always fixed by
a Compact & Repair.

I am speculating that my practice of leaving the recordsets open all the
time while the form is open may not be a good idea and may be the reason for
my problems. I would be grateful for any advice on this matter.

David

 

[Douglas J. Steele]

I don't believe that closing the recordset differently will change whether
or not there's bloat.

Your comment "the app occasionally gets really confused and starts to
present the data of one person when viewing another" makes me wonder whether
your users are all using the same database. They shouldn't be: all Access
applications should be split into a front-end (containing the queries,
forms, reports, macros and modules) linked to a back-end (containing the
tables and relations). Only the back-end should be on the server: each user
should have his/her own copy of the front-end, ideally on his/her hard
drive.

 

[David Anderson]

Hi Doug,
That isn't the cause in this case. My app is already split into a front and
back end. All the tables are in a back end accessible to the users via a
network.

David

 

[Douglas J. Steele]

And with each user having his/her own copy of the front-end, you're still
getting one person getting another person's data?

 

[David Anderson]

This sort of confusion only happens after continually using the form to add
new people and lots of associated data for at least half an hour, by which
time the front end MDB file will have significantly grown in size. The exact
symptoms of the problem seem to vary but a C&R always fixes it. In other
words, it appears to be a presentation problem for the controls on the form.
The underlying data tables are not being corrupted.

Could some sort of memory leak be the source of this problem? This is not an
area where I have much knowledge.

David

 

[Douglas J. Steele]

I've never heard of a memory leak manifesting itself in this way, but I
suppose it's possible.

Are you making use of complex queries, or temporary tables? That could lead
to the bloating. Realistically, I don't see the need to compact the
front-end though: why not simply replace it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This sort of confusion only happens after continually using the form to
add new people and lots of associated data for at least half an hour, by
which time the front end MDB file will have significantly grown in size.
The exact symptoms of the problem seem to vary but a C&R always fixes it.
In other words, it appears to be a presentation problem for the controls
on the form. The underlying data tables are not being corrupted.

Could some sort of memory leak be the source of this problem? This is not
an area where I have much knowledge.

David

 

[David Anderson]

There are no temp tables. What sort of query do you call a "complex" query?

I guess your suggestion to "simply replace" my front end database is based
on the fact that it contains no data. I assume you mean that I could
overwrite the bloated version with a compacted copy stored elsewhere. Is
this correct? Is there any advantage to doing this versus a C&R (which only
takes a few seconds)?

David

I've never heard of a memory leak manifesting itself in this way, but I
suppose it's possible.

Are you making use of complex queries, or temporary tables? That could
lead to the bloating. Realistically, I don't see the need to compact the
front-end though: why not simply replace it?

 

[Douglas J. Steele]

Yes, simply overwriting the bloated version with a "gold" copy is what I was
talking about. Any time you compact a database, there's always a slight risk
of that you could corrupt the database. Replacing it with a known good copy
eliminates one potential cause for worry.

Afraid I don't have any guidelines to what constitutes a complex query, but
Access does sometimes create temporary intermediary tables in order to
complete queries. Are your tables well indexed (i.e.: indexes exist that are
appropriate for the queries you're running?) By this, I mean that if you're
always querying table 1 based on Fields A and B, do you have an index on
fields A and B for that table?

 

[Clif McIrvin]

Hi Doug,
That isn't the cause in this case. My app is already split into a
front and back end. All the tables are in a back end accessible to the
users via a network.

David


message

Hi David-

My apologies if you answered Doug's point above and I missed it ... If
all users (or even "some" of the users) are sharing a networked copy of
the FE the symptoms you describe are not uncommon.

 

[David Anderson]

Clif,
No, the front end is not networked. A separate copy sits on each user's PC.
It's only the back end that is on the network.

David

 

[David Anderson]

Douglas,
My app has been in use all day today and there were little or no signs of
bloat or application confusion. Only one user PC experienced just two
glitches where the wrong user's data was displayed (fixed as before with a
C&R, as I have a button on the form to make this easy), but there was no
database bloat this time.

The difference from before is that I made a change so that all the
recordsets are closed and then immediately reopened each time a new person's
records are requested. I haven't fixed everything but I seem to have nailed
the bloat. Why this works is another matter.....

David