How to shrink a database?

[Andrew R.]

Is there any way to programmatically shrink an Access database? By using, let say, some ADO function?

[Fraggle]

Hi Andrew,

see my post "how to compress a DB using OLE" a few lines below yours. I had the same problem and got an answer. Didn´t try it out yet, though. But it looks good ;o)

cu
Fraggle

[John G Duffy]

Start a new project. Add Component "Microsoft ADO Data Control 6.0 (OLEDB). My version is SP4.
Paste this code into the general declarations section of the Form. Add a couple of command buttons, Command1.Caption = "Compact it", Command2.Caption = "Browse", a text box and a Common Dialog Control. "Browse" lets you select the Data Base. "Compact" does the dirty work.
Backup your data base first of course.

option Explicit

private Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA" (byval nBufferLength as Long, byval lpBuffer _
as string) as Long

Const MAX_PATH = 260

private Sub Command1_Click()
CompactJetDatabase (cdl.FileName)
End Sub

private Sub Command2_Click()
cdl.CancelError = true
on error resume next
cdl.Filter = "DataBase files (*.mdb)|*.mdb|" & _
"All files (*.*)|*.*"

cdl.ShowOpen
If Err.Number <> 0 then Exit Sub
Text1 = cdl.FileName
Text1.SelStart = len(Text1)
End Sub


public Sub CompactJetDatabase(Location as string, _
optional BackupOriginal as Boolean = true)

on error GoTo CompactErr

Dim strBackupFile as string
Dim strTempFile as string

'Check 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 DBEngine
DBEngine.CompactDatabase Location, strTempFile

' 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

else

End If

CompactErr:

Exit Sub

End Sub

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





John G

[Aage Eilertsen]

I am not familiar with using the DBEngine object.
How do I create this?

[John G Duffy]

DBEngine is part of the Component "Microsoft ADO Data Control 6.0 (OLEDB) I mentioned. Include it via the Project/Components menu Items of the IDE.

John G