Proper way to create a temporary throw away table

[OasisUnknown]

Hello Community,

I have an access database that I am working on and is still in the beginning phases.

What I am dealing with is my database file size growing exponentially. I have narrowed this down to how I am creating and dropping a temp table.

here is what is happening.

I have an external database that I am accessing (I have no access to its data other than to pull queries and export that data to an excel file)

this file has many thousands of records that I need to analyze and build reports off of and this data can change but the number of columns will stay fixed at 78.


So what my plan was to export a fresh pull of the data from the external database into an excel file and then import that data into my access database with my custom reports and quiers built off that data.

what I am doing is I have a button for uploading this excel document and I select the excel file with the file dialogue

and then I import that data into a temporary table (I do this so I can error check it before I formally put it into my database)

the error check counts the number of columns and compares that to how many are required for import (78)

then it error checks the field names to ensure that it is in the correct order (col A is not switched with Col B)

after the error check is done I would delete all the data from my permanent table in my database
then transfer the new data from my temp table to the permanent table.

and then I drop the temp table because I don't need it any more until I import my next pull


but I have noticed through the many tests of the code that my database goes from around 16 megs to upwards of 100+megs
and then I need to compact and repair and then it goes back down to 16 megs


from all my research I have done online this seems to be because even though access drops the table the memory is not really released until a compact and repair is done.


my question is 2 part.

Am I creating and using a temp table in the correct way.

and 2.
if I am can I automatically compact and repair after the table is dropped or release that memory space manually somehow.


Thank you in advance for reading.

 

[xenou]

It's a lot of code to handle a simple task but it's all here:
MCCompact_DB

The main thing was to have lots of options (but in general there is also a default for everything).

 

[Joe4]

Looks cool, too bad I probably won't be able to use it (trying to get any kind of add-ins/downloads to happen in our environment is a difficult task with our Data Security Team - it is the reason I can't use HTML Maker!).