Compact BE

[Guest]

I have created an analysis tool that uses a back-end DB. The BE grows in size
very quickly as it updates, deletes and appends data some of which is done in
a loop. I would like to be able to compact the BE programatically with out
add in software (I want to be able to distribute with out adding software).
Does any know how to compact a BE from a FE using VBA?

Thanks,
Robert

 

[Dirk Goldgar]

I have created an analysis tool that uses a back-end DB. The BE grows
in size very quickly as it updates, deletes and appends data some of
which is done in a loop. I would like to be able to compact the BE
programatically with out add in software (I want to be able to
distribute with out adding software). Does any know how to compact a
BE from a FE using VBA?

I've used this code:

'------ start of code ------
' Compact the database into the temp file.
Application.DBEngine.CompactDatabase strBackendPathAndName,
strTempFile
Application.DBEngine.Idle dbRefreshCache

' If that went well, rename the database to a different name.
strOldFile = strBackendFolder & strFileNameNoExt &
"_UNCOMPACTED.mdb"

If FileExists(strOldFile) Then
Kill strOldFile
End If

Name strBackendPathAndName As strOldFile

' If that went well, rename the temp file to the original DB name.
Name strTempFile As strBackendPathAndName

' If that went well, delete the old, uncompacted back-end.
Kill strOldFile
'------ end of code ------

Code preceding the above snippet determines/derives the correct values
for:

strBackendPathAndName
strBackendFileName
strBackendFolder
strFileNameNoExt

 

[Rick Brandt]

I have created an analysis tool that uses a back-end DB. The BE grows
in size very quickly as it updates, deletes and appends data some of
which is done in a loop. I would like to be able to compact the BE
programatically with out add in software (I want to be able to
distribute with out adding software). Does any know how to compact a
BE from a FE using VBA?

Thanks,
Robert

Providing that nothing in the back end is currently "in use"...

dbEngine.CompactDatabase "PathToSourceMDB","PathToDestinationMDB"
Kill "PathToSourceMDB"
Name "PathToDestinationMDB" as "PathToSourceMDB"

 

[Guest]

Robert,
I created a unique database for the sole purpose of compacting all my FE /
BE databases that I have in Production. This procedure is executed at night
when all databases are closed. What it does is compact the DB under new name,
delete the production DB, Rename the compacted DB into the production DB name
so the users have access the the same name in their short cuts and then make
a copy of the new compacted db for back up purpose.

You need 1 table and 1 form.

table name ;DBNames
FieldBID, Autonumber no duplicates
field;DBFolder, text (Store the full path to each of your databases
excluding file name Ex; C:\My Directory)
fied;DBName, text (store the name of each of your databases including the
extension ex; CSI.mdb)

Form name; CompactDB
Under the event tab, go to Timer Interval and type 60000
Under the event tab, got to On Timer and paste the following code;


'==================================================================
'The Timer event runs this code every minute. It compares your
'system time with the StartTime variable. When they match, it
'begins compacting all databases in the DBNames table.

'==================================================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "05:00 AM"
' If StartTime is now, open the DBNames table and start
compacting
If Format(Now(), "medium time") = Format(StartTime, "medium
time") Then
Dim RS As Recordset, DB As Database
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames")
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder") & "\" & RS("DBName")
' Create a new name for the compacted database.


NewDBName = Left(DBName, Len(DBName) - 4)
NewDBName = NewDBName & "1" & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
Kill DBName

RS.MoveNext
Loop


'-----------------------------------------------------------------------------------------------
'This is the Back End of my Database
'this code rename the compacted Database

Dim Oldname, NewName
Oldname = "G:\Corporate QA\Unrestricted\Quality
Assurance1.mdb": NewName = "G:\Corporate QA\Unrestricted\Quality
Assurance.mdb"
Name Oldname As NewName

'This code copy the new database into another directory
for back up purpose

Dim SourceFile, DestinationFile
SourceFile = "G:\Corporate QA\Unrestricted\Quality
Assurance.mdb" ' Define source file name.
DestinationFile = "G:\Corporate QA\BackUp\QA BU.mdb"
' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to
target.


'-----------------------------------------------------------------------------------------------
'This is the Back End of my Database
'this code rename the compacted Database

Dim Oldname1, NewName1
Oldname1 = "G:\Corporate QA\Unrestricted\CQA
Backstage1.mdb": NewName1 = "G:\Corporate QA\Unrestricted\CQA Backstage.mdb"
Name Oldname1 As NewName1

'This code copy the new database into another directory
for back up purpose

Dim SourceFile1, DestinationFile1
SourceFile1 = "G:\Corporate QA\Unrestricted\CQA
Backstage.mdb" ' Define source file name.
DestinationFile1 = "G:\Corporate QA\BackUp\CQA_FE
BU.mdb" ' Define target file name.
FileCopy SourceFile1, DestinationFile1 ' Copy source
to target.

'-------------------------------------------------------------------------------------------------
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
RS.Close
DoCmd.Quit acSaveYes
End If

Please make a copy of your database before implementing the code just in
case...

Hope this helps
Daniel