none
backing up an open copy of 2007 database? RRS feed

  • Question

  • I look again to your generous  responses.

    Below is a routine for backing up an open database (copied and adapted).

    I have used this very successfully without undue problems in access 97 over the years.

    The user is able to search in explorer for a path into which the db can be copied then an exit button is clicked to call this routine.

    I have looked around for similar routines available for 2007 access and have either not quite comprehended them or they do not do all I require - eg some do not copy the relationships - what use is that?

    The one below is doubtless in my inimitable fashion a little pedestrian but it works.

    As I have notated below the routine now fails (in access 2007) on the Dim fso As FileSystemObject - as not been recognised or what ever.

    I would be glad for any sugestions as to how to adapt the routine below or indeed for any other routine that allows me the same facility

    many thanks db

    Private Function BackupCopy(booPathFound As Boolean)

    'This function will allow you to copy a db that is open,
    'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

    10       strErrMsg = ""
    20      On Error GoTo BackupCopy_ERR

    Dim fso As FileSystemObject 'ACCESS 2007 DOESN'T RECOGNISE THIS - THIS ROUTINE WAS USED SUCCESSFULLY IN ACCESS 97
    Dim sSourcePath As String

    Dim sSourceFile As String

    Dim sBackupPath As String

    Dim sBackupFile As String
    Dim strKillpath As String
    Dim SearchString, SearchChar, CharPos
    Dim lenString As Integer


    Dim db As Database
    30    Set db = CurrentDb()

    ' this exists in case it's a new empty base -!!
    40      If IsNull(DLookup("[OwnerID]", "Owner")) Then
    50                      Exit Sub
    60      End If

    70    sSourcePath = db.Name


    'Now Delete all of string prior to '\' leaving just the filename
    'use 'inStr' MyPos = Instr(SearchString, SearchChar)

    80      SearchString = sSourcePath
    90      SearchChar = "\"

    100     CharPos = InStr(sSourcePath, SearchChar)

    110     Do Until CharPos = 0

    120     lenString = Len(SearchString)
    130     SearchString = Right(SearchString, (lenString - CharPos))
    140     CharPos = InStr(SearchString, SearchChar)

    150   Loop

    '****************   \\tsclient\D\_Alldata\XPEesDbBUHere   .......... is the actual path name held/inserted into  the  field me.thBU - can be changeed of course
    160     sBackupPath = Me.thBU & "\"
    170     sBackupFile = Left(SearchString, Len(SearchString) - 4) & "-ProdBU.mmm" 'change the file suffix to avoid any chance of overwriting an original
    180     Set fso = New FileSystemObject

    'her we kill the last bu'd up database

    190     strKillpath = Me.thBU & "\*.mmm"

    200     Kill strKillpath


    210     fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True

    'Check File Has been Saved
        Dim FileName As String
       
    220       FileName = Dir(sBackupPath & sBackupFile)

       
     

    230       If FileName = sBackupFile Then
    240                 MsgBox vbCrLf & vbCrLf _
                        & "Backup was successful and saved @ " & vbCrLf & vbCrLf _
                        & sBackupPath & vbCrLf & vbCrLf _
                        & "The backup file name is " & vbCrLf & vbCrLf _
                        & Dir(sBackupPath & sBackupFile) & vbCrLf & vbCrLf _
                        & "PLEASE NOTE !!! - THE FILE TYPE HAS BEEN SAVED AS AS '*.mmm'......" & vbCrLf & vbCrLf _
                        & "This is a non existent file type - to use this file change file type to '*.MDB' ", vbInformation, "Backup Completed"

    250       Else
    260                 MsgBox "Back up Failed"
    270       End If

    280   Set fso = Nothing
    290   Set db = Nothing

    Exit_BackupCopy_ERR:
    300   Exit Function

    BackupCopy_ERR:

    310       If Err.Number = 76 Or Err.Number = 5 Then

    320                           MsgBox vbCrLf & "Back Up Path Not Found" & vbCrLf _
                                            & "Path Cleared - enter Correct One!! OR Continue to Exit", vbOKOnly + vbCritical, EEsName
    330                           Me.thBU = ""
    340                           booPathFound = False
    350                           Exit Function
    360       End If
                                
              
    370        If Err.Number = 53 Then Resume Next

    380    MsgBox strErrMsg & vbCrLf & vbCrLf & "   In " & varThisForm & " -   BackupCopy  " & vbCrLf _
          & "Line No " & Erl & " Err# " & Err.Number & " Description: " & Err.Description

       
    390      Resume Exit_BackupCopy_ERR


    End Function


    • Edited by dbdesign Wednesday, February 22, 2012 11:28 AM spelling
    Wednesday, February 22, 2012 8:14 AM

