compact and repair via vb

[thread]

Hi all,
i would like to know if there is a realistic way to make compact and
repair via visual basic?

 

[Guest]

Here's some old code I've used...

Private Sub cmdMainFix_Click()
Dim ZZ As Database, RS As DAO.Recordset
Dim NewDBName As String, sDBName As String, MM$, NN$, Resp%
Dim FileLength, FileNum As Integer, Pauser As Double, EndTime As Date
On Error GoTo Blake1
MM = "Are You Sure You Want To Compact" & vbCrLf
MM = MM & "The Main Database?"
NN = "Compact The Main Database?"
Resp% = MsgBox(MM, vbYesNo + 256, NN)
If Resp% = vbYes Then
Screen.MousePointer = 11
MM = "Please Wait - The Main Database Is Being Compacted."
GoGoGo.SetFocus: lblWaiter.Caption = MM
Call NoButtons
Set ZZ = CurrentDb()
Set RS = ZZ.OpenRecordset("DBMainNames")
With RS
.MoveFirst
Do Until .EOF
sDBName = !DBName
'MsgBox sDBName
'New name for compacted DB
'''3/12/01 - NewDBName = Left(sDBName, Len(sDBName) - 4)
'MsgBox NewDBName
'MS example uses old name plus CurDate
'NewDBName = NewDBName & " " & Format(Date, "MMDDYY") & ".mdb"
'''3/12/01 - NewDBName = NewDBName & "AAA.mdb"
NewDBName = "C:\BobDev\ABC.mdb"
If Dir(NewDBName) <> "" Then
Kill NewDBName
End If
DBEngine.CompactDatabase sDBName, NewDBName
'FileCopy SourceFile, DestinationFile
FileCopy NewDBName, sDBName
'MsgBox NewDBName
If Dir(NewDBName) <> "" Then
Kill NewDBName
End If
.MoveNext
Loop
.Close: Set RS = Nothing: ZZ.Close: Set ZZ = Nothing
End With
Else
Exit Sub
End If
Pauser = 3
EndTime = DateAdd("s", Pauser, Now())
While EndTime >= Now()
DoEvents
Wend
FileNum = FreeFile()
NN = "\\Main Database\pas3dot2.mdb"
Open "\\Main Database\pas3dot2.mdb" For Input As #FileNum
FileLength = LOF(FileNum)
Close #FileNum
LblMainSize = "The Main Was " & Format(FileLength, "##,##") & " Bytes " _
& "When Last Compacted On " & Format(Now(), "mm/dd/yyyy") & ", " _
& Format(Now(), "Medium Time") & "."
txtActMain.Caption = "The Main Is " & Format(FileLength, "##,##") & " Bytes."
FileNum = FreeFile()
NN = "C:\BobDev\CompFETMM.mdb"
Open "C:\BobDev\CompFETMM.mdb" For Input As #FileNum
FileLength = LOF(FileNum)
Close #FileNum
lblCompacter.Caption = "The Compactor Is " & Format(FileLength, "##,##") & "
Bytes."
lblWaiter.Visible = False: Screen.MousePointer = 1
MsgBox "The Main Database Has Been Compacted.", , _
"Plastics Compactor Database"
cmdBacker.Enabled = True
Blake2:
Call HeyButtons
Call CkLoaded
Screen.MousePointer = 1: Exit Sub
Blake1:
Screen.MousePointer = 1
Select Case Err
Case 3024
MM = Err.Description & vbCrLf & vbCrLf
MM = MM & "Please Check The Database" & vbCrLf
MM = MM & "You Have Listed To Compact."
MsgBox MM: Resume Blake2
Case 3055
MM = Err.Description & vbCrLf & vbCrLf
MM = MM & "Please Check The Database" & vbCrLf
MM = MM & "You Have Listed To Compact."
MsgBox MM: Resume Blake2
Case 3356
MM = "The Main Database Can NOT" & vbCrLf
MM = MM & "Be Compacted Now. Please See Message Below."
lblWaiter.Caption = MM
MM = "The Main Database Is Being Used." & vbCrLf & vbCrLf
MM = MM & "Please Check " & """Who's Logged On?""" & vbCrLf
MM = MM & "To See The User." & vbCrLf & vbCrLf
MM = MM & "No User Can Be Using The Main" & vbCrLf
MM = MM & "Database While It Is Being Compacted." & vbCrLf & vbCrLf
MsgBox MM: Resume Blake2
Case Else
MsgBox "Error Number " & Err.Number & " " & Err.Description: Resume Blake2
End Select
End Sub

HTH - Bob

 

[thread]

hi bob,
i'm using ADO but i see that you are using DAO i believe there is not
so much diffrence but the issue is that i see that accualy the compact
and repair is been working outside from the file,please correct me if
i'm wrong.

 

[Dirk Goldgar]

Hi all,
i would like to know if there is a realistic way to make compact and
repair via visual basic?

If by "via visual basic" you mean, "via VBA within Access", and if you
mean you want to compact the current database, then it can be done one
of two ways, depending on whether the code is running in Access 2000 or
later, or in Access 97. In Access 97, you must use a helper application
of some sort; see this link:

http://www.mvps.org/access/general/gen0013.htm

However, if you are using Access 2000, 2002, or 2003, you can do it
within Access; see this link:

http://www.mvps.org/access/general/gen0041.htm

 

[aaron.kempf]

use SQL Server you ****ing PANSY

-Aaron
ADP Nationalist

 

[thread]

hi dirk,
thank you very much for your help,it was very helpful,the issue is that
after the compact and repair is finishing its raising an error for this
method accDoDefaultAction on CommandBars,what should i change becaouse
the compact&repair is working but when i'm pressing on open on the
security warning(usual default at start) it will raise the error

 

[thread]

hi dirk,
i think the issue is probbly becaouse of the looping the application is
opening again and activating this command,maybe there is a way to
somehow limit it to just once a day or just when the size of the file
is growing too much

 

[Dirk Goldgar]

hi dirk,
thank you very much for your help,it was very helpful,the issue is
that after the compact and repair is finishing its raising an error
for this method accDoDefaultAction on CommandBars,what should i
change becaouse the compact&repair is working but when i'm pressing
on open on the security warning(usual default at start) it will raise
the error

I'm sorry, I don't understand that last part of what you wrote. But
maybe if you set macro security to Low you won't get the message you're
talking about.

 

[Dirk Goldgar]

hi dirk,
i think the issue is probbly becaouse of the looping the application
is opening again and activating this command,maybe there is a way to
somehow limit it to just once a day or just when the size of the file
is growing too much

You certainly don't want to have this autocompact function run,
ungoverned, at startup. That would lead to in infinite loop. As you
say, you could limit it to the first time the application is run on any
given day, or by the size of the file -- but what happens when the
*compacted* size of the file hits that cutoff?

To make it run only once a day, have one-record table called, maybe,
"Settings". In that table, have a date/time field named
"LastCompacted". Then your auto-compaction procedure will do the
following:

1. Use DLookup() to find out if LastCompacted = Date().

2. If so, exit the procedure without compacting. If not, continue with
the following steps.

3. Run an update query to set LastCompacted = Date().

4. Compact the database.