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