Hi Don,
In all Access version (Access 97, Access 2000, and Access 2002), deleting
objects (including indexes) from a database does not automatically cause
the database to shrink in size. This is because, when an object is deleted,
the space that it occupies is marked as available. However, the size of the
database does not shrink.
If you are deleting objects from a database and you want the database to
return to its previous size, you should compact the database.
One of the features beginning from Microsoft Access 2000 is compact on
close. When
you use this feature, Access automatically compacts the database each time
that you close it. To use the compact on close feature, follow these steps:
1. On the Tools menu, click Options.
2. Click the General tab.
3. Click to select the Compact On Close check box, and then click OK.
One workaround could be programmatically compact the database after you
delete and re-create the indexes; one method is to use ADO.
HOWTO: Compact Microsoft Access Database via ADO
http://support.microsoft.com/support/kb/articles/q230/5/01.asp
In addition, as TC mentioned, perhaps you can consider removing some
un-necessary indexes to avoid deleting and creating repeatedly?
Hope it helps.
Sincerely,
Alick Ye, MCSD
Microsoft Online Partner Support
Get Secure! -
www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Don Culp" <
[email protected]>
| Sender: "Don Culp" <
[email protected]>
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| given the number of records in my db (500,000; >100
| fields), it grows about 2-3 MB, which is the amount of
| space that an index requires...in 97 it would not grow at
| all after the characteristic database bloat -- which
| means to me that it was reusing the space released by a
| deleted index...but in XP it continues to reserve new
| space with every new index
|
| >-----Original Message-----
| >
| >| >
| >> In order to satiate your curiosity, this is done when a
| >> conversion of data is taking place in order to provide
| >> the best possible performance of joins and searches
| over
| >> tables that originally consist of up to 150 fields and
| >> hundreds of thousands of records. Since Access is
| >> limited to 32 indexes, and since some fields only need
| >> indexed once and then discarded, and since updating an
| >> indexed field that is not subject to search or join
| >> criteria has a negative performance aspect, blah, blah,
| >> blah ...let's just assume that after 10 years of
| >> development this is done for the correct reason and
| done
| >> as conservatively and efficiently as possible.
| >
| >Huh? You need to create & delete indexes because you
| can't have more than 32
| >of them simultaneously??
| >
| >Don, that does not suggest that things are being
| done "for the correct
| >reason and as conservatively and efficiently as
| possible". It suggests that
| >there is something wrong with the design of your tables!
| >
| >In three times "10 years of development", I have found
| very few cases where
| >it was necessary to add *any* extra indexes, apart from
| the ones on the
| >primary key of each table. And I have never found it
| necessary to create &
| >delete indexes programatically.
| >
| >But that is not to say that I am right, and you are
| wrong
| >
| >Can you say more about your table structures?
| >
| >TC
| >
| >(snip)
| >
| >
| >.
| >
|
