CODEWITHSUNDEEP
sqlms-accesscompact-database
Herb A
Herb A

Reputation: 11

Can you compact one Access database using a query in another database

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

Upvotes: 1

Views: 195

Answers (1)

Gustav
Gustav

Reputation: 55806

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;

Upvotes: 1

Related Questions