Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rating: Thread Rating: 5 votes, 5.00 average. Display Modes
Old 06-21-2012, 03:08 AM   #1
jonathanchye
Newly Registered User
 
Join Date: Mar 2011
Posts: 448
Thanks: 27
Thanked 19 Times in 15 Posts
jonathanchye is on a distinguished road
[Code] Compact and Repair Backend

Sorry if this is posted before but I thought I will contribute back after the awesome help I've got here

What I have is a Compact & Repair code specifically for split backends. I have tested this to be working with .accdb files and I am pretty sure it will be easy to modify to accomdate .mdb files.

The code will first check if the backend is opened by checking for a record locking file (.laccdb). If the that file doesn't exist we can be 99% sure the backend is not being opened and ready for a Compact & Repair!

The code will also create a backup of the original file so you can easily revert back if possible and it is also handy to see how much space you've saved :P I've also built in basic error checking which can be improved upon.

I am running this on a remote server on a timed event so every night this code runs and tries to compact & repair the backends. I have a log file which keeps track of which process failed and which didn't

You will need to create a new module. I have called this "basCompactBE". Paste the code below in this new module :

Code:
Option Compare Database
Option Explicit

Public Function CheckLock(strPath As String) As Boolean
' This checks with the record locking file exists (backend is open if it exist
If Len(Dir(strPath)) = 0 Then
    ' doesn't exist
'    MsgBox "False"
    CheckLock = False
Else
'    MsgBox "True"
    CheckLock = True
End If
End Function

Function CompactBackend(strPath As String, strFileName As String) As Boolean
'strPath is the path to the folder containing your backend
'strFileName is the backend's full filename ie "my_Backend.accdb"

On Error GoTo Err_CompactBackend

Dim mNewPath As String
Dim mPath As String

'Names the compacted DB with _Compacted suffix
mNewPath = strPath & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "_Compacted.accdb" 
mPath = strPath & "\" & strFileName  

'Delete old _Compacted file if exist
If Len(Dir(mNewPath)) Then
Kill mNewPath
End If
'MsgBox "Compacting database"
Application.CompactRepair LogFile:=True, SourceFile:=mPath, DestinationFile:=mNewPath
' If this is successful then we rename the source file with the compacted one
'First backup the old uncompacted source file
Dim tempPath, tempPath2 As String
tempPath2 = mPath
'Adds a _Backup suffix
tempPath = strPath & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "_Backup.accdb"
'Make a backup copy of the Source file 
FileCopy mPath, tempPath
'Kill the original file
Kill mPath
'Rename the new compacted file to original filename
Name mNewPath As tempPath2
CompactBackend = True
'MsgBox "Done Compacting database"

Exit_CompactBackend:
Exit Function

Err_CompactBackend:
MsgBox Err.Description
CompactBackend = False
Resume Exit_CompactBackend

End Function
This is an example of how I use this module :

Code:
Public Sub CompactBE()
On Error GoTo Err_Handler
DoCmd.SetWarnings False
If CheckLock("X:\Access Databases\xxx\BE\xxx_be.laccdb") = False Then
    If CompactBackend("X:\Access Databasesxxx\BE", "xxx_be.accdb") = True Then
            Me.Last_Process_Ran = "Compacted xxx_be.accdb"
            Me.Last_Process_Ran_Timestamp = Now()
            Me.Form_Name = "frmScheduler1"
            Me.Online_status = True
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
    Else
            Me.Last_Process_Ran = "Failed to compact xxx_be.accdb"
            Me.Last_Process_Ran_Timestamp = Now()
            Me.Form_Name = "frmScheduler1"
            Me.Online_status = True
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
    End If
Else
            Me.Last_Process_Ran = "Backend Open xxx_be.accdb"
            Me.Last_Process_Ran_Timestamp = Now()
            Me.Form_Name = "frmScheduler1"
            Me.Online_status = True
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If

jonathanchye is offline  
The Following 3 Users Say Thank You to jonathanchye For This Useful Post:
diberlee (08-21-2014), HWiersma (11-29-2013), Rx_ (07-26-2012)
Old 07-26-2012, 08:19 AM   #2
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Re: [Code] Compact and Repair Backend

Thanks for posting this. While my backend database was recentlly moved to SQL Server, this is an important maintenance activity for everyone that keeps data in Access.

For audience who are new to Access, this is generally what happens during the process:
The Compact and Repair takes out the trash. Deleted items and records are still in the file until you compact it. That is why the database gets so large.
Access may have temporary tables that it uses. These are removed.
Very importantly, reindexes the indexed fields.
Access indexex are double linked (both directions) so if an idnex is corrupted this will usually fix it. Don't wait until the backup index is also corrupted.
It probably reorders the physical order of records in the tables. (I need to check for all versions.)
Depending on your version of Access it will probably reset the next autonumber value to 1 more than the highest value or to the seed value if the table is empty.

This is what I remember from the Access 97 course and certification exam. If anyone else has additional information or links, let me encourage you to add them.

If this isn't enough reasons already, then JUST DO IT on some schedule.
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline  
The Following User Says Thank You to Rx_ For This Useful Post:
diberlee (08-22-2014)
Old 12-16-2014, 12:21 PM   #3
dijilator
Newly Registered User
 
Join Date: Oct 2014
Location: Elk Grove, CA (Sacramento)
Posts: 14
Thanks: 6
Thanked 0 Times in 0 Posts
dijilator is on a distinguished road
Re: [Code] Compact and Repair Backend

