Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17

I have a DB but want to protect it from user changes and protect with a password. How do I do this?

  1. #16
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,463

    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 (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  2. #17
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,463
    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 (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

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
  •  
Tech Forums: Microsoft Office Forums