Network compact

[Exodus]

Ok so I'm trying to get the backend that resides on the network to compact when the last user exits the FE. I have read that network compacts can corrupt the db becuase of the data compacting over the network. I have ran across Rogers compact be code was wondering if there is the same risk with doing it this way.

'**********************************
'Created by Roger Carlson         *
'Roger.Carlson@spectrum-health.org*
'Rog3erc@aol.com                  *
'**********************************Private Sub cmdCompact_Click()
'On Error GoTo Err_cmdCompact_Click

    Dim LinkPathFile As String
    Dim LinkPath As String
    Dim LinkFile As String
    Dim FileWithoutExtention As String
    Dim Pw As String
    Dim GetPwLength As String
    Dim Pwis As String
       
    
    'uses the findsource, getpath, and getfile functions
    'to determine the path and filename of the linked database
    LinkPathFile = Mid(FindSource(), InStr(FindSource(), "\\"))
    Pw = Mid(FindSource(), InStr(FindSource(), ";"))
    GetPwLength = InStr(2, Pw, ";")
    Pwis = Mid(Pw, 1, GetPwLength)
    LinkPath = getpath(LinkPathFile)
    LinkFile = getfile(LinkPathFile)
    FileWithoutExtention = Left(LinkFile, InStr(LinkFile, ".") - 1)
 
    Debug.Print Pw
    'Debug.Print FindSource()
    Debug.Print GetPwLength
    Debug.Print Pwis
    'Compact the Back-End database to a temp file.
    DBEngine.CompactDatabase LinkPath & LinkFile, LinkPath & FileWithoutExtention & "Temp.mdb", Pwis, , Pwis

    'Delete the previous backup file if it exists.
    If Dir(LinkPath & FileWithoutExtention & ".bak") <> "" Then
        Kill LinkPath & FileWithoutExtention & ".bak"
    End If

    'Rename the current database as backup and rename the temp file to
    'the original file name.
    Name LinkPath & LinkFile As LinkPath & FileWithoutExtention & ".bak"
    Name LinkPath & FileWithoutExtention & "Temp.mdb" As LinkPath & LinkFile

Exit_cmdCompact_Click:
    Exit Sub

Err_cmdCompact_Click:
    MsgBox Err.Description
    Resume Exit_cmdCompact_Click
    
End Sub

 

[DCrake]

When compacting over a network you will need to take into consideration your network connection size, the size of the mdb, whether its a LAN or a WAN.

If it's a large mdb it may be more efficient to copy the mdb locally then run the compact and then copy it back to the residual folder. Access caches the whole mdb, especially if you compacting back to its original name. So if you do this on a network location consider the mdb size time 2. Also it still protects the original if you are compacting a copy.

 

[Exodus]

Thanks for the reply. I am trying to avoid doing a copy to the local as I have up to 10 users and it's hard to control what they might do if they see the program waiting to close for too long.
Is the Jetcomp.exe a better option at this point?