Compact & Repair thourhg VB

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

Thanks in advance.

DAO has the following methods you can use:

DBEngine.CompactDatabase
DBEngine.RepairDatabase

so if I had a database named 'dbEPS'

it would be

Code:

---

dbEPS.compactDatabase
dbeps.repairdatabase

---

?

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).

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?

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

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

could the same command do both things?

Must be another difference between 3.6 and 3.51

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"...

Code:

---

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:

Code:

---

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...