Compact on close or not! 1

[weezles]

Hi

I'm wondering if anyone can give me any information on the pro's and cons of setting database to compact on close.

Thanks

Lou

 

[DoubleD]

If only one user will ever be in the database at a time, then Compact on Close is a good option. It cleans things up and keeps your db running smooth.

If multiple people will be in the db simultaneously, avoid this as it will fail more often than it works, and can lead to corrupting the db.

I am what I am based on the decisions I have made.

DoubleD

 

[KornGeek]

The way I have my system set up, I have a back-end database shared by many users, and each user has a copy of the front-end. The front-end is set to compact on close, which keeps it from growing out of hand. However, Compact on Close does not affect the back-end, which is where the bloating is much more likely to occur.

I use code to determine if other users are in the database. If not, I then use code to compact the back-end as I exit.

 

[DoubleD]

KornGeek has a good solution.

I am what I am based on the decisions I have made.

DoubleD

 

[LittleSmudge]

KornGeek

Compacting the BE db on close of the FE sounds interesting.

Would you care to share the code that you use - please.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Checking]

Hi there,
Following triggers a back-end compact only when no user connected anymore.
When using an exit button on the front-end, the application closes all forms & opens a kind of maintenance form. So no back-end tables are connected anymore. An 'OnOpenEvent' with following code will compact the back-end when no user connected to it and will close the front-end afterwards.
First it will make a copy Back_End.mdb to Back_End2Comp.mdb, delete the original Back_End.mdb in order to prevent occasional log-in errors from other users during the whole process.Then it compacts the Back_End2Comp.mdb (Back-end to compact) to Back_EndComp.mdb (Back_End compacted). Lastly it copies the Back_EndComp.mdb again to the original Back_End.mdb and closes the Front_End.
Also you can play with it to compact the Front_End within the same Event.
Hope it's usefull. Best regards !!
'-------------------------------------
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("\\Server\Folder\Back_End.ldb") = False Then
' MsgBox ("BackEnd database may be compacted")
fs.CopyFile "\\Server\Folder\Back_End.mdb", "\\Server\Folder\Back_End2Comp.mdb"
Kill ("\\Server\Folder\Back_End.mdb")
DBEngine.CompactDatabase "\\Server\Folder\Back_End2Comp.mdb", "\\Server\Folder\Back_EndComp.mdb", ";pwd=PASSWORD", , ";pwd=PASSWORD"
fs.CopyFile "\\Server\Folder\Back_EndComp.mdb", "\\Server\Folder\Back_End.mdb"
Kill ("\\Server\Folder\Back_End2Comp.mdb")
Kill ("\\Server\Folder\Back_EndComp.mdb")
DoCmd.Quit
Else
'MsgBox ("BackEnd database still in use")
DoCmd.Quit
End If
'-------------------

 

[RandyMyers]

I mainly use Access front ends that are linked to SQL data back bones. Since SQL Server has an option to Auto-Shrink it pretty much takes care of itself.

I always will compact a modified front end before issuing it to the end users.

Since the front ends are the control screens and really do not have any data storage then they rarely require any compaction.

If I were using Access Jet Engine data base for the back end than I would make it part of my routine to periodically compact it (first thing in morning) since it is easy to tell if it currently has anyone connected to it (the .ldb file).

 

[Ed2020]

I think users compacting the database is very dodgy. If the user cancels the process via task manager (a distinct possibility on large databases that will take a long time) then Access can do some very unwanted things with your data.

