Clearing MS Access cache through VBA

[iqkh76]

I am uploading multiple Excel files through transfer sheet into Access table.

After each upload of files, multiple queries are triggered to get the final data from the input file.

Each file contains records on an average of 100,000 to 1,50,000.

So since I upload all the files by looping files in a folder. Hence, The size of database increases drastically and once activity is complete, it reduces to almost 50% less post compact and repair.

How can I do a compact and Repair on the go with the current db after each file processing is complete.

Thanks.

 

[Ranman256]

after import,
empty any non use tables via delete queries
then compact it via:
Application.CompactRepair sSrc, sTarget

sSrc = source db
sTarget = compacted target db
then you will need to rename the taget back to the original workname:
Name sSrc As sSrc & "uncompacted_.mdb"
Name sTarget As sSrc

 

[Cronk]

Or in the Options for the Current Database, check the Compact on Close setting.

 

[iqkh76]

Thanks Ranman,

When I tried using Application.CompactRepair sSrc, sTarget, I am getting error "Microsoft Access can't compact and repair the current database"

 

[arnelgp]

you need to put the code to another db.
on this db add an autoexec macro that will do the compacting.

on your original db, you must have some button to:
close your db
open the new db with autoexec + compact code.
after compacting, close the db with compact code.
re-open your db.

all this is done through vba.

 

[isladogs]

Just to clarify, you can no longer compact the CURRENT database using VBA.

From A2010 onwards, Microsoft removed the functionality listed by Ranman - probably because compacting 'on the fly' has been known to cause corruption.

If your database is being regularly 'bloated' by adding large number of records and then compacted regularly, it is DEFINITE that it will at some point get corrupted

Therefore I would suggest you ALWAYS make a backup first THEN compact

As others have said, you can still compact & repair using the toolbar or compact on close (NOT recommended for reasons stated above)
However, the safest way is to do what arnelgp suggested
i.e. compact a COPY of the db

The code below makes a backup copy of the db then compacts the backup

Public Function BackupFEDatabase()

On Error GoTo Err_Handler

'creates a copy of the current db (frontend) to the specified backups folder with date/time suffix

    Dim fso As Object
    Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
    Dim newlength As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    strFileType = Mid(CurrentDb.Name, InStr(CurrentDb.Name, ".")) 'e.g. .accdb
    strFilename = Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1)
    strFilename = Left(strFilename, Len(strFilename) - Len(strFileType)) 'e.g. JSONTest
    
    strOldPath = CurrentDb.Name
    strTempPath = Left(CurrentDb.Name, InStr(CurrentDb.Name, ".") - 1) & _
        "_TEMP" & Mid(CurrentDb.Name, InStr(CurrentDb.Name, "."))
            
    	'CR - the code below refers to a function GetBackupsFolder 
    	'Replace with your own folder name or function      
    	strNewPath = GetBackupsFolder & "\FE\" & _
         strFilename & "_" & Format(Now, "yyyymmddhhnnss") & strFileType    

        If MsgBox("This procedure is used to make a backup copy of the Access front end (FE) database.           " & vbCrLf & _
            "The backup will be saved to the Backups folder with date/time suffix                   " & vbCrLf & _
                vbTab & "e.g. " & strNewPath & "                            " & vbCrLf & vbCrLf & _
            "This can be used for recovery in case of problems    " & vbCrLf & vbCrLf & _
            "Create a backup now?", _
                vbExclamation + vbYesNo, "Copy the Access FE database?") = vbNo Then
                    Exit Function
        Else
            'copy database to a temp file
            fso.CopyFile strOldPath, strTempPath
            Set fso = Nothing
            
            strNewPath = GetBackupsFolder & "\FE\" & _
                strFilename & "_v" & GetVersion() & "_" & Format(Now, "yyyymmddhhnnss") & strFileType
                
            'Debug.Print strTempPath
            'Debug.Print strNewPath
            
            'compact the temp file
            DBEngine.CompactDatabase strTempPath, strNewPath
            
            'delete the tempfile
            Kill strTempPath
                
            DoEvents            
          
            'get size of backup  (this can be omitted if not wanted)
            newlength = FileLen(strNewPath) 'in bytes
             
            'setup string to display file size
            If FileLen(strNewPath) < 1024 Then  'less than 1KB
               strFileSize = newlength & " bytes"
            ElseIf FileLen(strNewPath) < 1024 ^ 2 Then  'less than 1MB
               strFileSize = Round((newlength / 1024), 0) & " KB"
            ElseIf newlength < 1024 ^ 3 Then 'less than 1GB
               strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 2), 1) & " MB)"
            Else 'more than 1GB
                strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 3), 2) & " GB)"
            End If
            
            DoEvents
       End If
     
       MsgBox "The Access FE database has been successfully backed up.                " & vbCrLf & _
        "The backup file is called " & vbCrLf & _
            vbTab & strNewPath & "                       " & vbCrLf & vbCrLf & _
            "The file size is " & strFileSize, vbInformation, "Access FE Backup completed"
    
