CODEWITHSUNDEEP
ms-accessvba
L42
L42

Reputation: 19737

Is it safe to frequently Compact and Repair Access DB?

I have an Access Database which I populate using a pass through query.
I get specific data from an SQL database and dumps it on Access.
This Access DB is updated weekly but I cannot simply append new data from the previous week because all other past weeks also updates.

What I did is truncate the Access DB and then load everything again.
When I do that, I need to Compact and Repair the DB so that the size doesn't bloat.

My question is, is it ok to do that? Currently I am using the logic posted in this answer.
I have not encountered any problems yet but I just want to make sure and get our access guru's thought about it. Also I'm planning on doing a scheduled run on our server to do the task.
Just need to make sure that it will not get corrupted easily (what is the chance of corrupting the file in the first place?).

If you'll ask, why do I need to do this? Users of data have no access on SQL server.
So I have to pull data for them so they can just connect to Access DB instead.

Just in case you need the code:

Dim sqlDelete As String
Dim sqlAppend As String

sqlDelete = "DELETE * FROM dbo_Table;"

sqlAppend = "INSERT INTO dbo_Table (Col1,Col2) SELECT Col1,Col2 FROM passThrough;"

With DoCmd
    .SetWarnings False
    .RunSQL sqlDelete

    .RunSQL sqlAppend
    .SetWarnings True
End With

Application.SetOption "Auto Compact", True

Upvotes: 0

Views: 1021

Answers (2)

Alfie Jin
Alfie Jin

Reputation: 9

I will refer this forum post: http://www.utteraccess.com/forum/index.php?showtopic=1561733

Upvotes: 0

Sergey S.
Sergey S.

Reputation: 6336

If you need to truncate the data and load again I would recommend to move all tables, which should be truncated and all temporary tables to separate database. After separating it will be possible to replace this database with unnecessary data by empty database file when the application starts. Just save a copy of empty database and copy this file over existing database with old data. This should be done before opening any form/recordset based on tables from temp database. It will work faster than C&R each time and more reliable, because sometimes C&R may damage the file.

Template database file also can be stored in main database in Memo field

Upvotes: 2

Related Questions