Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 5th, 2004, 10:38 PM
Authorized User
 
Join Date: Jul 2004
Location: seremban, negeri sembilan, Malaysia.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Tasha Send a message via Yahoo to Tasha
Default 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.

thanks.

tasha
__________________
tasha
 
Old August 6th, 2004, 10:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old August 6th, 2004, 03:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old August 9th, 2004, 07:46 PM
Authorized User
 
Join Date: Jul 2004
Location: seremban, negeri sembilan, Malaysia.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Tasha Send a message via Yahoo to Tasha
Default

Thanks Bob!:-)

tasha




Similar Threads
Thread Thread Starter Forum Replies Last Post
Compacting database Scootterp Access VBA 5 August 9th, 2006 09:33 AM
Compacting the Frontend Scripts82 Access VBA 0 February 18th, 2006 11:31 PM
Heeeelppp !!! Problems Compacting Access 2000 timmaher Access 2 August 13th, 2004 05:32 AM
Compacting Access Database problem imaley SQL Language 1 April 23rd, 2004 11:28 AM
Compacting Access Database from code imaley Access 6 March 31st, 2004 05:33 PM





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