Exit_Handler:
    Exit Function
    
Err_Handler:
    Set fso = Nothing
    If Err <> 0 Then
      MsgBox "Error " & Err.Number & " in BackupFEDatabase procedure : " & vbCrLf & _
          Err.description, vbCritical, "Error copying database"
    End If
    Resume Exit_Handler
    
End Function

 

[Mark_]

I am uploading multiple Excel files through transfer sheet into Access table.

After each upload of files, multiple queries are triggered to get the final data from the input file.

Each file contains records on an average of 100,000 to 1,50,000.

So since I upload all the files by looping files in a folder. Hence, The size of database increases drastically and once activity is complete, it reduces to almost 50% less post compact and repair.

How can I do a compact and Repair on the go with the current db after each file processing is complete.

Thanks.

Is your primary DB doing all of the imports or do you have a separate DB that handles this kind of work first, then adds to your main DB after it is all done?

If the first, I'd suggest moving the code and temporary tables to their own separate DB. This should avoid most of the issues associated with bloat/compact. For each upload you make a copy of "Import DB", use it, and once no longer needed you can delete. Should avoid the types of corruption issues these processes can otherwise cause.

 

[The_Doc_Man]

Darn, Mark_, ya beat me to it.

It would be possible to have a database of nothing but temp tables that you can open and close at will. You would have to manage the links, OR you could simply have a database object pointing to the temp-DB file. It IS possible to open tables referenced via a DB object as <object>.AllTables("tablename"), OR you can use the Search function here to show how to dynamically open and close tables in an external DB. In which case, you would open the temp DB, do a bunch of stuff, erase the temp tables, close the temp DB, and do a Compact & Repair on it, then delete the old file and rename the new file. It's a pain in the toches but you can write a subroutine to do it and then just call it at need.

 

[Cronk]

Alternatives to compact/repair are
(1) Maintain a master Temp db and delete the old working Temp db and replace it with a pristine copy of the master Temp db.
(2) Keep a set of unused temp tables in the FE then create a blank Temp db and export the blank temp tables from the FE to the Temp db.

I started off using method (1) but now use (2). Both processes are completely invisible to the user. All code is in the FE with links to the tables in the Temp db.
Both methods are quick (a second or two).

Should anyone be interested, here's an extract of my CreateTempDB routine

   If Len(Dir("C:\Data\SGHS\SportsTemp.MDB")) > 0 Then
               Kill "C:\Data\SGHS\SportsTemp.MDB"
          End If
      
   Set ws = DBEngine(0)
      Set db = ws.CreateDatabase("C:\Data\SGHS\SportsTemp.accdb", dbLangGeneral)
   
      '--Transfer temporary tables
      DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Data\SGHS\SportsTemp.accdb", acTable, "ctblFields", "tmpFields"
      DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Data\SGHS\SportsTemp.accdb", acTable, "ctblExcludeDates", "tmpExcludeDates"