hi there,
a manufacturing machine connected to front-end and everytime the machine reset the data will be transfer to front-end(access).the data from front-end later will be transfer to the main server(SQL).Problems occurs when data transfer to the SQL from front-end because of network connections.this will overload the access and evrytime i have to compact and repair it.is there anyway i can automated the compacting of the database through code? any help will be really appreciated.

thanks.

tasha

__________________
tasha

Hi Tasha,

Here's a standard DAO routine to compact a Jet database from code. Don't think it will help you if the db is already open, but it might give you some ideas.

Function CompactDb(strDbPath As String) As Boolean
    ' This procedure creates a backup copy of a database
    ' and then compacts it.
    '
    ' Arguments:
    ' strDbPath: The path to the database to be compacted.
    '
    ' Returns:
    ' A Boolean value indicating success or failure.

    Dim dbs As Database
    Dim intLength As Integer
    Dim varPosition As Variant
    Dim strDbTemp As String, strDbCompacted As String
    Dim strDbBackup As String
    Dim strMsg As String
    Const conPermissionDenied As Integer = 70

    On Error GoTo Err_CompactDb
    ' Initialize string for message.
    strMsg = "Database " & strDbPath & " cannot be opened exclusively. " _
        & "The database may have already been opened by you or another user."

    ' Compact the database to a temporary file.
    intLength = Len(strDbPath)
    varPosition = InStr(strDbPath, ".mdb")
    If varPosition > 0 Then
        strDbTemp = Left(strDbPath, varPosition - 1)

        ' Create backup file before compacting.
        strDbBackup = strDbTemp & ".bak"
        FileCopy strDbPath, strDbBackup

        ' Check whether database can be opened exclusively.
        ' This line calls a function defined in Chapter 2.
        If Not CanOpenDbExclusively(strDbPath) Then
            MsgBox strMsg
            GoTo Exit_CompactDb
        End If

        ' Compact to new file.
        strDbCompacted = strDbTemp & "Compacted.mdb"
        DBEngine.CompactDatabase strDbPath, strDbCompacted

        ' Delete uncompacted database.
        Kill strDbPath

        ' Rename compacted database to original name.
        Name strDbCompacted As strDbPath
    End If
    CompactDb = True

Exit_CompactDb:
    On Error Resume Next
    dbs.Close
    Set dbs = Nothing
    Exit Function

Err_CompactDb:
    If Err = conPermissionDenied Then
        MsgBox strMsg
    Else
        MsgBox "Error " & Err & ": " & vbCrLf & Err.Description
    End If
    CompactDb = False
    Resume Exit_CompactDb
End Function

HTH,

Bob

You can also use JRO...

Dim oJetEngine As JRO.JetEngine
Dim sSourceConn As String
Dim sDestConn As String

Set oJetEngine = New JRO.JetEngine

' Engine Type = 4 compacts an Access database in 3.5 format
' Engine Type = 5 compacts an Access database in 4.0 format (default)

sSourceConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=.\advworks.mdb;" & _
              "Jet OLEDB:Engine Type=5;"

sDestConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=.\advworks1.mdb;" & _
            "Jet OLEDB:Engine Type=5;"

' Compact the database (makes a new copy)
oJetEngine.CompactDatabase sSourceConn, sDestConn

see:

http://support.microsoft.com:80/supp...NoWebContent=1

Bob

Thanks Bob!:-)

tasha