Compacting Backend Access Databases

[Guest]

I'm using a front end access program linked to a back end database of tables.
When I set the option to compact and repair on close, it doesn't seem to
compact the back end database. Can someone recommend the code I can include
in an On Click event for a button that would compact and repair the back end
tables? I'm using Access 2002 but the database is saved as an Access 2000
database for use by a person who only has that version.

 

[Granny Spitz via AccessMonster.com]

When I set the option to compact and repair on close, it doesn't seem to
compact the back end database.

That option is only available for the *current* database file through the
Access interface. You would have to kick everybody out of the front end and
then open and close the back end with the compact on close option set on the
back end.

Can someone recommend the code I can include
in an On Click event for a button that would compact and repair the back end
tables?

To compact a database, you need exclusive access to the database. The front
end is linked so when you use code in the front end to compact the back end,
the compaction will fail because *you* are in it (linked from the front end).
You can unlink all your tables, run code to compact the back end then relink
the front end, but it's easier to just set up a windows task to compact it at
night when everybody is gone or compact the back end manually when nobody is
in it.

 

[Dirk Goldgar]

That option is only available for the *current* database file through
the Access interface. You would have to kick everybody out of the
front end and then open and close the back end with the compact on
close option set on the back end.


To compact a database, you need exclusive access to the database.
The front end is linked so when you use code in the front end to
compact the back end, the compaction will fail because *you* are in
it (linked from the front end). You can unlink all your tables, run
code to compact the back end then relink the front end, but it's
easier to just set up a windows task to compact it at night when
everybody is gone or compact the back end manually when nobody is in
it.

It's not necessary to unlink all the tables. As long as no one (the
current database included) has any *active* connection to the back
end -- no open bound forms, table/query datasheets, reports, recordsets,
etc. -- you can get exclusive access to the back end database and
compact it. I have some code here somewhere .... ah, here it is:

'------ start of code (Access 97) -----
Sub CompactBackendDatabase()

' Compact the back-end database to which this database is linked.

On Error GoTo Err_Handler


Dim strBackendPathAndName As String
Dim strBackendFileName As String
Dim strBackendFolder As String
Dim strFileNameNoExt As String
Dim strTempFile As String
Dim strOldFile As String
Dim strErrText As String
Dim strErrUser As String
Dim strMessage As String
Dim intPos As Integer

strBackendPathAndName = DataDBName
strBackendFileName = Dir(strBackendPathAndName)
strBackendFolder = Left(strBackendPathAndName,
Len(strBackendPathAndName) - Len(strBackendFileName))

intPos = InStrRev(strBackendFileName, ".")
If intPos > 0 Then
strFileNameNoExt = Left(strBackendFileName, intPos - 1)
Else
strFileNameNoExt = strBackendFileName
End If

strTempFile = strBackendFolder & strFileNameNoExt & "_COMPACTED.mdb"

DoCmd.OpenForm "PleaseWait", , , , , , "Compacting back-end database
...."
DoEvents
Application.DBEngine.Idle dbRefreshCache
DoEvents

' Compact the database into the temp file.
Application.DBEngine.CompactDatabase strBackendPathAndName,
strTempFile
Application.DBEngine.Idle dbRefreshCache

' If that went well, rename the database to a different name.
strOldFile = strBackendFolder & strFileNameNoExt &
"_UNCOMPACTED.mdb"

If FileExists(strOldFile) Then
Kill strOldFile
End If

Name strBackendPathAndName As strOldFile

' If that went well, rename the temp file to the original DB name.
Name strTempFile As strBackendPathAndName

' If that went well, delete the old, uncompacted back-end.
Kill strOldFile

' Refresh the table links.
' RefreshTableLinks

' We're done.

Exit_Point:
Application.DBEngine.Idle dbRefreshCache
DoCmd.Close acForm, "PleaseWait"
DoEvents
Exit Sub

Err_Handler:
DoCmd.Hourglass False
Select Case Err.Number
Case 3356
strMessage = "Unable to get exclusive access to the back-end
database.@"
intPos = InStr(Err.Description, " by user")
If intPos > 0 Then
strErrText = Mid(Err.Description, intPos)
strErrText = Left(strErrText, InStr(strErrText, "."))
strErrUser = Mid(strErrText, 11, InStr(11, strErrText,
"'") - 11)
If strErrUser <> CurrentUser Then
strMessage = strMessage & "The database is currently
in use" & strErrText
Else
strMessage = strMessage & _
"Close any open forms, reports, or datasheets
that are " & _
"displaying data, and try again."
End If
End If
strMessage = strMessage & _
"@All active connections to the database must be closed
in order to compact it."
MsgBox strMessage, vbExclamation, "Unable to Compact
Back-End Database"
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End Select

LogError "CompactBackendDatabase"

Resume Exit_Point

End Sub
'------ end of code -----

Be aware that many of the lines of code above will have been wrapped by
the newsreader. I haven't tried to fix them up.

Note: the function DataDBName() is a user-defined function that returns
the name and path of the back-end database. FileExists() is a function
that returns True if the argument "FileName" is the name of an existing
file, that is not a directory and not a hidden or system file, and False
if it is not. InStrRev() is not the built-in VBA function that was
introduced with Access 2000, but rather a user-written lookalike that I
added for use with Access 97. If you're adapting this function for
Access 2000 or later, you don't need to worry about that.

 

[Granny Spitz via AccessMonster.com]

It's not necessary to unlink all the tables.

Thanks for the code, Dirk. I especially like your idea of the "PleaseWait"
form with the OpenArgs string telling the user what to expect. Very nice!

 

[Guest]

Dirk,
I incorporated your code and ran it as a procedure from an on-click event
for a button on a very plain form. None of the controls or objects on the
form refer to any tables or querries...no reference to any data whatsoever.
Even the form itself had no assigned data source. The only objects on the
form are a couple of labels and other buttons that open other forms. All
those other forms were closed. And yet, when I ran the procedure to compact
the linked database, the message box reported that I did not have exclusive
use of the back-end database. This is a single user program so no worries
about someone else being signed on. Any idea how to ensure that I have
exclusive use of the back-end database?

 

[Dirk Goldgar]

Dirk,
I incorporated your code and ran it as a procedure from an on-click
event for a button on a very plain form. None of the controls or
objects on the form refer to any tables or querries...no reference to
any data whatsoever. Even the form itself had no assigned data
source. The only objects on the form are a couple of labels and
other buttons that open other forms. All those other forms were
closed. And yet, when I ran the procedure to compact the linked
database, the message box reported that I did not have exclusive use
of the back-end database. This is a single user program so no
worries about someone else being signed on. Any idea how to ensure
that I have exclusive use of the back-end database?

Odd. It works fine for me, and has worked in production for years. Are
you sure you have no recordsets, forms, datasheets, reports, etc. open
at all? No hidden forms, by any chance?

 

[Guest]

Hi Dirk,

I'll double check when I go home from my day job to see if there are any
open controls. However, since I know that I want all open things to be
closed when I click on the button to compact, can you help me with the code
to force everything to be closed and then run the rest of the procedure you
provided? In the best case there might be a "CloseAll" command. In the
worst case, I could just close all forms and reports one by one if I knew how
to write the statement.