Growing MS Access File Size problem

Question

I have a large MS Access application with a lot of computations in VBA code. When I run it it eventually crashes due to excessive file size. There are a lot of intermediate tables and queries created and subsequently deleted, but Access does not reclaim the space. I have diligently closed all intermediate record sets and set all temporary objects to nothing, but nothing helps. The only way I can get my code to run is to run part of it, stop and repair/compress the file then restart the code.

Isn't there a better way?

Thanks

Answer 1

You should be able to run the compact function from within your VBA code.

I had the below snippet bookmarked from a long time ago when I was doing access work.

Public Sub CompactDB() 
    CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database...").accDoDefaultAction 
End Sub 

You can put that in your code to get around it.

NOTE: you might also consider growing to a larger db system if you are having these types of scaling issues.