I don't know VBA, but have successfully implemented a public module already and so would like to try on this one. After I backup the frontend and backend of my database, I'd like to test implement this code, based on the instructions you give of what the StrPath and StrName are supposed to be.

But for the 2nd part, where you say, "This is an example of how I use this module :," would you give some starting point instructions? I was able to follow Allen Browne's starting point instructions for ser-56 on his website, and thus implement his code successfully, even though I couldn't have written it myself.

dijilator is offline  
Old 09-10-2015, 06:30 AM   #4
emihir0
Newly Registered User
 
Join Date: Apr 2015
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
emihir0 is on a distinguished road
Re: [Code] Compact and Repair Backend

Sorry for necroing the thread in advance but I have a question on WHEN/WHERE should I run the `CompactBE` sub?

I have made my front-end to auto-close on 2 hours inactivity and considering it's only used by 3-4 users, chances of the back-end being locked (due to FE being opened) at 2am is very slim.

Now I saw your post and wanted to use it, but I'm not exactly sure on scheduling the (above mentioned) piece of code.

Could you point me in right direction please?
emihir0 is offline  
Old 03-09-2017, 03:24 AM   #5
Damo1412
Newly Registered User
 
Join Date: Nov 2010
Posts: 65
Thanks: 14
Thanked 0 Times in 0 Posts
Damo1412 is on a distinguished road
Re: [Code] Compact and Repair Backend

Hi,

I know that this is an old post but I've just come across it and would really like to implement this into my database but I am doing something wrong, I'm just not sure what.

I have created a new database with a table called "Table1", a form called "frmScheduler1" and a module called "basCompactBE".

The table has the following fields:
  • ID - an autonumber
  • Last_Process_Ran - Short Text
  • Last_Process_Ran_Timestamp - Date / Time
  • Online_status - Short Text


The module has the following code:
Code:
Option Compare Database
Option Explicit

Public Function CheckLock(strPath As String) As Boolean
' This checks with the record locking file exists (backend is open if it exist
If Len(Dir(strPath)) = 0 Then
    ' doesn't exist
'    MsgBox "False"
    CheckLock = False
Else
'    MsgBox "True"
    CheckLock = True
End If
End Function

Function CompactBackend(strPath As String, strFileName As String) As Boolean
'strPath is the path to the folder containing your backend
'strFileName is the backend's full filename ie "my_Backend.accdb"

On Error GoTo Err_CompactBackend

Dim mNewPath As String
Dim mPath As String

'Names the compacted DB with _Compacted suffix
mNewPath = strPath & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "_Compacted.accdb"
mPath = strPath & "\" & strFileName

'Delete old _Compacted file if exist
If Len(Dir(mNewPath)) Then
Kill mNewPath
End If
'MsgBox "Compacting database"
Application.CompactRepair LogFile:=True, SourceFile:=mPath, DestinationFile:=mNewPath
' If this is successful then we rename the source file with the compacted one
'First backup the old uncompacted source file
Dim tempPath, tempPath2 As String
tempPath2 = mPath
'Adds a _Backup suffix
tempPath = strPath & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "_Backup.accdb"
'Make a backup copy of the Source file
FileCopy mPath, tempPath
'Kill the original file
Kill mPath
'Rename the new compacted file to original filename
Name mNewPath As tempPath2
CompactBackend = True
'MsgBox "Done Compacting database"

Exit_CompactBackend:
Exit Function

Err_CompactBackend:
MsgBox Err.Description
CompactBackend = False
Resume Exit_CompactBackend

End Function

The form is linked to the table and for testing purposes, I have a button to test that everything works. The "on click" code is:
Code:
Option Compare Database

Private Sub Command9_Click()
Public Sub CompactBE()
On Error GoTo Err_Handler
DoCmd.SetWarnings False
If CheckLock("\\path to back end\Database name.laccdb") = False Then
    If CompactBackend("\\path to back end", "Database name.accdb") = True Then
            Me.Last_Process_Ran = "Compacted Database name.accdb"
            Me.Last_Process_Ran_Timestamp = Now()
            Me.Form_Name = "frmScheduler1"
            Me.Online_status = True
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
    Else
            Me.Last_Process_Ran = "Failed to compact Database name.accdb"
            Me.Last_Process_Ran_Timestamp = Now()
            Me.Form_Name = "frmScheduler1"
            Me.Online_status = True
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
    End If
Else
            Me.Last_Process_Ran = "Backend Open Database name.accdb"
            Me.Last_Process_Ran_Timestamp = Now()
            Me.Form_Name = "frmScheduler1"
            Me.Online_status = True
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If
End Sub
When I try to run the code I receive an error message "Expected End Sub". From my research online, I think that the problem is with the lines:
Code:
Private Sub Command9_Click()
Public Sub CompactBE()
In that I am calling a function within a sub however I do not know how to correct this.



Any help would be greatly appreciated

Damo1412 is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Kick users, Compact and repair, create backup, auto compact on close? charliemopps Modules & VBA 3 06-03-2010 07:23 AM
Automatically Compact and Repair Code Jakboi General 22 01-24-2007 07:19 AM
Compact & Repair DB with code? MsLady Tables 3 02-09-2006 08:59 PM
Calling 'Compact and Repair' in code Dugantrain Forms 2 03-03-2003 10:55 AM
[SOLVED] compact/repair backend db Hammy General 3 01-07-2003 01:58 PM




All times are GMT -8. The time now is 08:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World