Run-time error 52 Bad file name or number (1 Viewer)

[Ashfaque]

Hello,
I have 2 dbs..one is backend where 5-6 tables are existing and those are linked in main db. Since the use want to have backup of his backend db I started searching code to have daily back up. Later on I got following vba ready code somewhere on net to take backup of Ms Access db (Back-end)

Private Sub btnBackup_Click()
On Error GoTo Err_backup
    
Dim strFullPath As String
Dim strBackendFile As String
Dim strPath As String
Dim strSourceFile As String
Dim strDestinationFile As String
Dim I, N As Integer

' get path to back-end using a linked table as reference. Change "tblLinked" to the name of a table within the back-end db.
' Mid function drops connection info including password (starts at character position 01)
' the 01 will need to be customized depending on the existence and length of the back-end database's password.

strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblLinked").Connect, 2)

' uncomment next line in order to check path string in order to fine-tune the number of characters to
' truncate from the begining (by changing #01 above) in order to arrive at just the file path.

MsgBox (strFullPath)

' isolate the name of the backend database
    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            strBackendFile = Mid(strFullPath, (I + 1))
            Exit For
        End If
    Next

    For N = Len(strBackendFile) To 1 Step -1
        If Mid(strBackendFile, N, 1) = "." Then
            strBackendFile = Left(strBackendFile, (N - 1))
            Exit For
        End If
    Next

' remove the filename of the database to isolate the path
    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            strPath = Left(strFullPath, I)
            Exit For
        End If
    Next

' reconstruct the source and destination file paths
' destination file to include the abbreviated day of the week
' \backup\ directory must already exist
' if working with <=2003 version databases, change 2 instances of ".accdb" below to ".mdb"

    strSourceFile = strPath & strBackendFile & ".accdb"
    strDestinationFile = strPath & "backup\" & strBackendFile & "-" & WeekdayName(Weekday(Date), True) & ".accdb"

FileCopy strSourceFile, strDestinationFile
MsgBox "The back-end database has been backed up.", vbOKOnly, "Success"

Exit_Backup:
Exit Sub

Err_backup:
If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therefore is locked and cannot be copied at this time. Please ensure that all forms, reports, and queries are closed, and that no one is using the database and try again.", vbOKOnly, "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & strSourceFile & "' could not be found. Please validate the location and name of the specifed Source File and try again", vbOKOnly, "File Not Found"
    Else
        MsgBox "Microsoft Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Error Source: ModExtFiles / CopyFile" & vbCrLf & "Error Description: " & Err.Description, vbCritical, "An Error has Occured"
End If

Resume Exit_Backup
End Sub

The code looks easy to me but producing Run-time error 52 (Bad file name or number ) at one line which is :

FileCopy strSourceFile, strDestinationFile

As per instruction in comments of this code, I created a table in backend db called 'tblLinked' and linked into main db like other tables.

Intially my backend Access file name was GREC_BE. After producing this error I thought because of file name that included" _" it may not working. So I changed file name to GRECBE

Still the error persist.

Can some please help?

 

[Gasman]

So show the values for both variables?
Underscores in filenames are fine, sometimes better than having spaces

 

[Ashfaque]

Yes it shows the sourcefile and backup file (with new name) too

 

[Gasman]

So show the values please. Do the paths actually exist, ie all the subfolders

 

[Ashfaque]

These are the values in pics

Yes I created a folder named 'backup'

 

[Gasman]

I am on my phone in a carpark.
I usually debug.print the variables when testing, then I can copy and paste the output here when I need help.
You have been on here since 2004 and have over 800 posts?, you should know code is easier to copy and paste or view, than pictures?

However with a zoom I think I see the problem, why you cannot amazes me, what is the ;database rubbish in the names?

You need to inspect what you are actually using, not what you think you are using.

 

[Ashfaque]

Gasman,

Thanks for suggestions. but I am not perfect like you people even though I raised 800 posts. Person is always learning.

Anyways thanks.

 

[Gasman]

Oh, I am not perfect believe me , but I do at least check what I am trying to use. I suggest you try and do the same. That way you will learn much more, very quickly.
Hopefully this episode has been a good lesson for the future?

 

[Ashfaque]

but I do at least check what I am trying to use.

Do you think I did not do it?

 

[Ashfaque]

Anyone else can help ?

 

[Gasman]

I thought we had solved it?
You do not want ;database= in the filename.
It should start with the drive letter D:

 

[Pat Hartman]

I would recommend a visit to www.fmsinc.com They have a number of very useful utilities including one that does backups. That way you don't have to rely on some user to click the backup button every day.