Compact and Repair Questions

Design by Sue

Registered User.
Local time
Today, 07:36
Joined
Jul 16, 2010
Messages
816
I have a database that is split in to a front end and backend. I have a table in the front end that is used as a temporary table which may be completed and cleared often during a day. There may be more than one user at any given time. It is my understanding that using a temporary table can cause the database to grow so I want to add a compact and repair to the quit button on the front end.

1. I have read that in the past there are issues with this (access 2007) I am using 2010. Are there any problems about it now or have they been corrected.

2. What are the issues?

3. Does the compact and repair work on the front and back ends both? (tables in both ends?)

4. What happens if one user closes while another is still using the system?

5. Any other thoughts on this?

Thanks
Sue
 
While you can certainly set the front end to compact and repair on exit, it tends to be a bit buggy. I had one file corrupt itself three times in one month because of that option. You're far better off moving your temporary table to the back end, and using code to compact that when the front end is closed down.

Now, question 4 concerns me. The whole point of front ends is that they are installed and run locally, which would mean that there would never be more than one user running a specific front end file. Is this not the case with yours? Are you having the users run the same front end file via the network?

In answer to actual question number 4, you cannot compact a database to which you do not have exclusive access. If two people are using the same mdb/accdb file, then neither can compact it.
 
Thank you.

The front ends are installed locally and each user will have their own (sorry that is what I meant but was not specific enough) That is the reason the temp table is in the front end. The info stored there is a collection of info not stored in the database as it is one time use only for that specific user. If I were to move the temp table to the back end, as I see it at this point, each front end would need to be customized to a specific table and that does not make sense.

Is it the compact and repair on exit that causes the issues? could I give a user a button to run the compact and repair with the program still open and then do the close?? (hope that makes sense)

EDIT: sorry a better term for the temp table is a local table that I am using as a temp table and is stored in the front end.
 
Last edited:
Ah, so it's a staging table, not an actual temporary table?

You cannot programmatically compact an open database, unfortunately. You can compact a DIFFERENT database as long as it's not in use, but to compact the one currently in use requires the use of either 'Compact on Close' - which is in the Options on the General tab - or the use of the actual Compact Database menu item in the menu/ribbon.

I can think of two workarounds offhand. If you're just using a few items of information, you can store them in hidden, unbound fields in either the main menu or even a hidden form loaded automatically. That removes the need for an actual staging table.

