Access 2013 VBA Backup Routine File Not Found Error

Hello,

I acquired the following VBA Access DB backup snippet from one of Helen Fedemma's Access Archon columns and have slightly modified it to remove the user interaction.

However, after modifying it to remove the user interaction in an Access 2013 database (.accdb), I keep getting a "file not found" error # 53 and cannot seem to identify why.

I tested the original code with Helens db and it works absolutely fine in the older mdb format and drops the backup directly into its correct directory.

I am using MS Office 32-Bit version and running Windows 7 Professional 64-bit.

Helens original code (directly below) followed by my own after I modified it:

Public Function BackupFrontEnd()
'Created by Helen Feddema 3-Sep-2006
'Modified by Helen Feddema 16-Sep-2006

On Error GoTo ErrorHandler
   
   Set dbs = CurrentDb
   Set tdfs = dbs.TableDefs
   Set fso = CreateObject("Scripting.FileSystemObject")
   strCurrentDB = Application.CurrentProject.Name
   Debug.Print "Current db: " & strCurrentDB
   intExtPosition = InStr(strCurrentDB, ".")
   strExtension = Mid(strCurrentDB, intExtPosition)
   intExtLength = Len(strExtension)
   
   'Create backup path string (Backups folder under database folder)
   strBackupPath = Application.CurrentProject.Path & "\Backups\"
   Debug.Print "Backup path: " & strBackupPath
   
   'Check whether path is valid
On Error Resume Next
   
   Set sfld = fso.GetFolder(strBackupPath)
   If sfld Is Nothing Then
      'Create folder
      Set sfld = fso.CreateFolder(strBackupPath)
   End If
   
On Error GoTo ErrorHandler
   'Create proposed save name for backup
   strDayPrefix = Format(Date, "d-mmm-yyyy")
   strSaveName = Left(strCurrentDB, _
      Len(strCurrentDB) - intExtLength) & " Copy " & SaveNo _
      & ", " & strDayPrefix & strExtension
   strProposedSaveName = strBackupPath & strSaveName
   Debug.Print "Backup save name: " & strProposedSaveName
   strTitle = "Database backup"
   strPrompt = "Save database to " & strProposedSaveName & "?"
   strSaveName = Nz(InputBox(prompt:=strPrompt, _
      Title:=strTitle, Default:=strProposedSaveName))
   
   'Deal with user canceling out of the InputBox
   If strSaveName = "" Then
      GoTo ErrorHandlerExit
   End If
   
   Set rst = dbs.OpenRecordset("zstblBackupInfo")
   With rst
      .AddNew
      ![SaveDate] = Format(Date, "d-mmm-yyyy")
      ![SaveNumber] = SaveNo
      .Update
      .Close
   End With

   fso.CopyFile Source:=CurrentDb.Name, _
      destination:=strSaveName
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & err.Number & "; Description: " & _
      err.Description
   Resume ErrorHandlerExit

End Function

Open in new window


And mine after it was modified slightly:

Public Function BackupFE()
On Error GoTo Err_Handler
'Created by Helen Feddema 3-Sep-2006
'Modified by Laila Jackson 06-Mar-2015

    Set dbs = CurrentDb
    Set tdfs = dbs.TableDefs

    'components of the FileSystemObject object library are used to work with files ->>
    Set fso = CreateObject("Scripting.FileSystemObject")
    strCurrentDB = Application.CurrentProject.Name
    intExtPosition = InStr(strCurrentDB, ".")
    strExtension = Mid(strCurrentDB, intExtPosition)
    intExtLength = Len(strExtension)
   
    'create backup path string (Backups folder under database folder) ->>
    strBackupPath = Application.CurrentProject.Path & "\Backups\"
    Debug.Print "Backup path: " & strBackupPath
    
    'check whether directory path is valid ->>
On Error Resume Next
    
    Set sfld = fso.GetFolder(strBackupPath)
    If sfld Is Nothing Then
        'create backup folder
        Set sfld = fso.CreateFolder(strBackupPath)
    End If
    
On Error GoTo Err_Handler
    'create save file name for completed backup ->>
    strDayPrefix = Format(Date, "d-mmm-yyyy")
    strSaveName = Left(strCurrentDB, _
        Len(strCurrentDB) - intExtLength) & " Copy " & FESaveNo _
        & ", " & strDayPrefix & strExtension
    'generating save name (inclusive of path) ->>
    strProposedSaveName = strBackupPath & strSaveName
    Debug.Print "Backup save name: " & strProposedSaveName
    
    'adding new save record to db table ->>
    Set rst = dbs.OpenRecordset("tbl_backup")
    With rst
       .AddNew
       ![SaveDate] = Format(Date, "d-mmm-yyyy")
       ![SaveNumber] = FESaveNo
       .Update
       .Close
    End With
    
    'perform the final copy of current db to the specified location ->>
    fso.CopyFile Source:=strCurrentDB, _
        destination:=strProposedSaveName
    
'error handler specification ->
ErrorHandlerExit:
    Exit Function

Err_Handler:
    If Err.Number = 0 Then
        Resume ErrorHandlerExit
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & _
        Err.Description
        'add event log ->>
        'Call addErrorLog("")
        Resume ErrorHandlerExit
   End If
End Function

Open in new window


My debug result are:
Backup path: D:\SMARTA\Backups\
Backup save name: D:\SMARTA\Backups\Database1 Copy 6, 6-Mar-2015.accdb

Can anyone help me identify where things are be going wrong and what is needed to get it working in Access 2013?

Many thanks,
Lai
Laila JacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

the difference is that the original code uses "CurrentDb.Name" as source. That includes the complete path and the file name. You used "CurrentProject.Name" (through a variable) which only includes the file name but not the path and so the file system object cannot find the file.

You should also include "Option Explicit" at the top of each module and declare all variables before using them, that's the same problem in the original code.

Cheers,

Christian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laila JacksonAuthor Commented:
Legend! I thought it was something simple but could not get my head around it.
Many thanks. Lia
0
BitsqueezerCommented:
You're welcome...:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.