1

I would like to compact DATABASE2 from a query within DATABASE1.

Situation: I have 2 Access databases, one with all the queries/logic, one with tables created from the first. Before I write from DATABASE1 into a table in DATABASE2 I use a query to delete the contents of the table. Since the table is large, I would like to also compact the database after the query deleting the content runs (ideally I would like to nest the query into one).

Anyone know if this is possible?

Using MS Access for Office 365 MSO (16.0....) 32-bit

Herb A
  • 11
  • 1
  • https://stackoverflow.com/questions/2831749/how-to-compact-an-ms-access-database-while-the-database-is-open-using-vba – Mitch Wheat Nov 03 '20 at 00:38
  • Does this answer your question? [How to compact an MS Access database, while the database is open, using vba](https://stackoverflow.com/questions/2831749/how-to-compact-an-ms-access-database-while-the-database-is-open-using-vba) – June7 Nov 03 '20 at 00:48
  • https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/dbengine-compactdatabase-method-dao – Andre Nov 03 '20 at 06:34

1 Answers1

1

Yes, you can do that. Create this function:

Public Function CompactDatabase(ByVal Path As String)

    Dim SrcName As String
    Dim DstName As String
    Dim Success As Boolean
    
    On Error GoTo Exit_CompactDatabase
    
    SrcName = Path
    DstName = Replace(Path, ".accdb", ".tmpdb")
    
    DBEngine.CompactDatabase SrcName, DstName
    Kill SrcName
    Name DstName As SrcName
    Success = True
    
    CompactDatabase = Success
    
Exit_CompactDatabase:

End Function

Then run a query like this using a table not from Database2:

SELECT Top 1 CompactDatabase("d:\path\Database2.accdb") AS Success
FROM SomeTable;
Gustav
  • 53,498
  • 7
  • 29
  • 55