p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   compacting access (http://p2p.wrox.com/showthread.php?t=16483)

Tasha August 5th, 2004 10:38 PM

compacting access

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.



Bob Bedell August 6th, 2004 10:46 AM

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

    On Error Resume Next
    Set dbs = Nothing
    Exit Function

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



Bob Bedell August 6th, 2004 03:10 PM

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




Tasha August 9th, 2004 07:46 PM

Thanks Bob!:-)


All times are GMT -4. The time now is 04:25 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.