If you're doing a whole set of records, on the other hand, I'd suggest moving the staging table to the back end, and adding one field: UserID. Then in the process that fills the table, you add in the users windows id (which you can pull via the function:
Code:
Environ("UserName")

Then when you clean the info out of the table, just delete anything from the staging table with the current user's username. If your main menu has no links/data that requires keeping the backend open, then you can put a compact on close routine on the 'Exit' button that checks to see if the back end is in use (look for the .ldb/.lccdb file), and if not, compacts it. If necessary, I can provide you with one I use.
 
The table has 3 fields and I am not exactly sure of how many records - could be a few and possibly 50 or more. How would I store these as you stated, either in the main menu or hidden form. I am not clear of how to do this.

I can see the other method as a possibilty but would really prefer to keep the info in the front end (without adding the extra user id as that would require a lot of rework on what I already have.

I would appreciate your exit code for compacting as I have found some others on the internet but when I try to test them Access just throws up a lot of warning windows.

Thanks for you help!
 
If you have 50 records, then you definitely need to use a staging table. If you do not move it to the back end with the modifications I suggested (or perhaps someone else here can give a better idea - I'm no MVP), then you're pretty much stuck with turning on Compact on Close and hoping it doesn't corrupt the front end. Just to be fair, though I've only had that happen maybe 5 times in the last few years. Three of them were in the span of one month with one file, however....

Anyway, here's the compact procedure I use:

Code:
Public Function CompactSelectedDB(ByVal FilePath As String) As Byte
' ************************************************************
' Created by      : Scott L Prince
' Parameters      : Path to the database being backed up and compacted
' Result          : Creates a backup copy of the selected database, then runs a compact and repair on the back end.
' Returns         : 0   - Uncaught/unhandled exception
'                   1   - FilePath is not an MDB or ACCDB file
'                   2   - FilePath is in use
'                   255 - Compact successful
' Date            : 2-18-14
' Remarks         :
' Changes         :
' ************************************************************
On Error GoTo CompactSelectedDB_Err
 
    'Defaults
    CompactSelectedDB = 0
    'Determine if FilePath is to an Access database.
    If Not FilePath Like "*.mdb" And Not FilePath Like "*.accdb" Then
 
        'The designated file is not an Access DB.  Return 1 and terminate.
        CompactSelectedDB = 1
 
    'FilePath is an Access DB file that can be compacted.
    Else
 
        'Create necessary variables.
        Dim LockFilePath As String      'Path to the designated file's lock file
        Dim DotLoc As Long              'Location of the final period in the file name
        Dim Extension As String         'The file extension, taken from FilePath
 
        'Determine where the extension begins in FilePath.
        DotLoc = InStrRev(FilePath, ".")
 
        'Copy FilePath up to the location of the period to LockFilePath.
        LockFilePath = Left(FilePath, DotLoc - 1)
 
        'Save the extension from FilePath.
        Extension = Right(FilePath, Len(FilePath) - DotLoc + 1)
 
        'Determine if FilePath ends in ".mdb" or ".accdb".  All other file times have already been rejected.
        Select Case Extension
            Case ".mdb"     'Access 2003 file or older
                'Tack ".ldb" onto LockFilePath.
                LockFilePath = LockFilePath & ".ldb"
            Case ".accdb"   'Access 2007 or newer
                'Tack ".laccdb" onto LockFilePath.
                LockFilePath = LockFilePath & ".laccdb"
        End Select
 
        'Determine whether or not the lock file indicated by LockFilePath exists.
        If FileExists(LockFilePath) Then
 
            'The lock file exists, so the database cannot be backed up. Return 2 and terminate.
            CompactSelectedDB = 2
 
        'The lock file doesn't exist, so the database can be compacted.
        Else
            'Create necessary variables.
            Dim TempFilePath As String  'Full path to the temporary file created during the compact
            Dim BackupPath As String    'Full path to the backup file
 
            'Create TempFilePath in format 'C:\FolderPath\TEMP_FileName'.
            TempFilePath = GetFolder(FilePath) & "\" & "TEMP_" & GetFileName(FilePath)
 
            'Create BackupPath in format 'C:\FolderPath\FileName_BACKUP.extension'
            BackupPath = Left(FilePath, DotLoc - 1) & "_BACKUP" & Extension
 
            'Activate the hourglass cursor.
            DoCmd.Hourglass True
 
            'Turn warnings off.
            DoCmd.SetWarnings False
 
            'If TempFilePath exists, delete it.
            If FileExists(TempFilePath) Then Kill TempFilePath
 
            'Compact the database into TempFilePath
            Application.CompactRepair FilePath, TempFilePath, True
 
            'If BackupPath exists, delete it.
            If FileExists(BackupPath) Then Kill BackupPath
 
            'Rename the original file to the name in BackupPath.
            Name FilePath As BackupPath
 
            'Rename TempFilePath to FilePath.
            Name TempFilePath As FilePath
 
            'Return the 'success' code (255)
            CompactSelectedDB = 255
 
            'Turn warnings back on.
            DoCmd.SetWarnings True
 
            'Turn the hourglass back off.
            DoCmd.Hourglass False
        End If
    End If
CompactSelectedDB_Exit:
    Exit Function
 
CompactSelectedDB_Err:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
    "In procedure:" & vbTab & "CompactSelectedDB" & vbCrLf & _
    "Err Number: " & vbTab & Err.Number & vbCrLf & _
    "Description: " & vbTab & Err.Description, vbCritical, AppTitle
    Resume CompactSelectedDB_Exit
End Function

These are the supplementary functions you will need:

Code:
Public Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean = False) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. [URL]http://allenbrowne.com[/URL] June, 2006.
    Dim lngAttributes As Long
    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function
 
Public Function GetFileName(ByVal FullPath As String) As String
'**************************************************
'*  Created By:     Scott L Prince
'*  Created On:     10/2/13
'*  Modified:
'*  Purpose:        Returns a file name from the full path provided.
'*  Parameters:     Full path including file name
'*  Output:         File name, or empty string if no file name could be determined.
'*  Comments:
'**************************************************
On Error GoTo GetFileName_Err
 
    'Defaults
    GetFileName = ""
    'Only necessary if a FullPath has actually been passed.
    If FullPath <> "" Then
        Dim BackslashLocation As Long   'Location of the last "/" or "\" in the path
        'Locate the FINAL backslash.
        BackslashLocation = InStrRev(FullPath, "\")
 
        'If no "\" was found, then check for "/" (sharepoint file structure).
        If BackslashLocation = 0 Then BackslashLocation = InStrRev(FullPath, "/")
 
        'Determine if a slash was found.
        If BackslashLocation > 0 Then
 
            'A slash was found, so return the file name.
            GetFileName = Right(FullPath, Len(FullPath) - BackslashLocation)
        Else
 
            'No slash found, so return FullPath as the file name.
            GetFileName = FullPath
        End If
    End If
 
GetFileName_Exit:
    Exit Function
 
GetFileName_Err:
    MsgBox "An error has occurred in procedure 'GetFileName'!" & vbCrLf & vbCrLf & _
           "Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
           "Description:" & vbTab & Err.Description, vbOKOnly + vbCritical
    Resume GetFileName_Exit
End Function
 
Public Function GetFolder(ByVal FullPath As String, _
                          Optional ByVal IncludeLastSlash As Boolean = False) As String
'**************************************************
'*  Created By:     Scott L Prince
'*  Created On:     10/3/13
'*  Modified:
'*  Purpose:        Returns the folder portion of the supplied path (ie - C:\Temp\Test.doc returns C:\Temp)
'*  Parameters:     Full path including file name
'*  Output:         Path to indicated folder, or empty string if no folder was found.
'*  Comments:
'**************************************************
On Error GoTo GetFolder_Err
    'Defaults
    GetFolder = ""
 
    'Only execute if an actual value was passed to FullPath.
    If FullPath <> "" Then
        Dim BackslashLocation As Long   'Location of the FINAL backslash in the path.
 
        'Determine the location of the final backslash in the path.
        BackslashLocation = InStrRev(FullPath, "\")
 
        'If no "\" was found, then check for "/" (sharepoint file structure).
        If BackslashLocation = 0 Then BackslashLocation = InStrRev(FullPath, "/")
 
        'If there is a slash, use it to determine the path sans filename in FullPath.
        If BackslashLocation > 0 Then
            'If IncludeLastSlash is false, subtract 1 from Backslash location so the last one is not included.
            If Not IncludeLastSlash Then BackslashLocation = BackslashLocation - 1
            'Return the folder path.
            GetFolder = Left(FullPath, BackslashLocation)
        End If
    End If
GetFolder_Exit:
    Exit Function
 
GetFolder_Err:
    MsgBox "An error has occurred in procedure 'GetFolder'!" & vbCrLf & vbCrLf & _
           "Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
           "Description:" & vbTab & Err.Description, vbOKOnly + vbCritical
    Resume GetFolder_Exit
End Function

And FYI, AppTitle is just a constant I set in my Globals module with the module application. You will need to replace that with whatever text you want to appear on the title bar of any error messages.
 
Thank you so much - I will be working on this again tomorrow and let you know of my success (or not)

I am guessing that your procedure replaces the compact on close and it looks like it creates a back up copy before doing the compact (is that correct)

I will look into the user ID and putting it in the back end - but then compact and repairing the backend will be even more important as there will be more usage of this staging table
 
Had a few minutes before I need to close up for the day and was trying you compact code. But I guess I am going wrong somewhere. What I did was put your code in 4 modules, one for each function. Is this correct? Then what do I do? I tried to put the code on a command button with simply CompactSelectedDB, which has worked for other modules. This gives an error message argument not optional. Sorry I thought I had this one. (I put it on a temporary command button with only that line as I was planning on testing it - should I have put it on my quit button? and if so I can I test it to know that it worked.

Thanks again!
 
You can put the procedures in anywhere from one to four modules. Personally, because I can wind up with a lot of short functions, I generally put the Compact procedure in one called Utilities that includes it, a link refresher, my startup routine, and a couple other general-use functions. The other three generally go in a module I call FileHandling that includes them, an OpenFile procedure, and any other file-based procedures I need. You can handle it however you want, though, as long as they're in modules somewhere, as all four are public.

To run the compact function, you need to provide the full path to the database being compacted and repaired. What it does is this:

  1. Checks to see if the target database is in use. If it is, it returns an error code to the calling procedure and terminates.
  2. Compacts the target database into a new file.
  3. Renames the target database as database name + "_BACKUP" + database extension. (QV - Backend.mdb becomes Backend_BACKUP.mdb)
  4. Renames the new, compacted version of the target database to the original name. (QV - TEMP_Backend.mdb becomes Backend.mdb)

I generally place it in the EXIT button on the main menu, but you can also place it in a Compact Database button. Just make sure you pass the full path to the backend, and include code to interpret the response. (The returned codes are listed in my comments at the start of the procedure.)

The fact that you're getting an 'Argument not optional' error probably means you're not providing the path to the target database.
 
Last edited:
Temporary data doesn't belong in the Back End or the Front End.

Use what I call a Side End. It is a local database that is specifically for this purpose. It can be created as required and deleted on close. Alternatively the user can compact it since they have exclusinve access.

Google the forum for Side End to see previous discussions.
 
Galaxiom - I really like the thought that this can be created and deleted. I will search and hopefully I can find info on how to do this as right now I have no idea.

Frothingslosh - Thanks so much for your help - I will try it again with your additional help.
 
Frothingslosh - Thanks again but I can't figure out where to put the path. I tried DoCmd.SetParameter "FilePath", "C:\Folder\File\Datafile" but that still gave argument required. This is beyond my abilities.
 
Frothingslosh - Thanks again but I can't figure out where to put the path. I tried DoCmd.SetParameter "FilePath", "C:\Folder\File\Datafile" but that still gave argument required. This is beyond my abilities.

If you're putting in the full path including file name - ala C:\Folder\Subfolder\Database.mdb - and still generating an error, I would need to know where exactly the error is happening. If necessary, you can comment out all the On Error lines to force it to drop into the debugger and highlight the line in question.

And yeah, I think Galaxiom's idea is the way to go. :D
 
Galaxiom - I searched and cannot find enough info to create a side end - I can't even find how to set it up, let alone create and deleted tables to it. If you know of any links to help on this I would really appreciate it.

Thanks
 
Below are the two lines of code I have on the button. The error code highlights CompaceSelectDB.

DoCmd.SetParameter "FilePath", "C:\Folder\File\Datafile"
CompactSelectedDB
 
SetParameter, to my understanding, is only used with BrowseTo, OpenForm, OpenQuery, OpenReport, and RunDataMacro.

For a custom function, you should just pass the parameter directly. In the case of CompactSelectedDB, make sure to include the full name of the data file. That means you'd use one of the following:

Code:
CompactSelectedDB "C:\Folder\File\Datafile.accdb"
or
Code:
Select Case CompactSelectedDB("C:\Folder\File\Datafile.accdb")
    Case 1
        (Code to handle what you want done when target db is not an mdb/accdb file)
    Case 2
        (Code to handle what you want done when target db is in use)
    Case 255
        (Code to handle what you want done, if anything, when compact/repair is complete)
        (NOTE: This case can be left without code in it if you don't want to do anything at this step.)
    Case Else
        (Code to handle unanticipated errors)
End Select
 
Thanks - now I am getting the security warnings I was getting with the other code I tried "A potential security concern has been identified..." When I click Cancel - the code tries to execute but I get an error# 31523 unable to open the file... and it triggered the case else in the code (unanticipated error)

Is there a way to avoid the security concern message?

Any thoughts on the error 31523?
 
You're running either 2010 or 2013, right? That sounds like you're running the database in a non-trusted location. If that's the case, you need to either manually enable macros every time you run your database, or else add the location containing the database to your trusted locations.
 
Temporary data doesn't belong in the Back End or the Front End.

Use what I call a Side End. It is a local database that is specifically for this purpose. It can be created as required and deleted on close. Alternatively the user can compact it since they have exclusinve access.

Google the forum for Side End to see previous discussions.

Finally found the solution - thank you so much Galaziom for your suggestion. For others who want to create a side end database the info I used was found herehttp://help.wugnet.com/office2/DB-Size-Increase-ftopict844621.html

The answer was:

"One way to work around this is to create the make table in another database.
I will frequently create a apptemp.mdb file and create tables in that which
are are generally temporary in nature. Then, I link those tables into my
application, and rather than running make-table queries, I delete from and
insert records into these tables. This will prevent your application file
from bloating, but will require that you occassionally compact the
apptemp.mdb file. "

I am hoping to store this side end with the user's FE so each user has their own apptemp.mdb file and there will be no conflicts between users (at least that is my theory - if anyone sees a fault in it please warn me!)

Thanks
 
Amazing! I think I have it worked out now. Frothingslosh you are fantastic to stick with me through this. I changed the settings for Trusted location and it worked. I will now set this up to compact and repair my apptemp.mbd and all should be taken care of!

Thank you both.
 

Users who are viewing this thread

Back
Top Bottom