Solved Does It Affect My DB in Any Way If I Delete Temporary Tables after Creating and Using?

[Pac-Man]

Hello,

I need to create a temp table for my one subroutine to work. I have a sub on form load even which checks if temp table exists or not. If it doesn't, then it creates it. Should I delete the temp table while closing the form or let it remain for next time usage? Will creating and then deleting and repeat the same cycle every time form is loaded and closed bloat the DB?

Best Regards,
Abdullah

 

[MajP]

You should create an external db and link to it. Then delete when done. I think there is an example here

Creating and using Temporary Tables in Microsoft Access | Experts Exchange

Learn more about Creating and using Temporary Tables in Microsoft Access from the expert community at Experts Exchange

www.experts-exchange.com

 

[Galaxiom]

Will creating and then deleting and repeat the same cycle every time form is loaded and closed bloat the DB?

Just adding and deleting records from any table will cause bloat. The space can be recovered with Compact & Repair.

I recommend temporary tables be held in a disposable database I call a Side End, as opposed to the Back End where the data resides and the Front End where the queries forms and reports are held. Then there is no need for the Compact & Repair the Front End which also wipes out the stored query plans

 

[NauticalGent]

Here is a thread that address the effects of frequently deleting tables. In a word: "Bloat"

Mythbusters - Make Table vs Append Query

In the spirit of the other mythbuster demos available here, such as comparing the speed between a WHERE or HAVING query, I would like to submit the attached demo. There has been long argument regarding which approach creates more bloat for a database, using a Make-Table query or using an...

www.access-programmers.co.uk

MajP's response (which I would recommend) is what one of our members has named a "Side End" DB.

 

[NauticalGent]

In addition to giving cool names to concepts like Side End DB's, he his a pretty fast responder too....

 

[isladogs]

As already stated, either creating and deleting temp tables or appending to existing temp tables then emptying the tables will cause a similar amount of bloat.
Usually create/delete is faster but if you do so repeatedly there is a risk of instability and you may end up with an error such as unrecognised database format or file not found. For that reason I suggest appending and emptying tables instead.

Whichever method you choose, I agree with the idea of using a separate 'side end' DB for this. If corruption occurs neither FE nor BE will be affected. Just create a new copy of the side end

 

[moke123]

There is a very tiny bit of bloat in the MySysNameMap table when linking and unlinking from tables in a Temp Database.
Much less than using temp tables in the main database.

Here's an example which is based on code originally from TheDataBaseGuy.

 

[Pac-Man]

You should create an external db and link to it. Then delete when done. I think there is an example here

Creating and using Temporary Tables in Microsoft Access | Experts Exchange

Learn more about Creating and using Temporary Tables in Microsoft Access from the expert community at Experts Exchange

www.experts-exchange.com

Thanks a lot for the link. I'll check and get back here. Would side end db method not slower?

 

[Pac-Man]

Here is a thread that address the effects of frequently deleting tables. In a word: "Bloat"

As already stated, either creating and deleting temp tables or appending to existing temp tables then emptying the tables will cause a similar amount of bloat

I've read the mythbuster thread shared by @NauticalGent and also read the comments by @isladogs in the post. Thanks a lot @NauticalGent and @isladogs for the link and explaining.

 

[Pac-Man]

There is a very tiny bit of bloat in the MySysNameMap table when linking and unlinking from tables in a Temp Database.
Much less than using temp tables in the main database.

Here's an example which is based on code originally from TheDataBaseGuy.

Thanks for the db @moke123.

 

[MajP]

Would side end db method not slower?

I think that article proposes using a "side end" table. I was actually looking for an example where you create a "side end" database and then delete the database when done. This Avoids any issue with corruption and bloating. It is just a temporary database and fresh each time. That example seems to require you having the side db before hand and it does not seem to create and delete the whole db. However, there is some good code in there.
Somewhere on this forum there are examples of creating the temporary db, and the comments were positive as a good approach.
I did not look at @moke123 post, but I do think the example was from the @theDBguy . I need to take a look.

 

[NauticalGent]

Thanks a lot for the link. I'll check and get back here. Would side end db method not slower?

I have tried this method before, mostly just to do it to see how it works. Naturally there is a few seconds to make the DB and the tables, but the extra time is negligible and the benefits are great. - mostly the avoidance of bloat, the loss of query plans from Compact/Repairs and the instability of frequent deletion of table objects.

 

[MajP]

OK took a look and that is the example I was looking for. Have it bookmarked now. Thanks.

 

[moke123]

here's a link to DBG's original temp database demo http://www.accessmvp.com/thedbguy/demos/tempdb.php

I usually create the temp database when I open the App and then create the tables on demand when needed. I use template tables as it makes it easy to update them when needed without having to do any coding. I disconnect any tables and delete the temp database on closing the main app.

 

[Gasman]

I get 'Unrecognised database format' in 2007, which is pretty normal for me these days.

 

[gemma-the-husky]

Out of interest.

I use temporary tables in front ends. I would rather given them keys and indexes to manage the contents, and just "delete * from temptable1" to empty them, rather than re-create tables.

I never use side tables, and I have never had an issue with excessive size of a front-end.

 

[Pac-Man]

I think that article proposes using a "side end" table. I was actually looking for an example where you create a "side end" database and then delete the database when done. This Avoids any issue with corruption and bloating. It is just a temporary database and fresh each time. That example seems to require you having the side db before hand and it does not seem to create and delete the whole db. However, there is some good code in there.
Somewhere on this forum there are examples of creating the temporary db, and the comments were positive as a good approach.
I did not look at @moke123 post, but I do think the example was from the @theDBguy . I need to take a look.

I have tried this method before, mostly just to do it to see how it works. Naturally there is a few seconds to make the DB and the tables, but the extra time is negligible and the benefits are great. - mostly the avoidance of bloat, the loss of query plans from Compact/Repairs and the instability of frequent deletion of table objects.

Thanks a lot again. I misunderstood, initially I was thinking that it will be on network that is why I thought it would be slower but now I get it. It could be on user's PC.

 

[gemma-the-husky]

If you want to keep temporary tables in the front ends, and also control bloat, then just check the size of the front end at startup, and above a certain size remind the operator to install a clean version of the front end.

 

[Pac-Man]

If you want to keep temporary tables in the front ends, and also control bloat, then just check the size of the front end at startup, and above a certain size remind the operator to install a clean version of the front end.

Thanks a lot @gemma-the-husky, that is also doable. Thanks. I've two options now, side end db and your suggestion. I'll check both and will decide which way to go. Thanks again everybody. Stay healthy.