Solved C&R Backend

Kayleigh

Kayleigh

Member
Local time
Today, 13:17
Joined
Sep 24, 2020
Messages
709
Hi,
I'm looking to compact and repair the backend database on a nightly basis.
I found this useful code as a starter: https://www.access-programmers.co.u...ct-a-back-end-or-a-front-end-database.221398/
However, I got stuck at the source path name. How do I explicitly state where the database file is located - it assumes it is in my documents directory.
Thank you
 
as per the instruction

Call CompactRepair(sSourceFile)

' where 'sSourceFile' is the full path to the

' database you want to compact.
Click to expand...
so perhaps something like

c:\somefolder\somefolder\BEfile.accdb
 
Ah silly I didn't notice that!
Now when I run with a valid URI I get eror 3704 that I'm attempting to open a database which is already open - obviously it is open, as its backend of current db. How do I work around this?
 
In general, you trigger some tiny, stand-alone DB to do the dirty work for you.
 
Hi. Performing a C&R over the network is not recommended. Perhaps you could implement something similar to this one instead.

 
To be honest, what I always did was manual during a scheduled maintenance period.

1. Copy the DB as-is to a secondary location
2. Rename the BE file in its shared location
3. Manually open the BE in that secondary location
4. C&R
5. Copy the C&R'd file to the shared location with the right name.
6. Make the renamed copy into the backup copy - move to an archive folder and rename it to some backup name.

Yes, you can automate this, but if something goes wrong, I think you want to be in control at the time.
 
Or use SSMS after kicking everyone out, honestly, I don't get the compact and repair every night. I did it maybe a couple of times a year on my backends.
 
you cannot compact and repair whilst the BE is in use by others. As others have said, you need to boot them out first. A number of ways to do this on this and other forums. The problem comes when you decide to time it for 3am when nobody is on the system - but someone went home without logging off from the BE.

compacting only really has an impact on file size if you delete records - which should typically be when you are archiving data.
 
I have a built-in method to kick all users out of system. But if I am logged in to back end, it is still open technically?
 
think that depends on whether it is opened exclusively or not. Close all forms which are connected to the BE
 
If it has a .LDB file then it was counted as "open."
 
Is there any way to test if a database has been opened in exclusive mode?
 
Krayna said:
Is there any way to test if a database has been opened in exclusive mode?
Click to expand...
One way is to try to connect to it. If you get an error, then it's probably opened exclusively.

Just thinking out loud...
 
Maybe I'm losing the thread here, but if the BE is open any way at all (by someone else) then having a C&R on it is likely to wreak all sorts of havoc because of the way C&R works.

It opens the source DB file, opens a new empty DB file, and essentially copies everything one thing at a time into the originally empty DB file. Then it DELETES the original and renames the new file. During this time, because for a brief moment, the BE file doesn't exist (under the right name).
 
Considering your ideas above, I envision an option of a different front-end which will always remain open exclusively with no forms active so no LCB file on back end (tried and tested). This will send code to kick users off their front end. Then it will compact and backup the back end.
Does this sound reasonable?
 
I know this thread has been resolved but I would like to bring up a related issue with the code below:
It works fine when the backend is unencrypted but I cannot find how to programmatically provide the password. Can anyone help me?
Code: 
Dim FSO As FileSystemObject

Dim sDestFile As String
Dim sExt As String
Dim sFileName As String
Dim sPrompt As String
Dim sSourcePath As String
Dim sTitle As String

Function CompactRepair(ByVal sSourceFile As String)
On Error GoTo Error_Handler

    If sSourceFile <> Application.CurrentDb.Name Then
        
        'Compact the back end
        Set FSO = New FileSystemObject
        sFileName = FSO.GetBaseName(sSourceFile)
        sExt = "." & FSO.GetExtensionName(sSourceFile)
        sSourcePath = FSO.GetParentFolderName(sSourceFile) & "\"
        
        'Delete the previous Temp file if it exists.
        If Dir(sSourcePath & sFileName & "_Temp" & sExt) <> "" Then
            Kill sSourcePath & sFileName & "_Temp" & sExt
        End If
        
        'Compact the Back-End database to a temp file.
        DBEngine.CompactDatabase sSourceFile, sSourcePath & sFileName & "_Temp" & sExt
        
        'Delete the previous backup file if it exists.
        If Dir(sSourcePath & sFileName & ".bak") <> "" Then
            Kill sSourcePath & sFileName & ".bak"
        End If
    
        'Rename the current database as backup and rename the temp file to
        'the original file name.
        Name sSourceFile As sSourcePath & sFileName & ".bak"
        Name sSourcePath & sFileName & "_Temp" & sExt As sSourceFile
        Kill sSourcePath & sFileName & ".bak"
        Set FSO = Nothing
        
      '  MsgBox "Compact and repair successful for " & sFileName & ".", vbOKOnly + vbInformation, "IntelAce for ACE Windows"
    
    Else
    
        'Compact the front end
        Application.SetOption "Auto Compact", True
        
        'Sets the file name of the batch file to create
        Dim BatchFile As String
        BatchFile = CurrentProject.Path & "\Compact.cmd"
                
        ' creates the Batch file
        ' Change the ping value to allow for speed.
        ' 60000 = 60 secs, 30000 = 30secs etc
        ' I recommend 60 seconds to prevent any overlapping should the compact take time.
        ' Large databases may require more time and smaller databases may require less
        ' time. Just be sure you are not trying to open the database while it is still
        ' compacting.

        Open BatchFile For Output As #1
        Print #1, "Echo Off"
        Print #1, "ECHO Compacting Front End"
        Print #1, ""
        Print #1, "ping 1.1.1.1 -n 1 -w 60000"
        Print #1, ""
        Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
        Print #1, "START /I " & """MSAccess.exe"" " & sSourceFile
        Print #1, ""
        Print #1, "Del %0"
        Close #1

        ' runs the batch file
        Shell BatchFile

        'closes the current front end and runs the batch file
        DoCmd.Quit
        
    End If
 