Answers

  • Hi dbdesign,


    It's either set the reference "Microsoft Scripting Runtime" (Goto: Tools > References > Tick this reference) or you use late binding to avoid having a reference ticked.


    For late binding:

    Dim fso As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    Hope this helps,



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"


    • Proposed as answer by danishaniModerator Wednesday, February 22, 2012 6:48 PM
    • Edited by danishaniModerator Thursday, February 23, 2012 12:33 AM typo in code
    • Marked as answer by dbdesign Thursday, February 23, 2012 11:58 AM
    Wednesday, February 22, 2012 2:15 PM
    Moderator
  • Hello Daniel Again - and many thanks again - works perfectly!

    Inevitably the odd ancillary question:

    If I 'backend' the database I presume I proceed along the above lines for the front end (using  sSourcePath = db.Name) - but how do I get to the backend element - to back it up - I can't believe the programme will recognise and BU both files involved ?

    Secondlly do you have any comments relative the statements I've read advising agains backing up an open database - can't remember the full arguments but it implied that the db might not be properly 'wrapped up' or some such term - ?

    I'd be pleased to have your opinions

    Best wishes

    davidbailie

    ps any others who may care to comment please do

    • Marked as answer by dbdesign Friday, February 24, 2012 6:14 PM
    Thursday, February 23, 2012 12:14 PM
  • If your back-end is located on a network, then it should be in the network back-up, which hopefully is in place every day (at night).

    Make sure you have a seperate version (used by users) front-end at Network, which can be distributed (deployed) to users at demand.

    There are many tools out there, free and paid, which can help you deploying Front-end to users.

    And you might have a copy of the developer version of Front-end and Back-end at hand for developing purpose.

    So, that's how I handled it in the past. So no need to have all kind of Back-up procedures code running, it was taken care of by Network Admin.

    Hope that helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Marked as answer by dbdesign Friday, February 24, 2012 6:14 PM
    Friday, February 24, 2012 12:51 AM
    Moderator

All replies

  • Hi dbdesign,


    It's either set the reference "Microsoft Scripting Runtime" (Goto: Tools > References > Tick this reference) or you use late binding to avoid having a reference ticked.


    For late binding:

    Dim fso As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    Hope this helps,



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"


    • Proposed as answer by danishaniModerator Wednesday, February 22, 2012 6:48 PM
    • Edited by danishaniModerator Thursday, February 23, 2012 12:33 AM typo in code
    • Marked as answer by dbdesign Thursday, February 23, 2012 11:58 AM
    Wednesday, February 22, 2012 2:15 PM
    Moderator
  • Hi Daniel -

    I thank you for that -

    Obscurely - (I've only been working on 2007 for about 18 months !!! ) - right at the beginning I looked for/checked out (I thought) references and missed the fact that they existed in 2007 - (I knew about it in access 97) - I assumed, I suppose, that 2007 had moved on in it's structures/programming .........  I, now that I recheck, see that I was wrong ..... soooo

    I am now adapting the routine for my 2007 db's and will report back

    I have also absorbed your ref to 'late binding' (which I was not aware of) and will explore that too - :)

    I thank you again

    best wishes

    db 

     

    Wednesday, February 22, 2012 6:17 PM
  • Hello Daniel Again - and many thanks again - works perfectly!

    Inevitably the odd ancillary question:

    If I 'backend' the database I presume I proceed along the above lines for the front end (using  sSourcePath = db.Name) - but how do I get to the backend element - to back it up - I can't believe the programme will recognise and BU both files involved ?

    Secondlly do you have any comments relative the statements I've read advising agains backing up an open database - can't remember the full arguments but it implied that the db might not be properly 'wrapped up' or some such term - ?

    I'd be pleased to have your opinions

    Best wishes

    davidbailie

    ps any others who may care to comment please do

    • Marked as answer by dbdesign Friday, February 24, 2012 6:14 PM
    Thursday, February 23, 2012 12:14 PM
  • If your back-end is located on a network, then it should be in the network back-up, which hopefully is in place every day (at night).

    Make sure you have a seperate version (used by users) front-end at Network, which can be distributed (deployed) to users at demand.

    There are many tools out there, free and paid, which can help you deploying Front-end to users.

    And you might have a copy of the developer version of Front-end and Back-end at hand for developing purpose.

    So, that's how I handled it in the past. So no need to have all kind of Back-up procedures code running, it was taken care of by Network Admin.

    Hope that helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Marked as answer by dbdesign Friday, February 24, 2012 6:14 PM
    Friday, February 24, 2012 12:51 AM
    Moderator
  • Cheers Daniel - here all you say

    and will put it in place

    db

    Friday, February 24, 2012 6:14 PM