INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Bloat problem related to table creation/deletion

Bloat problem related to table creation/deletion

(OP)
hello to all

I am working on a database that involves looping through code that invlove the creation and deletion of large tables.  Today I had a big crash.  I realized that my mdb file, usually around 5 Mb in size, had bloated to 1.99 Gb.  I'm using Access 2002, where I believe the max file size is 2Gb, so I'm sure this was the cause of my problem.

My backups got me out of a jam, but I'm hoping to prevent this sort of thing in the future.  Compacting would likely help, but that would prevent me from running bulk operations - I'd have to frequently stop the code, then compact.

Any clues?  Is there a proper way to create then delete queries and tables without having this bloat problem?

Thank you
Vickyc

RE: Bloat problem related to table creation/deletion

What are you trying to accomplish with the creation and deletion of tables?  Yes that will bloat the database.  If possible, do not add and delete tables.  Have skeleton tables that you insert into and delete from.  Put these tables in a separate database and bring them in as linked tables.  This will help avoid corruption to the main data.
  Also why are you looping through code and not running queries to and and delete records?

RE: Bloat problem related to table creation/deletion

(OP)
MajP - good advice.  I got trapped into the create/delete tables routine because the created tables had names based on the test being done.  That will be changed. I'll take up your skeleton tables idea and use queries for all but the final output.  

In a few places, I'd used tables instead of queries because I needed to INSERT INTO something with an Autonumber ID field to act as a PK.  I suspect this will still give me a few probs.

Thanks again
Vicky

RE: Bloat problem related to table creation/deletion

Quote:


tables had names based on the test being done  
Probably better to simply have a field for this identification
testName

Quote:


I needed to INSERT INTO something with an Autonumber ID field to act as a PK
Should still be able to do that with an append query into your skeleton.  I was just suggesting that you normally do not have to loop through code to do this.  Often people use recordsets when it is far more efficient to use sql.  The general rule is only use recordsets when it can not be done through SQL.  (Obviously there is exceptions)

RE: Bloat problem related to table creation/deletion

(OP)
hi again - I'm now using the 'skeleton' tables, and this is taking care of the bloat problem.  Thanks again for the suggestion.  BUT, I now remember why I was driven to create-then-delete Temp tables in the first place.  Most of these Temp tables are large, but, in the end, just a few records are sent to a permanent Log table.  One of the Temp Tables uses an autonumber field as a key (called SchedID), and I need this autonumber to reset to 1 each time the table is invoked. (that's why I created-then-deleted).  I know that compacting the database will reset the autonumber, but - HERE'S THE PROBLEM - I run the whole process multiple times in a loop.  It is important that I be able to study results in the permanent Log table and note instances where SchedID values are equal.  So... compacting solves the autonumber-reset problem, but I'm not sure if it's safe to compact from code within a loop.

Hope this makes sense.  Any comments welcome.
Vickyc

RE: Bloat problem related to table creation/deletion

Make your own auto incrementing field instead of using autonumber.
If you are inserting record by record this should be very simple.  If you are using a query to do bulk insert then you can create an ID by using a ranking query.

RE: Bloat problem related to table creation/deletion

(OP)
Yes, I'm doing a bulk insert, so I'll try the Ranking query idea.  I'm hoping there won't be speed issues, because this table is particularly large.

Thanks for your assistance!

RE: Bloat problem related to table creation/deletion

We had a similar problem and it was solved when we split our database. We too use skeleton tables.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close