database size growing faster than expected

[Paul James]

I have an Access 97 database which, when compressed, is about 65 megs in
size. After two hours of manual keyboard data entry, the size of the
database is 131 megs in size. Is there anything I can do to avoid this?
That is, to keep the size of the db from exploding so quickly like this?

A note about the background of this file: Last week there was a problem
with the system tables, and we were getting error messages like "Operation
invalid without a current index" and " " isn't an index in this table. So
I took the following steps:

1. Compressed the file using JetComp.exe;
2. Created a new blank database;
3. Imported all the objects from 1 into 2, and
4. Compressed and Repaired the new database from Tools - Database
Utilities - Compact/Repair.

All of the functionality seems to work well, but I'm concerned about the
seemingly unwarranted growth in the size of the new file. Is there anything
I can do to overcome this problem?

Thanks in advance.

Paul

 

[Albert D. Kallal]

"Paul James" <[email protected]

1. You said that code that does not close recordsets is a possible cause of
the problem I'm having. I'm just beginning to learn data access objects,

Me.RecordsetClone.FindFirst "[LicenseeID] = '" & Me![ID] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

I don't know if this code could be a problem, but if so, what code could I
use to closed the recordset (if it's open).

The above is not a problem, since your code does not open the above
recordset. The above is not ting to worry about (only your code that
actually creates and opens a recordset is to be watched).

2. You suggested eliminating temp tables. By "temp" tables, do you mean
tables that are created by a make table query or VBA code, then deleted with
a delet query or VBA code?

yes, in fact any code that creates a record, and then deletes a record is a
problem. So, it is not just temp tables, but creating, and then DELETING
records. Records when created take up disk space, but that space is NOT
given back when you delete. So, no doubt the above is the source of
problems. Disk space is returned when you compact. Remember, even 15 years
ago old dbase did not give deleted record space back, and you had to issue a
pack command.

Are you saying that I
should eliminate all such tables and instead replace them with select
queries?

Yes, most certainly (if you can!). Of course, often it can be more work, or
difficult to avoid temp tables. I mean, it is often much easer to just throw
a bunch of records to a temp table and work on them. However, you want to
avoid this if possible. Often, due to performance reasons, or complexity you
can not avoid temp tables..but it is a very important issues.

You can't always be perfect, but when you can avoid temp tables, or temp
records, you should.

And, if a lot of processing with a lot of temp records needs to be done,
then the best solution is to use a temp.mdb file outside of the application
that gets created each time, and then deleted. (this solve any type of bloat
problem rather nicely).

Tony' has a exmple on how to create a temp.mdb file, but if you can just
start using some selects querys (or even in-line sql), then I go that way
for sure..

http://www.granite.ab.ca/access/bloatfe.htm

 

[Paul James]

Great info, Albert. Thank you for explaining how adding and deleting
records and using temp tables can bloat a database. I think that's what's
going on with mine, so I'm going to try to figure out a way to eliminate
those operations.

Two followup questions about opening and closing a recordset:

1. I'm not entirely sure when a recordset is open. Does this occur only
when you use the OpenRecordset method? And if I located all the instances
in my code where that method was applied, would that show me all the places
where I had an open recordset?

2. How do I close a recordset?

Thanks

 

[Albert D. Kallal]

Two followup questions about opening and closing a recordset:

1. I'm not entirely sure when a recordset is open. Does this occur only
when you use the OpenRecordset method?

Yes, you only need worry about recordsets that YOU open.

And if I located all the instances
in my code where that method was applied, would that show me all the places
where I had an open recordset?
Yes.


2. How do I close a recordset?

rstMyRecordSet.Close
set rstMyRecordSet = Nothing

 

[Paul James]

Thanks for all your help on this, Albert.