Bloating

[Guest]

I have an Access 97 Database, about 500K. I convert it to Access 2002
(compresses to 335K) and run the program. It "BLOATS" to corruption size at
about 2.097 Gig.

I have explicitly closed all record sets (rs.close) (to minimize bloating
per technical support suggestions).

HOW do I stop BLOATING?

Thanks

Ross

 

[John Vinson]

I have an Access 97 Database, about 500K. I convert it to Access 2002
(compresses to 335K) and run the program. It "BLOATS" to corruption size at
about 2.097 Gig.

I have explicitly closed all record sets (rs.close) (to minimize bloating
per technical support suggestions).

HOW do I stop BLOATING?

Are you storing graphical images in tables? Doing so is a notorious
source of bloat. Other causes: temp tables (consider using the
CreateDatabase method to create a "scratch" .mdb file for them),
frequent cycles of emptying tables and refilling them, whole-table
update queries.

See Tony Toews' suggestions at

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

as well. If you have not already done so, *do* split the database so
that the tables are in a backend and the user interface in a frontend,
and distribute individual copies of the frontend to each user.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,

No images, just data. Why does Acc97 grow 15% and Acc2002 grow 600% with
the same program?!

 

[david epsom dot com dot au]

No images, just data. Why does Acc97 grow 15% and Acc2002 grow 600% with

the same program?!

There is a problem. For example, with some versions of Jet,
some actions may create a new page in the database for each
new action. What are you trying to do?

(david)

 

[Guest]

The program is a financial program that deletes 470,000 records from the main
table, then imports (appends) from ODBC oracle tables, a new set of 470,000
new records (new month). It then does calculations on these new records thru
a series of public procedures.

There are about 250 pages of code. I am presently trying to isolate the
procedure(s) that are causing bloating.

Thank you for your interest in helping.

PS: I found this question 3 pages back.

 

[John Vinson]

The program is a financial program that deletes 470,000 records from the main
table, then imports (appends) from ODBC oracle tables, a new set of 470,000
new records (new month). It then does calculations on these new records thru
a series of public procedures.

Well, that will cause half a million records worth of bloat right
there. The space occupied by the deleted records will NOT be reused.

I'd really suggest using the CreateDatabase method (see the VBA online
help) to create a new, vacant backend, and create this table in that
new backend. Link to it from your database to do the calculations, and
then delete the backend .mdb file when you're done with it, rather
than emptying and refilling the table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I did this last night (the temp table thing).
1. Create a new temp database,
2. Export the big table into the dbtemp (db = 200K at this point).
3. Populate the empty fields in the 470,000 records in the temp table.
During the population process (which takes about 2 hours), the temp database
grows from 200,000 to 2,097,000 with no new records added or deleted. Only
15 fields in each record are populated with data calculations.

I am using .edit and .Update on each record.

 

[John Vinson]

I did this last night (the temp table thing).
1. Create a new temp database,
2. Export the big table into the dbtemp (db = 200K at this point).
3. Populate the empty fields in the 470,000 records in the temp table.
During the population process (which takes about 2 hours), the temp database
grows from 200,000 to 2,097,000 with no new records added or deleted. Only
15 fields in each record are populated with data calculations.

I am using .edit and .Update on each record.

Ah. You didn't SAY that.

Record by record updates is both slow and prone to bloat. If there is
ANY way that you can do the population using an Update query, or (even
better) an Append query containing the calculations, it's going to
probably be both faster and less bloating. Depending on the
calculations, you might be able to do them directly in the Append
query as expressions, or you may need to write a custom VBA function
and use a call to it as a calculated field in an Append query.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Manfred Zenz]

Ah. You didn't SAY that.

Record by record updates is both slow and prone to bloat. If there is
ANY way that you can do the population using an Update query, or (even
better) an Append query containing the calculations, it's going to
probably be both faster and less bloating. Depending on the
calculations, you might be able to do them directly in the Append
query as expressions, or you may need to write a custom VBA function
and use a call to it as a calculated field in an Append query.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,
As you know, there is always a way. I just don't understand why this
"bloating" wasn't a problem in Acc97. I like recordsets for the control and
doing many tasks on one record at a time. I have many "functions" that do
the calculations.

I have found that update and append querys with tooo many calculations in
them can get bogged down also.

I will re-write the code and give it a try.

Thanks for your ideas.

Ross