Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    All going well here thanks and the weather is glorious.
    As a result i'm very busy on outside projects but will pass on backup code later today.

    If you are passing on your FE and BE, you could keep life simple by instructing the other user to save the BE to the same location.
    If so, the links should still work.



    Otherwise, the tables will need relinking by the person doing the tweaking. To do so, they will need to bypass whatever security you have added.
    Don't forget they will need an ACCDB version of the FE for development work.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As promised, here is a routine to compact then backup your BE database.
    Its a function so it can be run from a macro if needed - see below
    The backup file will include the date & time in the format yyyymmddhhnn
    Place in a standard module and tweak as appropriate. Omit any parts you don't need

    Code:
    Option Compare Database
    Option Explicit
    
    Public Function BackupBEDatabase()
    
    On Error GoTo Err_Handler
    
    'creates a copy of the backend database to the selected backups folder with date/time suffix
    
        Dim fso As Object
        Dim strFilename As String, strFilePath As String, strFileType As String, strBackupsFolder As String
        Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
        Dim newlength As Long
        Dim strPwd As String
        
        strPwd = "Your BE password here"
           
        strFilename = "Your database name here"
        strFilePath= "Your database path here"
        strBackupsFolder = "Yourbackup folder path here"
        strFileType = Mid(strFilename, InStr(strFilename, ".")) 'e.g. .accdb
                
        strOldPath = strFilePath & "\" & strFilename
        
        strNewPath = strBackupsFolder & "\BE\" & _
             Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType
             
        strTempPath = strBackupsFolder & "\" & _
             Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType
             
       ' Debug.Print strOldPath
       ' Debug.Print strTempPath
       ' Debug.Print strNewPath
        
       'optional message - omit section if run automatically via scheduled task or similar
            If MsgBox("This procedure is used to make a backup copy of the Access back end database." & vbCrLf & _
                "The backup will be saved to the 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 & _
                "Create a backup now?", _
                    vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then
                        Exit Function
            Else
                DoEvents
                       
    StartBackup:     
                Set fso = CreateObject("Scripting.FileSystemObject")       
    
                'copy database to a temp file
                fso.CopyFile strOldPath, strTempPath
                Set fso = Nothing
                          
                'compact the temp file (with password) - if required
                DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & strPwd & "", , ";PWD=" & strPwd & ""
                
                'delete the tempfile
                Kill strTempPath
                    
                DoEvents
                
                OPTIONAL - '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
                
        End If
       
       'Optional success message - omit if run automatically         
         MsgBox "The Access 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 BE Backup completed"
                    
    Exit_Handler:
        Exit Function
        
    Err_Handler:
        Set fso = Nothing
        If Err <> 0 Then
           MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & vbCrLf & _
               Err.description, vbCritical, "Error copying database"
        End If
        Resume Exit_Handler
        
    End Function
    You can run this at any time from the within the FE itself
    However, to run it automatically at a scheduled time there are 3 possible approaches you could use. Choose whichever suits you best.

    1. Create a hidden form that runs at startup & is always open
    Add a form timer event so it runs hourly - time interval = 3600000 (its in milliseconds)
    Add code to the timer event so it runs the backup if close to midnight
    Code:
    If Hour(Now())=0 Then BackupBEDatabase
    2. Create a macro to run the scheduled task. Call the macro e.g. BackupData
    Now create a scheduled task to run at midnight, open your FE, run the macro then quit the app
    You will need to use your full FE path followed by the command line switch /BackupData

    3. Create a small standalone app which includes the backup routine and an autoexec app so it runs at startup.
    The macro does the same as in 2. above
    Create a scheduled task to run that standalone app at midnight

    Method 1 is simplest but requires your app to be left running overnight. Will fail if your app or the hidden form is closed
    Method 2 or 3 are more robust but require setting up a scheduled task

    My preferred method is usually method 3

    Hope that all makes sense.
    Let me know if you have any problems
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #18
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49
    Colin,

    Thanks. I'll look this over.

  4. #19
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49

    Backup BE at a scheduled time

    Carla v15.zipCarla v15_be.zipColin,


    Attached is the FE and BE database with fictitious data. I created a module however I had trouble naming the module and now it gives the error message "Error 5 in BackupBEDatabase Procedure: Invalid procedure call or argument". When I run the module it starts a module I setup but stopped using. Could you look at it to see what I did wrong?

    Also, I don't have a password and probably won't need it at this time.

    Regarding scheduling, I do want to setup an automatic backup to run at midnight. I prefer your method #3: a standalone app. My app is on a server drive at my work and the workstations use Windows 7. Since the users including myself log off their workstations daily I'm not sure how the app will run if the workstation is logged out at midnight. My question is when you say use a scheduled task does this mean using the Windows scheduling task?

    Thank you,

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You made a couple of mistakes:
    1. NEVER give a procedure the same name as the module. Access will get confused if you call the procedure
    I've renamed the module modBackup
    2. You had altered the strFileType code - I've corrected this

    Also, although the code will run if you include a trailing backslash at the end of strFilePath & strBackupsFolder, it will result in a double backslash in the displayed messages.
    I've removed them from your code

    As your data is sensitive I would recommend you always use a password for the BE.
    However as you don't currently have one I've amended the compact database line (the old code has been commented out in case you add a password later)
    Code:
    DBEngine.CompactDatabase strTempPath, strNewPath
    The code I supplied does not create the backups folder or BE subfolder & will error if it doesn't exist
    I use a BE subfolder as my backups folder also contains FE backups (in a FE subfolder!)
    So make sure you create the folder: C:\Users\Duncan\Documents\Personal Documents\HIM Documents\EHR\BE"
    Or alter the following line in the code to remove the BE\ part leaving the leading \ in place
    Code:
     strNewPath = strBackupsFolder & "\BE\" & _
            Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType
    I did indeed mean you should use the Windows Task Scheduler to automate the process.
    I'll leave you to do that
    However I have made the macro BackupBE needed to run the backup routine from the Task Scheduler

    There is an option to wake the computer to run the scheduled task. However it does need to remain logged on for that user

    Finally I would only save one backup module - mine or that by datAdrenaline - but not both or you will get confused

    NOTE - You will need to relink the FE to your BE
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #21
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49
    Isladogs,

    Thanks for straightening out the coding problems and errors. Also, thanks for clear instructions. I was able to run the backup successfully. Now I will look at the Windows task scheduler or another scheduling process. Again, your help is appreciated.

  7. #22
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Excellent. Hopefully Windows Task Scheduler will be straightforward for you to set up.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #23
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49

    Back FE and BE using a macro while not backing up more than one copy per day

    Isladogs,

    Attached is the DB. I would like to setup a backup outside of using the Windows Task Scheduler. I would like to create a backup upon loading the program using a macro. I attempted to set this up but couldn't get it to work. Also, I prefer that it not create more than one backup for each day. Also, I would like to make a copy of the front end at the same time. Could you look at what I did and edit it so I can backup upon loading the program?
    Attached Files Attached Files

  9. #24
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    To do this you could do one of the following
    1. Create a table tblLatestBackup to store the date of the last backup done
    2. Check the date of the latest backup in the backup folder using its name or date modified property.
    In either case if the date is not the current date, run the backup routine
    It will run no matter which user opens your app.

    However, I disagree with this approach for several reasons.
    1. It will create a delay when opening the app which will led to poor user experience.
    The delay will be caused by checking whether backups have been done that day and then running the backups if needed.

    2. There is no point backing up the FE automatically as it is only changed during development work. Make a copy before you do any significant changes.
    The production version of the FE should always be a copy of the latest completed development version.
    In other words you will already have FE backups.

    Also you have created a completely unnecessary module and function RunSub which runs the BackupBEDatabase routine.
    If you want the code to run at startup, just run my backup function in AutoExec.
    Edit that routine itself if you want to do so.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #25
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49
    Isladogs,

    Thanks for your comments. I can use front end copies after I make changes.
    I edited the AutoExec macro to Run code as below but it still errors out. Can you help with this? below is the Autoexec macro, Function code and error message upon loading the program. Iv'e noticed that the BackupBEdatabase() function won't run unless I step into the function and start at the line: Public Function BackupBEDatabase().


    Error message "The Expression you entered has a function name that (my database) can't find" then displays error 2425 or 3021.

    AutoExec:
    Runcode
    Function Name BackupBEdatabase()



    Option Compare Database
    Option Explicit


    Public Function BackupBEDatabase()


    On Error GoTo Err_Handler


    'Isladogs - minor changes made 23/07/2019
    'creates a copy of the backend database to the selected backups folder with date/time suffix


    Dim fso As Object
    Dim strFilename As String, strFilePath As String, strFileType As String, strBackupsFolder As String
    Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
    Dim newlength As Long
    Dim strPwd As String

    strPwd = "" 'Your BE password here

    strFilename = "Carla v15_BE.accdb"
    'Isladogs - removed trailing backslashes
    strFilePath = "C:\Users\Duncan\Documents\Personal Documents\HIM Documents\Health Record Processes"
    strBackupsFolder = "C:\Users\Duncan\Documents\Personal Documents\HIM Documents\Health Record Processes"
    strFileType = Mid(strFilename, InStr(strFilename, ".")) 'e.g. .accdb

    strOldPath = strFilePath & "" & strFilename

    strNewPath = strBackupsFolder & "\BE" & _
    Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType

    strTempPath = strBackupsFolder & "" & _
    Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType

    'Debug.Print strOldPath
    'Debug.Print strTempPath
    'Debug.Print strNewPath

    'optional message - omit section if run automatically via scheduled task or similar
    If MsgBox("This procedure is used to make a backup copy of the Access back end database." & vbCrLf & _
    "The backup will be saved to the 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 & _
    "Create a backup now?", _
    vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then
    Exit Function
    Else
    DoEvents

    StartBackup:
    Set fso = CreateObject("Scripting.FileSystemObject")


    'copy database to a temp file
    fso.CopyFile strOldPath, strTempPath
    Set fso = Nothing

    'compact the temp file
    'backup with password - if required
    'DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & strPwd & "", , ";PWD=" & strPwd & ""
    'no password
    DBEngine.CompactDatabase strTempPath, strNewPath

    'delete the tempfile
    Kill strTempPath

    DoEvents

    'OPTIONAL - '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

    End If

    'Optional success message - omit if run automatically
    MsgBox "The Access 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 BE Backup completed"

    Exit_Handler:
    Exit Function

    Err_Handler:
    Set fso = Nothing
    If Err <> 0 Then
    MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & vbCrLf & _
    Err.Description, vbCritical, "Error copying database"
    End If
    Resume Exit_Handler

    End Function

  11. #26
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's odd. I got the same error as you.
    Have renamed the procedure as BackupBE and modified Autoexec accordingly.
    It now seems to work fine.
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #27
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49
    Isladogs,

    I hope this message doesn't disturb since it is late at night where you are.

    It's almost there. I wanted to take out the Yes/No questions so the user doesn't see this. I removed the section asking Y/N to proceed and it errored out with: Macro Single Step Error 2001.
    How can I remove the section in bold below?

    Public Function BackupBE()


    On Error GoTo Err_Handler


    'Isladogs - minor changes made 23/07/2019
    'creates a copy of the backend database to the selected backups folder with date/time suffix


    Dim fso As Object
    Dim strFilename As String, strFilePath As String, strFileType As String, strBackupsFolder As String
    Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
    Dim newlength As Long
    Dim strPwd As String

    strPwd = "" 'Your BE password here

    strFilename = "Carla v15_BE.accdb"
    'Isladogs - removed trailing backslashes
    strFilePath = "C:\Users\Duncan\Documents\Personal Documents\HIM Documents\Health Record Processes"
    strBackupsFolder = "C:\Users\Duncan\Documents\Personal Documents\HIM Documents\Health Record Processes"
    strFileType = Mid(strFilename, InStr(strFilename, ".")) 'e.g. .accdb

    strOldPath = strFilePath & "" & strFilename

    strNewPath = strBackupsFolder & "\BE" & _
    Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType

    strTempPath = strBackupsFolder & "" & _
    Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType

    'Debug.Print strOldPath
    'Debug.Print strTempPath
    'Debug.Print strNewPath

    'optional message - omit section if run automatically via scheduled task or similar
    If MsgBox("This procedure is used to make a backup copy of the Access back end database." & vbCrLf & _
    "The backup will be saved to the 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 & _
    "Create a backup now?", _
    vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then
    Exit Function
    Else
    DoEvents


    StartBackup:
    Set fso = CreateObject("Scripting.FileSystemObject")


    'copy database to a temp file
    fso.CopyFile strOldPath, strTempPath
    Set fso = Nothing

    'compact the temp file
    'backup with password - if required
    'DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & strPwd & "", , ";PWD=" & strPwd & ""
    'no password
    DBEngine.CompactDatabase strTempPath, strNewPath

    'delete the tempfile
    Kill strTempPath

    DoEvents

    'OPTIONAL - '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

    End If

    'Optional success message - omit if run automatically


    Exit_Handler:
    Exit Function

    Err_Handler:
    Set fso = Nothing
    If Err <> 0 Then
    MsgBox "Error " & Err.Number & " in BackupBE procedure : " & vbCrLf & _
    Err.Description, vbCritical, "Error copying database"
    End If
    Resume Exit_Handler

    End Function

  13. #28
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No problem.
    I've commented out all the message related code so it will run automatically each time your app is opened.
    You will only see a message if there is an error e.g. Error 76 - path not found … if the backup folder hasn't been created.

    I haven't added code to ensure only one backup is created each day. See previous replies for suggested approaches
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #29
    Duncan Pucher is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Bellevue, WA
    Posts
    49
    Isladogs,

    Works great! The file runs in Autoexec without the users knowing.

    Thanks,

  15. #30
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Excellent. Good luck with the rest of your project
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Password protect a design
    By Skydiver16 in forum Database Design
    Replies: 2
    Last Post: 08-06-2016, 04:08 AM
  2. Replies: 7
    Last Post: 03-13-2013, 07:34 PM
  3. password protect form
    By nkuebelbeck in forum Forms
    Replies: 2
    Last Post: 01-15-2012, 01:02 PM
  4. Password Protect Forms
    By jlclark4 in forum Security
    Replies: 3
    Last Post: 01-25-2011, 04:26 PM
  5. Password Protect Forms
    By Robert M in forum Programming
    Replies: 3
    Last Post: 01-15-2010, 01:50 PM

Posting Permissions

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