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.

 

[Joe4]

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

You may not need to import the data at all. You can link Excel files as Access tables. If you do that, the data really resides in Excel and not Access, and won't "grow" your Access database size. You can use a Linked Table like any other Table in Access. It doesn't really matter if the data physically resides in Access or not.

So you may not need a Temp Table at all.

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

Access has a "Compact on Close" functionality that automatically compacts whenever someone who has exclusive access to the database closes it. It can be found under Access Options -> Current Database -> Compact on Close. I have used this many times before. Just note that it can be temperamental at times.

 

[xenou]

You do need to compact and repair - that's just how it is with Access. Experiment with creating the temp table in a second database so that the compact is only needful for the one table. In that case the compact might go quicker and your first database won't be affected by the sudden increases in size. You can always link to the second DB and use its tables the same as any other tables. There is no right or wrong way here.

 

[OasisUnknown]

I think that I am going to try the linked excel file. I don't expect the database to grow to much after the actual import of the data because the size of the data will always remain about the same time because the data will be about the same size with each pull. I just need it to be fresh new information.

(It's basically a customer list)
And customers will come and go frequently I just need the data associated with those customers.

So from my logic that if I delete the data and then import it fresh. The new data is just filling the hole left by the old data.

The problem with the temp table as is and how I currently do it

The data gets imported and it uses memory and then gets dropped.

Then a new pull is created and instead of filling the the hole it creates more memory and then drops. This just compounds until compact.


But if I link the excel sheet the the data lives outside the database not affecting the memory.

And I can do the same error checks with the linked workbook and then import it from there jnto my database then I won't have to run compact as often and risk corruption.

We will see at least as I test it.

 

[xenou]

It will work. Beware that the linked workbooks will be locked while in use by access (which is whenever you have Access open). You also forgo the benefit of primary keys, indexes, and strict datatyping, should those have any relevance. Personally, I wouldn't call the need to compact and repair a "problem". It's standard procedure with Access. If your database is properly split you should have no issues (I've never corrupted a database by compacting and repairing it in 10 years using Access).

 

[Joe4]

If your database is properly split you should have no issues (I've never corrupted a database by compacting and repairing it in 10 years using Access).

I have had the occasional issue with the "Compact on Close" feature, especially in an automated environment. Occasionally, it doesn't work, so it leaves a locking database open, and creates a copy of the database. So I just have to bring up Task Manager, kill the Access session, then delete the lock and things then usually work fine again.

I had a few that kept doing this (I think they somehow became corrupted, though I cannot say this is what caused the corruption). So I just imported them to new databases and the issue went away.

By just like xenou says, I have never had this issue manually compacting and closing. And if you are using linked tables, the frequency with which you will need to do compacting and closing will be much less than if you use temporary tables (as the database will not grow in size nearly as fast).

 

[xenou]

I guess I should mention I don't use the compact on close feature.

 

[Joe4]

Its a nice concept, especially if you have Access database that are used in automated processes that are never manually opened/used by users (or if you have a single-user database, and you don't trust that user to run Compact and Repair regularly). But it tends to flake out occasionally.

 

[xenou]

I wrote a program in Access to handle all my C&R's on a schedule (normally once a week) during the night, and also get backups at that time.

 

[Joe4]

I wrote a program in Access to handle all my C&R's on a schedule

Does your program actually Compact & Repair other Access databases?
If so, I would be very interested in seeing what that code looks like.