Results 1 to 7 of 7

Timed compact of Backend Database

  1. #1
    perryc is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    55

    Timed compact of Backend Database

    I have a Backend Database that would like to have it compacted daily at mid-night, let's say. It is typically not opened, and it is password protected as well. I can only compact it when no one is using it, therefore need to be in an late hour.



    I can use the Task Scheduler, but, I have to have my laptop connected to the network to execute that and I don't know how to program it to include the Password to open the BE database.

    Thanks in advance.

    Sincerely,
    Perry

  2. #2
    perryc is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    55
    no solution from anyone?? Really?

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,319
    I think it will fail if anyone still has a connection open to the be but try

    DBEngine.CompactDatabase FullPathOfSourceFile, FullPathOfDestinationFile, , , ";pwd=YourPassword"

    Thus you would have to use Dir command to test for the existence of the new file and if Dir does not return an empty string, overwrite the old with the new if you don't want copies of the original hanging around. Or you could move the original to a backup folder.

    AFAIK, you cannot perform the same simple cr in vba the same way you do manually when you have the db open. In the former case, you are not prompted to save as a new file. In vba, you must; likely because it is not done when open.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  4. #4
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,749
    This is a procedure I use for compacting and backing up an external database.
    It will work even if the file is in use as it is copied then compacted

    This code probably contains more than you need.
    Omit any parts you don't need e.g. file size of compacted file, messages etc

    Code:
    Public Sub BackupBackendDataFile()
    
    On Error GoTo Err_Handler
        
        'This procedure creates a backup of the backend datafile
        'If the file already exists then it is deleted before the new backup is created
        
        Dim fso As Object
        Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
        Dim newlength As Long
        Dim SilentFlag As Boolean
        
        Dim STR_PASSWORD As String
        STR_PASSWORD = "Your Password here"
        
        Set fso = CreateObject("Scripting.FileSystemObject")
           
        strFilename = "Your Backend File Name here" ' e.g. BE.accdb
        strFileType = Mid(strFilename, InStr(strFilename, ".")) 'e.g. .accdb
        
        strOldPath = Application.CurrentProject.Path & "\" & strFilename 'change as necessary
        
        strTempPath = "BackupsFoldeName" & "\" & _
            Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType
        
        strNewPath = "BackupsFoldeName" & "\" & _
            Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType
                     
        If SilentFlag = True Then GoTo StartBackup 
    
    
       'message can be omitted by setting SilentFlag=True
        If MsgBox("This will create a backup of the configuration datafile UKPAFConfig.accdb" &vbCrLf & _
            "The backup will be saved to the program backups folder with date/time suffix                   " & vbCrLf & _
                vbTab & "e.g. " & strNewPath & "                            " & vbCrLf & vbCrLf & _
            "This can be used for recovery in case of problems    " & vbCrLf & vbCrLf & _
            "If the backup already exists it will be replaced." & vbCrLf & vbCrLf & "Continue?", _
            vbInformation + vbYesNo + vbDefaultButton2, "Backup configuration datafile?") = vbNo Then
                Exit Sub
        End If
    
    
    StartBackup:   
        'copy database
        fso.CopyFile strOldPath, strTempPath
        Set fso = Nothing
        
        'compact database with password
        DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & STR_PASSWORD & "", , ";PWD=" & STR_PASSWORD & ""
        
        DoEvents
        
        'delete temp file
        Kill strTempPath
        
        'get size of backup
        newlength = FileLen(strNewPath) 'in bytes
         
        'setup string to display file size
        If FileLen(strNewPath) < 1024 Then  'less than 1KB
           strFileSize = newlength & " bytes"
        ElseIf FileLen(strNewPath) < 1024 ^ 2 Then  'less than 1MB
           strFileSize = Round((newlength / 1024), 0) & " KB"
        ElseIf newlength < 1024 ^ 3 Then 'less than 1GB
           strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 2), 1) & " MB)"
        Else 'more than 1GB
            strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 3), 2) & " GB)"
        End If
        
        DoEvents
          
                
        MsgBox "The backend database has been successfully backed up.                " & vbCrLf & __
            "The backup file is called " & vbCrLf & _
                vbTab & strNewPath & "                       " & vbCrLf & vbCrLf & _
                "The file size is " & strFileSize , vbInformation, "Access conifg file backup completed"
                
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
       'If Err.Number = 53 Then GoTo FileNotFound
        Set fso = Nothing
        If Err <> 0 Then
          MsgBox "Error " & Err.NumPostcodesr & " in BackupBackendDataFile procedure : " & vbCrLf & _
              Err.description & "      @", vbCritical, "Error copying database"
        End If
        Resume Exit_Handler
    
    
    End Sub
    Hope that helps
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  5. #5
    perryc is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    55
    Ty both! I did not realize someone responded. Ty again. I will try and report back.

  6. #6
    perryc is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    55
    Just a quick silly question, isaldogs, where should I put these codes in? 1. BE.accdb 2. FE.accdb 3. another DB to execute this? I am trying to figure out how it will fire on its own? e.g. at mid-night daily?

    Another question, do I put this in a module?

  7. #7
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,749
    Quote Originally Posted by perryc View Post
    Just a quick silly question, isaldogs, where should I put these codes in? 1. BE.accdb 2. FE.accdb 3. another DB to execute this? I am trying to figure out how it will fire on its own? e.g. at mid-night daily?

    Another question, do I put this in a module?
    This code should go in a standard module in the front end database

    To run it automatically at a specified time, I suggest the following:
    1. Change it from a sub to a function so it can be run from a macro
    2. Create a scheduled task to run at the specified time which opens your FE using the macro you created

    Alternatively, create another FE specifically for this task only, link it to the BE & use an autoexec macro so the code runs at startup
    Now run that database at the specified time using a scheduled task.

    You will want to remove/disable all code that requires user interaction from the backup procedure if it is run automatically
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-09-2019, 12:51 AM
  2. Compact database automatically
    By jaryszek in forum Access
    Replies: 18
    Last Post: 09-02-2017, 05:53 PM
  3. Compact thesize of MS Access Database
    By kacocal in forum Import/Export Data
    Replies: 3
    Last Post: 03-06-2016, 04:25 AM
  4. Backend File Compact And Repair
    By data808 in forum Access
    Replies: 5
    Last Post: 04-03-2015, 03:04 AM
  5. Unable able to compact BE Database
    By crowegreg in forum Access
    Replies: 3
    Last Post: 01-30-2014, 09:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums