Hi , I'm using Access 2003,

Q. I was wondering is it possible to compact an individual table within a database?

Reason for asking, I need to bring into a Data store table circa 250K records at a time, on different products we sell

Basic procedure is:

Upload product ‘A’ in to the Data Store tbl, various procedures take place and the resulting outputs are stored in a temp table for product ‘A’.

The data held in the Data Store tbl is then deleted; the procedure is repeated for the remaining products.

My issue is the database can grow considerably in size, so I was wondering if you can compact the Data Store tbl, on each loop of the products, to keep the database size down.

Thanks

IM not aware of being able to selectively comapct and repair a table, tios either all or nothing.

I suspect your problem is the creation fo temp tabels, deleting rows then adding rows in the master product table.

is their not a better algolrhtym, which updates rows ars required, deletes and adds if update is not possible.

the alternative is tor run this as a batch process overnight and then rebuild your indexes before first use.

a crazy workaround: use an external temporary .mdb just for this table. from VBA you can CreateDatabase, CREATE TABLE, link to a table, DROP the linked table, kill the temporary .mdb

is delete the "ultimate compact" ?

izy

Thank you for the advice guy's,

Have you done some trend analysis to see when you will likely hit Access (JET's) limit of 2GB? Quarter million rows imported at a time (and of course the width of the row is also a consideration) is really heading towards (and possibly exceeding) JET's capabilities.

pootle flump ur codings are working excelent.