Compacting a back-end DB from the front-end

[Guest]

I have a front-end/back-end access db application. How can I compact the
back-end via a function from the front-end?

 

[Guest]

In addition, I've looked at the other post relating to this topic and tried
them all to no avail. I keep getting the message "You attempted to open a
database that is already opened exclusively by user........Try again when the
database is available"

 

[Douglas J. Steele]

You can't compact the back-end if anyone else is using it, and it's possible
for you to be using it if you've got a bound form open, for instance.

In general, you can use DAO to compact the back-end if it's not open. You
can tell if it's open by whether or not there's the locking file (.ldb) in
the same folder. I typically rename the existing back-end and then compact
it to "the correct name", just in case:

Function CompactDatabase(DatabaseName As String) As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Dim booStatus As Boolean
Dim strBackupFile As String

booStatus = True

' Make sure that DatabaseName exists
If Len(Dir$(DatabaseName)) > 0 Then

' Figure out what the backup file should be named
If StrComp(Right$(DatabaseName, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(DatabaseName, Len(DatabaseName) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name DatabaseName As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, DatabaseName
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOkOnly + vbCritical
Resume End_CompactDatabase

End Function

(watch for word-wrap in that code)

You can also use JRO (Jet Replication Objects) to do the compact.

See http://support.microsoft.com/?id=230501 for details

 

[Corey]

-----Original Message-----
In addition, I've looked at the other post relating to this topic and tried
them all to no avail. I keep getting the message "You attempted to open a
database that is already opened exclusively by user........Try again when the
database is available"
.

If you want to compact DB1, open a second file (DB2).
Then close DB2. With no files open, choose Tools,
Database Utilities, Compact...

You may need to rename to a new name - I can't remember
for sure.

 

[Guest]

I've tried your code and on the following statement:

Name DatabaseName As strBackupFile

I've got the following error:

Path/File access error (Error# 75)

I am running the procedure from the form utilizing the TreeView control by
clicking a menu choice and then subsequently calling the procedure.

If instead, this menu choice opens a form having a command button which
calls the procedure, it works fine.

What can be the reason of the failure in the first case?
Thanks.

 

[Guest]

I have a procedure that I run twice daily to update some of the data in the
back-end from a completely separate database (not Access), which results in
the back-end becoming bloated and needing to be compacted every time. This
procedure runs from a Switchboard command. Is it better to have the
procedure close the switchboard before running the compact code you have here
or to just move the Switchboard Items table to the front-end?

Amy

 

[Douglas J Steele]

I can't really comment. I never use the built-in Switchboard, so I don't
know the pros and cons.

 

[Van T. Dinh]

Should not matter either way. If the "Switchboard Items" Table is in the BE
you are trying to compact, closing the Switchboard should release the
connection to the Table.

I don't use the Switchboard but personally, I would prefer to keet it in the
FE since logically, it is part of the GUI, not part of the data.