Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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

Reply With Quote
  #3 (permalink)  
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


Reply With Quote
  #4 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:02 PM.


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