Compacting live database

[HartJF]

I have a database that is used in a data mining system. After compacting-on-close, it is over 800MB. Each week, the data is refreshed by deleting all records from the tables, then appending new records by way of queries against linked text files. Those files total nearly 500MB. Before the queries have finished, the code abends because the database has expanded to 2GB.

I would like to compact the database after deleting the records. When I delete the records, manually close and compact the database, and re-open and append the records, the process completes successfully.

Do I have any options to automate the compacting?

 

[Trevor G]

There are loads of websites out there which will give you the code to do this, here is a link

http://forums.devarticles.com/microsoft-access-development-49/compact-database-via-vba-24958.html

If automating then you can call the process once you have run the queries to empty the tables via a macro or vba

 

[HartJF]

Thank you for your response, Trevor.

That's a new forum to me. It's excellent! I've bookmarked it (below, of course, Access World )

My database is a utility back-end that will be used in a variety of mining projects. What I have done is placed an update shell in the manipulating .accdb that uses automation to delete the current records, close (and compact-on-close) the database, and inserts the new records, as follows:

Sub sbrRefreshPowerCAMPUS()
Dim strPowerCAMPUStemplatePath As String
Dim objAccess As Access.Application
strPowerCAMPUStemplatePath = CurrentProject.Path & "\" & "PowerCAMPUS template.accdb"
Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase strPowerCAMPUStemplatePath, True
.Run "sbrDeleteFiles"
.CloseCurrentDatabase
.OpenCurrentDatabase strPowerCAMPUStemplatePath, True
.Run "sbrAppendFiles"
.CloseCurrentDatabase
End With
Set objAccess = Nothing
End Sub

 

[Trevor G]

Excellent news that this works for you

well done.