Compact and repair by SQL

[zggs90]

Does anybody know if it possible to "compact and repair" an access database from within a VB programm using SQL or ADO or DAO?

I have developed a VB program for a client which uses an acces database and I would like to be able to provide the facility for the user to "compact and repair" without having to open Access.

Geoff

 

[blarson0]

You can do it in code, to another database, but not to the currently open database.

' This code was originally written by Terry Wickenden.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.

Sub CompactDB(strOldDb As String, Optional strNewDb As String)
'strOldDb = name of database to be compacted including full path
'strNewDb = name of database once compacted including full path

Dim strCompact As String

On Error GoTo errCompactDB
'Turn off screen refresh
Application.Echo False
'Check if new database has been specified
If IsMissing(strNewDb) Then
strCompact = strOldDb
Else
strCompact = strNewDb
End If
'Which data base to be compacted
SendKeys strOldDb, False
SendKeys "{enter}", False
'Name of database to compact to
SendKeys strCompact, False
SendKeys "{enter}", False
RunCommand acCmdCompactDatabase
exitCompactDB:
'Turn the screen refresh back on
Application.Echo True
Exit Sub

errCompactDB:
Select Case Err
Case 2501
'Cancel Button selected in the compact database dialog
Case Else
MsgBox Err.Number & ":- " & vbCrLf & Err.Description
End Select
Resume exitCompactDB
End Sub




' This code was originally written by Terry Wickenden.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.

Sub DBRepair(strRepair As String)
On Error GoTo errDBRepair
SendKeys strRepair
SendKeys "{ENTER}"
RunCommand acCmdRepairDatabase
Exit Sub
errDBRepair:
Select Case Err
Case 2501
'Cancel Button selected in the repair database dialog
Case Else
MsgBox Err.Number & ":- " & vbCrLf & Err.Description
End Select
End Sub




I got all of the code from

http://home.clara.net/tkwickenden/

, there might be a better way to do it.

HTH!

-Brad

 

[oharab]

Have a look at my FAQ faq705-1955 on compacting a database from code. Basically you could just run the vbscript file that it uses manually.
If you want more info, let me know.
Cheers

Ben ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------