Compact on Close - beware?

[Kirk P.]

I just had a database file delete itself and replaced with a file named db1.
Unfortunately, when I try and open the db1 file, I get "unrecognizable
database format" error.

I was accessing the database (Access 2003) on a corporate network from home
via a broadband VPN connection, compact on close is turned on, and the
problem happened when exiting the database. I suspect a problem with
"compact on close", so I'm pondering whether I should turn this off and train
the users to periodically compact on their own.

I know I should split the db into FE/BE, which I certainly will do - any
other advice to prevent this in the future?

 

[John Ortt]

Maybe use an option button.

"Would you like to compact the database now?"

Complete with the advisory warning "Do not compact the database if
connecting externally"

One possibility...

 

[Paul Shapiro]

Access is going to be vulnerable to corruption from any network disruption,
which becomes a more likely scenario working remotely. If you have the
option, using Remote Desktop to your work pc or a Terminal Server on the
same LAN as the Access db will be both faster and less likely to cause db
corruption.

 

[krissco]

Yes, turn off "Compact On Close" - it is slow, annoying, and causes
problems.
You can programmatically turn on/off compact on close. A couple years
ago I created the attached code (with help of course). It compacts
when the current database grows > 50MB and the only user in the db is
exiting.

To exit the database, place a call to myExit() on your switchboard or
wherever your user's usually exit from.

'Function determines if the current database is in use by one and only
one user
'Users that have left the database in a suspect state do not count.
'Adapted from Microsoft MSDN help
Function singleUser() As Boolean
Dim cn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim i As Integer

Set cn = CurrentProject.Connection

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

Set RS = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Count the number of connect users with no suspect state
i = 0
While Not RS.EOF
If IsNull(RS.Fields(3)) Then i = i + 1
RS.MoveNext
Wend
singleUser = (i = 1)

'Cleanup
If Not cn Is Nothing Then
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End If
If Not RS Is Nothing Then
If RS.State = adStateOpen Then RS.Close
Set RS = Nothing
End If
End Function


'Compact and clear data if database is larger than 50 megs (and there
is only one user)
Public Function myExit()

If singleUser() And FileLen(Application.CurrentDb().Name) > 52428800
Then

On Error Resume Next
'Restrict other users from opening the database
CurrentProject.Connection.Properties("Jet OLEDB:Connection
Control") = 1

SysCmd acSysCmdSetStatus, "Please Be Patient. Removing Temporary
Data From The Database."

'Place any function calls to clear out temp tables in this spot

'Set compact on close to true
CurrentDb.Properties.Delete "Auto Compact"
CurrentDb.Properties.append CurrentDb.CreateProperty("Auto
Compact", dbBoolean, True, True)
CurrentDb.Properties("Auto Compact").Value = True
Else
On Error Resume Next
CurrentDb.Properties.Delete "Auto Compact"
CurrentDb.Properties.append CurrentDb.CreateProperty("Auto
Compact", dbBoolean, True, True)
CurrentDb.Properties("Auto Compact").Value = False
End If

DoCmd.RunCommand acCmdExit
End Function


Have fun!

-Kris

 

[John W. Vinson]

I just had a database file delete itself and replaced with a file named db1.
Unfortunately, when I try and open the db1 file, I get "unrecognizable
database format" error.
:-{(

I was accessing the database (Access 2003) on a corporate network from home
via a broadband VPN connection, compact on close is turned on, and the
problem happened when exiting the database. I suspect a problem with
"compact on close", so I'm pondering whether I should turn this off and train
the users to periodically compact on their own.

Yep. Compaction works by copying the database to db1.mdb, then deleting the
database and renaming db1. Evidently this process failed in your case; the
copy was corrupt so the compaction never completed.

I know I should split the db into FE/BE, which I certainly will do - any
other advice to prevent this in the future?

Don't use compact on close, at least not on a remote connection; keep current
backups; and.... SPLIT THE DATABASE!!!

John W. Vinson [MVP]