compact .mdb from vb

[gab2001uk]

One small point. If the database is password protected

DBEngine.CompactDatabase Location, strTempFile, , , ";pwd=YourPassword"

[Jacsoftware]

I checked the code line by line .. and it kicks out to the error handler at the dbengine line.. Do I have to dim the dbengine. .. the intelesense doesnt come up when I do a "dbengine. "

[gab2001uk]

Have you added a reference to DAO v 3.51 for Access 97 or 3.6 for 2000 and above?
Alterative method. You do not need to add any references, but you do need a Common Dialog control to locate your file, and rename it cdlgFile

Private Sub cmdDAOCompact_Click()
    Dim dbE As Object
    Dim dbDAO As Object
    Dim prop As Object
    Dim fAccess97 As Boolean
    Dim fRepair As Boolean
    Dim strSource As String
    Dim strPassword As String
    Dim strDestDB As String
    Dim iFileStart As Integer
 
    
    'locate database file
    With cdlgFile
        .DialogTitle = "Select Database to Compact"
        .Filter = "Access 97/2000 (*.mdb)|*.mdb"
        .CancelError = False
        .InitDir = "C:\My Documents"
        .ShowOpen
    End With
    
    strSource = cdlgFile.FileName
    If strSource = "" Then
        MsgBox "Cancel clicked"
        Exit Sub
    End If
    
    'create DBEngine version 3.6, to open database and find version
    Set dbE = CreateObject("DAO.DBEngine.36")
    
    strPassword = InputBox("Enter password for this database. Leave blank if no password required")
    'open to get jet version
    Set dbDAO = dbE.OpenDatabase(strSource, True, False, ";pwd=" & strPassword)
    
    'loop through properties
    For Each prop In dbDAO.Properties
        If prop.Name = "Version" Then
            If prop.Value = "3.0" Then
                fAccess97 = True
            Else
                fAccess97 = False
            End If
            Exit For
        End If
    Next prop
    
    'close database ready for compacting
    dbDAO.Close
    Set dbDAO = Nothing
    
    If fAccess97 Then
        'check if user wants to repair first
        If MsgBox("Access 97 Databases can be repaired separately from the .Compact method." & vbCrLf & "Do you want to repair first?", vbQuestion + vbYesNo) = vbYes Then
            fRepair = True
        End If
        'need to use DBengine 3.5 so close version 3.6
        Set dbE = Nothing
        Set dbE = CreateObject("DAO.DBEngine.35")
    End If
    
    
 
    'get path/file name of source, add "Backup of " to file name for temporary filename
    iFileStart = InStrRev(strSource, "\", , vbTextCompare)
    strDestDB = Mid$(strSource, 1, iFileStart) & "Backup of " & Mid$(strSource, iFileStart + 1)
    
    On Error GoTo CompactErr
    If fRepair Then
        dbE.RepairDatabase strSource
    End If
    If Len(strPassword) > 0 Then
        dbE.CompactDatabase strSource, strDestDB, , , ";pwd=" & strPassword
    Else
        dbE.CompactDatabase strSource, strDestDB
    End If
    Kill strSource
 
    DoEvents
    
    'rename to original filename
    Name strDestDB As strSource
 
    
    MsgBox strSource & vbCrLf & "Repaired and Compacted Successfully"
    
    Exit Sub
    
CompactErr:
    MsgBox strSource & vbCrLf & "Compaction error:" & vbCrLf & dbE.Errors(0).Number & " " & dbE.Errors(0).Description
End Sub

[Jacsoftware]

ah ha ....sorry never taught of it .. its been a long day .. thanks