Hi Gary
You don't mention if this is a FE / BE configuration, so I'm sending you 2
flavors of code. Please see the function CanBeOpenedExclusively which will
allow you to check to see if the DB is already in use.
Best Regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
Private Const AttachedTable As Long = 6
Public Sub CompactAttachedTableMDBS()
Dim r As Recordset
Dim s As String
If Forms.Count Or Reports.Count Then
msgbox "Please, close all forms and reports, and retry.",
vbExclamation,
"FFDBA"
Else
s = "SELECT Distinct CStr(DataBase) AS db" _
& " FROM MSysObjects WHERE Type=" & AttachedTable _
& " AND Len(Dir$(CStr(DataBase)))<> 0;"
With DBEngine(0)(0)
.TableDefs.Refresh
Set r = .OpenRecordset(s)
With r
Do While Not .EOF
If CanBeOpenedExclusively(!db) Then
Shell SysCmd(acSysCmdAccessDir) & "MsAccess.Exe " &
"""" & !db
& """" & " /compact"
Else
msgbox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to be opened by another user.",
vbExclamation, "FFDBA"
End If
.MoveNext
Loop
.Close
End With
Set r = Nothing
End With
End If
End Sub
Private Function CanBeOpenedExclusively(ByVal FullPath As String) As Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
Set d = Nothing
Set p = Nothing
End Function
------------
I use this code to compact the current database over itself...
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
