Bloat problem related to table creation/deletion 1

[VickyC]

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

 

[MajP]

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?

 

[VickyC]

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

 

[MajP]

tables had names based on the test being done

Probably better to simply have a field for this identification
testName

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)

 

[VickyC]

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

 

[MajP]

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.

 

[VickyC]

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!

 

[MajP]

If you cannot do that then you can reset the autonumber without a compact and repair. There are a couple of ways. But if you delete the field and add it back that is one way.

http://www.access-programmers.co.uk/forums/showthread.php?t=60177

 

[SeadnaS]

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