Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1

    Question Unanswered: Compact an Individual Table

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  4. #4
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Thank you for the advice guy's,

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MarkWhyte
    I need to bring into a Data store table circa 250K records at a time, on different products we sell
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •