Temp Table in Access / Manage Size

Question

I have an Access file with some data. To perform certain calculations , I need to create certain tables with calculated (generated) values from the Original data. The size of these temp tables is around 500 MB each criteria , I use it. This has to be done over and over again at least 50 times for various criteria.

Once the first part is completed - the temp table can be discarded. Problem is in Access even after deleting rows and dropping tables the file size still remains high. As a result after each pass the size still increases despite dropping the table. Soon the 2GB wall is hit.

Solution Tried : 1. using VBA - create a temp mdb file -> create table there -> link to original access file - save the generate values here -> use for calculations -> delete the file Do it all over again.

It is very slow Inserting records into this table. I beleive beacuse it is a Linked Table. Number of Inserts I would be doing is in hundred thousands.

Compact and Repair Current DB using VBA - No clear answer. Send Keys work - but Fail Proof.
Use a seperate accdb file 'control_file_ , which will do the work on the original access file , drop table , compact and repair and then do over again for the next criteria. Please advise VBA code for this. That continuity in the VBA code remains.

Split your database up so the tables are in SQL Server. You should be able to do more powerful queries via ODBC Passthrough, perhaps even without the temp tables. But if not, you can make the temp tables on the SQL Server. — ta.speot.is, Apr 22 '13 at 06:39

Renaud Bompuis · Accepted Answer · 2013-04-22T06:43:37.850

The easiest way I could recommend to solve the issue of compacting the database would be to simply use the code I published a while ago for Restarting and compacting an MSAccess database programmatically.

Otherwise, using a separate temp database is probably the best. One thing regarding performance though: using a separate linked database is not slower if you take care of keeping a linked table always open.
What makes operations slow is the fact that the database engine needs to create and delete the database lock file too often. If you keep a linked table open (open a recordset to a dummy table and do not close it), then the lock file will remain and you'll get proper performance.

score 1 · Answer 2 · answered Apr 29 '13 at 15:48

The compact and repair is impossible in a current database in Access 2007. All techniques you saw about this either are wrong or refer to older versions of Access.

I had the same problem as you had and the best way is indeed to split your DB so that you could perform your calculations in the BE and compact and repair that temporary DB. Furthermore, your problems regarding performance will in fact be solved using the idea from Renaud.

RBILLC · Answer 3 · 2018-04-09T17:18:31.180

I have encountered the same issue where my database is bloating on raw data import. Instead of splitting the database and compacting the backend routinely, I decided to use the database object (DAO) to create a temp database, import the data, query from that temp database and then delete it. Base code shown below:

Sub tempAccessDatabaseImport()
    Dim mySQL As String
    Dim tempDBPath As String
    Dim myWrk As DAO.Workspace
    Dim tempDB As DAO.Database
    Dim myObject

    'Define temp access database path
    tempPathArr = Split(Application.CurrentProject.Path, "\")
    For i = LBound(tempPathArr) To UBound(tempPathArr)
        tempDBPath = tempDBPath + tempPathArr(i) + "\"
    Next i
    tempDBPath = tempDBPath + "tempDB.accdb"

    'Delete temp access database if exists
    Set myObject = CreateObject("Scripting.FileSystemObject")
    If myObject.FileExists(tempDBPath) Then
        myObject.deleteFile (tempDBPath)
    End If

    'Open default workspace
    Set myWrk = DBEngine.Workspaces(0)

    'DAO Create database
    Set tempDB = myWrk.CreateDatabase(tempDBPath, dbLangGeneral)

    'DAO - Import temp xlsx into temp Access table
    mySQL = "SELECT * INTO tempTable FROM (SELECT vXLSX.*FROM [Excel 12.0;HDR=YES;DATABASE=" & RAWDATAPATH & "].[" & WORKSHEETNAME & "$] As vXLSX)"

    'DAO Execute SQL
    Debug.Print mySQL
    Debug.Print
    tempDB.Execute mySQL, dbSeeChanges

    'Do Something Else

    'Close DAO Database object
    tempDB.Close
    Set tempDB = Nothing

    myWrk.Close
    Set myWrk = Nothing

    'Delete temp access database if exists
    If myObject.FileExists(tempDBPath) Then
        'myObject.deleteFile (tempDBPath)
    End If
End Sub

Temp Table in Access / Manage Size

3 Answers3

Linked