Another potential problem is if the drive runs out of disk space during the process. Access seems unable to detect this problem (unless it's been fixed in 2003) and can lose a lot of data as a result.

I prefer to design databases that will keep bloat to a minimum and keep the job of compacting to myself.

Ed Metcalfe.

Please do not feed the trolls.....

 

[hermanlaksko]

KornGeek
Would you care to share the code that you use - please.
I'll join LittleSmudge's cry

Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[Ed2020]

Sub CompatDB()
On Error GoTo CompactDB_Err
Const conFilePath = <put your directory path here>

DBEngine.CompactDatabase conFilePath & "<put your filename here>", conFilePath & "<put temporary filename here>"

If Dir(conFilePath &"<filename without extension>.bak") <>"" Then
kill conFilePath &"<filename without extension>.bak"
End If

Name conFilePath& "<put your filename here>" As conFilePath &"<filename without extension>.bak"
Name conFilePath & "<put temporary filename here>" As conFilePath & "<put your filename here>"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_compactDB

End Sub

Please do not feed the trolls.....

 

[fburges]

I like the code ED2020 used. I'd like to share an idea I once acted on. Several years ago I used DBEngine.CompactDatabase in a standalone db. The only thing the standalone db does is compact other db's. It can compact more db's in a shorter period of time than it takes me just to open one db. You select the folder you want to find/compact databases in, and whether or not to compact all databases in that folder, or just the selected db. While it is compacting the db's, the main form receives feedback on its progress. Most of the code went to error checking, launching the system file open dialog to select files, and things like that. But the act of compacting happened in that one lone statement. It's an idea I'd like to share with you. I prefer this method over compacting a db when you quit the session because it gives me more control over when the process takes place, and it is really fast.

 

[KornGeek]

The code I use to do the compact goes a little something like this:

Public Sub CompactDB(strDBName As String)
  On Error GoTo CompactDB_Err

  Dim strDBNameRoot as String
  Dim strTempDBName As String
  Dim strDBBakName As String

  'check to see if DB exists  
  If Dir(strDBName) = "" Then
    Exit Sub
  End If

  'remove .mdb extension to get root of the file name
  If Right(strDBName, 4) = ".mdb" Then
    strDBNameRoot = LStrip(strDBName, 4)
                   'LStrip = Left(strDBName, Len(strDBName) - 4)
                   'For some reason I make a lot of typos writing this
                   '  so I created a function for it.
  Else
    strDBNameRoot = strDBName
  End If

  'set up file names for temp and backup files  
  strTempDBName = strDBNameRoot & "_c.mdb"
  strDBBakName = strDBNameRoot & ".bak"

  'remove previous temp file    
  If Dir(strTempDBName) <> "" Then
    Kill strTempDBName
  End If

  'compact the database
  DBEngine.CompactDatabase strDBName, strTempDBName

  'remove the previous backup file
  'this only happens if the compact succeeded
  If Dir(strDBBakName) <> "" Then
    Kill strDBBakName
  End If

  'rename files to move the old database file into backup filename
  '  and the temp file into the database filename
  Name strDBName As strDBBakName
  Name strTempDBName As strDBName

Exit_CompactDB:
    Exit Sub

CompactDB_Err:
    If Err.Number = 3356 Then
      'I forgot what this error was, but intentionally ignored it
    Else
      StdErrMsg Err.Number, Err.Description
      'My standard error message routine
    End If
    Resume Exit_CompactDB

Sorry it took so long to post this. I haven't been checking these message boards regularly lately. Before calling this function, you first need to make sure your users are out of the back-end. I will post that code later.

 

[PHV]

I forgot what this error was
Err = 3356 when the database is open in exclusive mode.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hermanlaksko]

From Ed2020's code I developed this. THANKS ED2020 - a star is on the way !
Change the following var:
YrAttTbl = a table name - the table must be attached from the database you want to back/compact.

YrAppName = Your application name.

YrForm = all forms must be closed - i.e. all forms that uses data from the attached database. You can use CloseAllForms "" if you want all forms to close - Note this also closes any open reports.

Function ComBackDB() 'Compact & backup mdb
On Error GoTo CompactDB_Err
Dim ResStr, Nbr As Integer
ResStr = Mid(CurrentDb.TableDefs("YrAttTbl").Connect, InStr(1, CurrentDb.TableDefs("YrAttTbl").Connect, "=") + 1, Len(CurrentDb.TableDefs("YrAttTbl").Connect))
Do While right(ResStr, 1) <> "."
ResStr = Left(ResStr, Len(ResStr) - 1)
Loop
ResStr = Left(ResStr, Len(ResStr) - 1)
CloseAllForms "YrForm"
If Dir(ResStr & ".bak") <> "" Then
Kill ResStr & ".bak"
End If
DBEngine.CompactDatabase ResStr & ".MDB", ResStr & ".Bak"
If Dir(ResStr & ".mdb") <> "" Then
Do While Dir(ResStr & Format(Nbr, "000") & ".mdb") <> ""
Nbr = Nbr + 1
Loop
Name ResStr & ".mdb" As ResStr & Format(Nbr, "000") & ".mdb"
End If
Name ResStr & ".bak" As ResStr & ".mdb"
MsgBox "Backup created " & ResStr & Format(Nbr, "000") & ".mdb", , "YrAppName"
Exit_CompactDB:
Exit Function
CompactDB_Err:
MsgBox Err.Description, , "YrAppName"
Resume Exit_CompactDB
End Function
Public Function CloseAllForms(NotThis As String)
Dim dbs As DAO.Database, ctr As Container, Doc As Document, Doc_Name
Set dbs = CurrentDb
Set ctr = dbs.Containers!Forms

For Each Doc In ctr.Documents
Doc_Name = Doc.Name
If Doc_Name <> "MrHide" Then
If Doc_Name <> NotThis Then DoCmd.Close acForm, Doc_Name
End If
Next Doc
Set ctr = dbs.Containers!Reports
For Each Doc In ctr.Documents
Doc_Name = Doc.Name
DoCmd.Close acReport, Doc_Name
Next Doc
Set dbs = Nothing
End Function

Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[JerryKlmns]

Compact-Repair-Encrypt with JRO on a user-level secured Access database thread709-1013953