Compact & Repair thourhg VB

[Zxurian]

does anybody know the function or method to compact and repair an Access2000 database through Visual Basic 6.0 Pro?

Thanks in advance.

[JHausmann]

DAO has the following methods you can use:

DBEngine.CompactDatabase
DBEngine.RepairDatabase

[Zxurian]

so if I had a database named 'dbEPS'

it would be

dbEPS.compactDatabase
dbeps.repairdatabase

?

[JHausmann]

Nope.

It's what I gave you. the exact format is:

DBEngine.CompactDatabase sourceDB, destinationDB

You need to make sure your mdb is closed before you attempt it. The value of sourceDB should be something like "c:\databases\production\myDB.mdb" (as an example).

[Zxurian]

if its going to be the same database name for both the source and the destination, should I put down the same name, or just omit the destination?

[JHausmann]

Oooh, you like living on the edge. Not sure but I think it's a required parameter. Safest thing to do is populate it.

[Zxurian]

okay, tried the original code, but there is no method for '.repairdatabase' only '.compact database'.

could the same command do both things?

[JHausmann]

Must be another difference between 3.6 and 3.51

[FrancisC]

This is something I found on this site... sorry for the author, I forgot who you are...

This uses ADO, but for some reason I could never get it to work with the OLE DB 3.51. To use that, you need to declare a reference to: "Microsoft Jet and Replication Objects 2.5 Library"...

Const DB_PROVIDER = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source="

' Location is the full path where to find the database you want to compress.
Public Sub CompactJetDatabase(Location As String, Optional BackupOriginal As Boolean = True)

Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine

On Error GoTo CompactErr

Dim strBackupFile As String
Dim strTempFile As String

Dim strOrgConnect As String
Dim strDestConnect As String

'Check that the database exists
If Len(Dir(Location)) Then

    ' If a backup is required, do it!
    If BackupOriginal = True Then
        strBackupFile = GetTemporaryPath & "backup.mdb"
        If Len(Dir(strBackupFile)) Then Kill strBackupFile
        FileCopy Location, strBackupFile
    End If

    ' Create temporary filename
    strTempFile = GetTemporaryPath & "temp.mdb"
    If Len(Dir(strTempFile)) Then Kill strTempFile

    ' Do the compacting via JRO
    strOrgConnect = DB_PROVIDER & Location & ""
    strDestConnect = DB_PROVIDER & strTempFile & ";Jet OLEDB:Engine Type=5"
    
    JRO.CompactDatabase strOrgConnect, strDestConnect

    ' Remove the original database file
    Kill Location

    ' Copy the temporary now-compressed database file back to the original location
    FileCopy strTempFile, Location

    ' Delete the temporary file
    Kill strTempFile
End If

Exit Sub
CompactErr:

MsgBox Err.Description & "  Error # " & Err.Number, _ vbExclamation, "Error"

End Sub

This also uses the API to find the temporary path, and a function based on the API:

Public Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
    (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Public Function GetTemporaryPath()

    Dim strFolder As String
    Dim lngResult As Long
    
    strFolder = String(MAX_PATH, 0)
    lngResult = GetTempPath(MAX_PATH, strFolder)
    
    If lngResult <> 0 Then
        GetTemporaryPath = Left(strFolder, InStr(strFolder, Chr(0)) - 1)
    Else
        GetTemporaryPath = ""
    End If

End Function

I hope this helps...