Hi,
i have read that compacting automatically is wrong.
Why ?
My code looks like below:
Code:
Function BackUps()
Dim sFile As String
Dim Db As dao.Database
Dim oDB As dao.Database
Dim oTD As TableDef
Dim OtherDB As dao.Database
Dim sPath As String
Dim newFile As String
Dim sPath2 As String
DoCmd.SetWarnings False
On Error Resume Next
'paths for my databases and their copies
sPath = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\Aplikacja_Braki_BE.accdb"
sPath2 = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\Aplikacja_Braki_BE_V2.accdb"
sFile = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Archiwum_kopie\" & Format(Now(), "DD-MM-YYYY_HHMM") & ".accdb"
'here i am creating copy of my back-end
DBEngine.CompactDatabase sPath, sFile, , , ";pwd=1234"
' here i am checking if database is open, if yes close the database
If CheckLock("\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\Aplikacja_Braki_BE.accdb") = True Then
Access.Quit
Exit Function
Else
'if back-end is closed- i am creating copy of my database (and in the same time i am compacting it), i am killing the sPath file and changing name from Path2's name to Path1's name.
DBEngine.CompactDatabase sPath, sPath2, , , ";pwd=1234"
Kill sPath
Name sPath2 As sPath
End If
Set Db = Nothing
On Error GoTo 0
Access.Quit
End Function
'function to check if database is opened
Private Function CheckLock(strFileName As String) As Boolean
On Error Resume Next
Dim intFF As Integer
intFF = FreeFile
Open strFileName For Binary Access Read Lock Read As #intFF
Close #intFF
If Err.Number Then
Err.Clear
CheckLock = True
Else
CheckLock = False
End If
End Function
What about automatically compacting database? what is wrong with that?
Best Wishes,
Jacek Antek