Error on Compacting

[Paul H]

This database needs to be compacted each time it is closed. This has NEVER been an issue with any database I have ever worked on, till now. I sporadically get this error "Access is unable to rename database.accdb" and then access closes. Of course database.accdb is my compressed database, but this would be catastrophic if this happened in the field. It doesn't happen every time. This is something that just popped up in the last day or two. I'm not sure what to think.



Thanks in advance,

Paul

[ranman256]

try creating a new blank db,
import EVERYTHING from the old one. (including relations, etc)
then try compacting that.

you shouldnt have to compact everyday.

[NoellaG]

it sounds like the database is still in use by another user. Close the database and check if there still exists a laccdb file. If so, and nobody else but you is working with the database, delete the laccdb file and try again.

[Paul H]

it sounds like the database is still in use by another user.

It a singe user database mean to be used by one person at a time. There is no back end. I am working on a local copy on my desktop.

[Paul H]

try creating a new blank db,
import EVERYTHING from the old one. (including relations, etc)
then try compacting that.

you shouldnt have to compact everyday.

Yeah, I'll probably try that.

There is no back end. Users get their own copy and import new batches of data. That data is purged each time there is a new import. Over time the db can get quite bloated. The compact on close is meant to take care of that.

[isladogs]

Using compact automatically on close can easily lead to corruption if the process gets interrupted.
Far better to make a backup then do a compact when it is actually needed.
Better still, modify the approach so temporary data is stored in a separate 'side end' database which can be deleted and replaced as necessary.

In the meantime, before importing everything into a new blank database, try decompiling to remove any corrupt code. It only takes a second or two.
See http://www.mendipdatasystems.co.uk/c...lds/4594523656
Before you do so, make a backup. Afterwards recompile then compact.

[Paul H]

I think I've found a solution This database needs to be compact occasionally, ideally monthly. I have found a way to Compact on demand using VBA.

I've added a button to set the Compact on Close property to True.

Private Sub cmdCompact_Click()
If MsgBox("Compact and Repair should be performed once per month." & vbNewLine & vbNewLine & _
                "Click yes to Compact on exit.", vbYesNo, "Compact and Repair") = vbYes Then
                
       Application.SetOption "Auto compact", True
       
       MsgBox "Data Quality tools will compact on closing"
End If


End Sub

and then set it back to being False when the application opens.

I understand the corruption issue, but since all data is stored elsewhere and only imported when needed there is no chance of corruption of data.
If needed the user can just download a fresh copy of the application.

By compacting only on demand I hope to avoid this.

My next step, if I have time, will be to store the date of the last Compact and Repair and have it run monthly after that.

Your thoughts?

[Micron]

Why not just compact based on file size? If the characteristic of a db is that it needs compacting more frequently than some, then surely this is driven by use. Usage could be 3x in one month what it was in the prior month, so does a time period make as much sense as a file size? Not to me. Question is, what is the file size you'd pick? Experience would probably tell you that.

[Paul H]

These databases sit on the users desktop. There is no back end I can compact. Each user will have different size files which they can import multiple times. The tables are all cleared each time they import. Depending on their use they can balloon in size. I don't think I can depend on them to check on the file size, so I put in a recommendation that they compact once each month. Is there a way I can trigger this based on file size?

[CJ_London]

I don't think I can depend on them to check on the file size, so I put in a recommendation that they compact once each month. Is there a way I can trigger this based on file size?

have some vba code which runs when the user closes the db

if filelen(currentdb.Name)>???? then
'compact
end if

[Paul H]

That worked, thanks.