Krayna said:
I know this thread has been resolved but I would like to bring up a related issue with the code below:
It works fine when the backend is unencrypted but I cannot find how to programmatically provide the password. Can anyone help me?
Code: 
Dim FSO As FileSystemObject

Dim sDestFile As String
Dim sExt As String
Dim sFileName As String
Dim sPrompt As String
Dim sSourcePath As String
Dim sTitle As String

Function CompactRepair(ByVal sSourceFile As String)
On Error GoTo Error_Handler

    If sSourceFile <> Application.CurrentDb.Name Then
       
        'Compact the back end
        Set FSO = New FileSystemObject
        sFileName = FSO.GetBaseName(sSourceFile)
        sExt = "." & FSO.GetExtensionName(sSourceFile)
        sSourcePath = FSO.GetParentFolderName(sSourceFile) & "\"
       
        'Delete the previous Temp file if it exists.
        If Dir(sSourcePath & sFileName & "_Temp" & sExt) <> "" Then
            Kill sSourcePath & sFileName & "_Temp" & sExt
        End If
       
        'Compact the Back-End database to a temp file.
        DBEngine.CompactDatabase sSourceFile, sSourcePath & sFileName & "_Temp" & sExt
       
        'Delete the previous backup file if it exists.
        If Dir(sSourcePath & sFileName & ".bak") <> "" Then
            Kill sSourcePath & sFileName & ".bak"
        End If
   
        'Rename the current database as backup and rename the temp file to
        'the original file name.
        Name sSourceFile As sSourcePath & sFileName & ".bak"
        Name sSourcePath & sFileName & "_Temp" & sExt As sSourceFile
        Kill sSourcePath & sFileName & ".bak"
        Set FSO = Nothing
       
      '  MsgBox "Compact and repair successful for " & sFileName & ".", vbOKOnly + vbInformation, "IntelAce for ACE Windows"
   
    Else
   
        'Compact the front end
        Application.SetOption "Auto Compact", True
       
        'Sets the file name of the batch file to create
        Dim BatchFile As String
        BatchFile = CurrentProject.Path & "\Compact.cmd"
               
        ' creates the Batch file
        ' Change the ping value to allow for speed.
        ' 60000 = 60 secs, 30000 = 30secs etc
        ' I recommend 60 seconds to prevent any overlapping should the compact take time.
        ' Large databases may require more time and smaller databases may require less
        ' time. Just be sure you are not trying to open the database while it is still
        ' compacting.

        Open BatchFile For Output As #1
        Print #1, "Echo Off"
        Print #1, "ECHO Compacting Front End"
        Print #1, ""
        Print #1, "ping 1.1.1.1 -n 1 -w 60000"
        Print #1, ""
        Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
        Print #1, "START /I " & """MSAccess.exe"" " & sSourceFile
        Print #1, ""
        Print #1, "Del %0"
        Close #1

        ' runs the batch file
        Shell BatchFile

        'closes the current front end and runs the batch file
        DoCmd.Quit
       
    End If
Click to expand...
Hi. If you go back to the link I provided earlier in post #5, you'll see there's a commented out line of code for password-protected BEs.
 
You must log in or register to reply here.

Similar threads

MajP
Access Item Genie demo for Organizing / Tracking your Items
2 3 4
Replies
74
Views
2,335
ClaraBarton
C
M
Solved Avoiding opening the database from OneDrive
2
Replies
23
Views
636
GPGeorge
GPGeorge
A
Solved VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file
2
Replies
24
Views
982
isladogs
isladogs
R
Database inconsistent
Replies
9
Views
460
gemma-the-husky
gemma-the-husky
T
inconsistent state
Replies
2
Views
236
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom