Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Unanswered: Compact and Repair

    Hi guys,

    I need to copact and repair my database, MS Acess 2007, with using a VBA code. Can you help me PLZ?

    Thanks,
    Behi

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by bseyedes View Post
    Hi guys,

    I need to copact and repair my database, MS Acess 2007, with using a VBA code. Can you help me PLZ?

    Thanks,
    Behi
    Are you wanting to compact the front end or the back end?

    I would also urge you to also make a backup before you run the compact. It is possible the that the compact could fail. When it does, it destroys the database beyond repair. You will need the backup to recover.


    Here is some code that will do the compact. Note: it does not do a backup
    Code:
    Function RepairDatabase(strSource As String) As Boolean
    On Error GoTo err_proc
    ' Input path and file name of the source file.
    
    Dim strTemp1 As String
    Dim strComp1 As String
    If CanOpenDbExclusively(strSource) = False Then
    MsgBox "BE File in use - Please try the Compact and repair Function later", vbCritical, "File In Use"
    Exit Function
    End If
    strTemp1 = CurrentProject.Path & "\TempBE.MDB"
    strComp1 = CurrentProject.Path & "\Comp.MDB"
    If Dir(strTemp1) <> "" Then Kill strTemp1
    If Dir(strComp1) <> "" Then Kill strComp1
    
    'Copy source file to local directory
    FileCopy strSource, strTemp1
    
    ' Compact and repair the database.
    Application.CompactRepair LogFile:=True, SourceFile:=strTemp1, DestinationFile:=strComp1
    
    'Write compacted file back to source
    Kill strSource
    FileCopy strComp1, strSource
    
    RepairDatabase = True
    
    exit_proc:
    Exit Function
    
    err_proc:
    Resume exit_proc
    
    End Function
    
    
    Function CanOpenDbExclusively(strDbPath As String) As Boolean
    On Error GoTo err_proc
    'Checks if file define by strDbPath can be oened exclusively,
    'if so then sets CanOpenDbExclusively = true
    
    On Error Resume Next
    Dim dbe As PrivDBEngine
    Dim wrk As Workspace
    Dim dbs As DAO.Database
    
    Const conFileInUse = 3045
    Const conDBOpenedExclusively = 3356
    
    Set dbe = New PrivDBEngine
    Set wrk = dbe.Workspaces(0)
    Set dbs = wrk.OpenDatabase(strDbPath, True, False)
    If dbs Is Nothing Then
    ' If error is unanticipated, display message.
    If (err = conFileInUse Or err = conDBOpenedExclusively) Then
    ' If database cannot be opened exclusively, return False.
    CanOpenDbExclusively = False
    Else
    MsgBox "Error: " & err & ": " & vbCrLf & err.Description, , CurrentDb.Properties("strTitle")
    End If
    Else
    ' If database can be opened exclusively, return True.
    CanOpenDbExclusively = True
    dbs.Close
    End If
    
    exit_proc:
    Exit Function
    
    err_proc:
    MsgBox err.Description
    Resume exit_proc
    
    End Function
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    if it the backend you want to compact/repair a tick I do

    is set the msaccess to compact on close in the backend create "autoexec" macro to quick the database

    then set a schedule to open the database 1am (after a backup was done) the autoexec kick in and the database get compacted/repaired on close
    Last edited by myle; 05-05-10 at 06:04. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Apr 2010
    Posts
    5
    Hi guys,
    Thanks for the reply:

    That's what I have in my code:
    Private Sub Command76_Click()

    Call RepairDatabase

    End Sub

    'Function RepairDatabase()
    'DBEngine.CompactDatabase "C:\Documents and Settings\beskandari\Desktop\InventoryDatabase\2007 VersionInventory.accdb", "C:\Documents and Settings\beskandari\Desktop\InventoryDatabase\2007 VersionInventory.accdb", , ";pwd:=password"
    Function RepairDatabase(strSource As String) As Boolean
    On Error GoTo err_proc
    ' Input path and file name of the source file.

    Dim strTemp1 As String
    Dim strComp1 As String
    If CanOpenDbExclusively(strSource) = False Then
    MsgBox "BE File in use - Please try the Compact and repair Function later", vbCritical, "File In Use"
    Exit Function
    End If
    strTemp1 = CurrentProject.Path & "\TempBE.MDB"
    strComp1 = CurrentProject.Path & "\Comp.MDB"
    If Dir(strTemp1) <> "" Then Kill strTemp1
    If Dir(strComp1) <> "" Then Kill strComp1

    'Copy source file to local directory
    FileCopy strSource, strTemp1

    ' Compact and repair the database.
    Application.CompactRepair LogFile:=True, SourceFile:=strTemp1, DestinationFile:=strComp1

    'Write compacted file back to source
    Kill strSource
    FileCopy strComp1, strSource

    RepairDatabase = True

    exit_proc:
    Exit Function

    err_proc:
    Resume exit_proc

    End Function

    and I get the following message saying that "argument not optional" and it high lights Call RepairDatabase
    What should I do?

  5. #5
    Join Date
    Apr 2010
    Posts
    5
    What thsi code means?
    strTemp1 = CurrentProject.Path & "\TempBE.MDB"
    strComp1 = CurrentProject.Path & "\Comp.MDB"

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bseyedes View Post
    and I get the following message saying that "argument not optional" and it high lights Call RepairDatabase
    What should I do?
    Ah! That means the argument is not optional for the function RepairDatabase have a look at the function definition - it requires a non-optional argument.

    To find out what code like that means (since it is an assignment you can be fairly sure it is safe) copy and paste in the Immediate Window and run it (by hitting Enter):
    Code:
    ?CurrentProject.Path & "\TempBE.MDB"
    it will be obvious then.

    BTW - please could you wrap [ code] [ /code] tags around code (without the spaces)? it makes it much easier to read.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by bseyedes View Post
    Hi guys,

    I need to copact and repair my database, MS Acess 2007, with using a VBA code. Can you help me PLZ?

    Thanks,
    Behi
    I wouldn't recommend doing this in code. If you need to compact/repair the backend/frontend because you rolled out an application for another company where you are not an employee, then I can understand. If you're doing it because of 'bloating' on temp table creation, there's a better way. If you're doing it for any other reasons (such as user's locking/corrupting the backend/frontend), there's a better way.

    It's very easy to actually corrupt an accdb/mdb if running this kind of code.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •