Backup Error _ the Back-end File Path Not Found (1 Viewer)

[sshafiu]

Dear All,

I have the following code that backup a dynamic BE. It works when the BE is on the same project path as the FE. When the BE is on a server, it reports error of Path Not Found.

Please can anyone help me locate the error in my Code?

Thanks in advance I am grateful for you taking the time to even read my post.

Regards,
SS.

Private Sub cmdBackUp_Click()

'******************************
Dim str As String
Dim buf As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75
On Error GoTo Backup_Button_Backup

'buf = Back Up Folder
'buf is created if it does not exist
'CurrentProject.Path = the path that the FE is located
'-------------------
Dim strBackEndPath As String
Dim lenPath As Integer
Dim i As Integer
Dim j As Integer

strBackEndPath = CurrentDb.TableDefs("tblProtocols").Connect    'tblProtocols is one of the tables in the database.

' To remove the database  & password prefix
j = InStrRev(strBackEndPath, "=") + 1
strBackEndPath = Mid(strBackEndPath, j)

'-----------------
buf = strBackEndPath & "\Backups\"
MkDir buf

'-----------------
    Resume Backup_Button_Backup
Backup_Button_Backup:
'-----------------
On Error GoTo Err_Button_Backup
'-----------
MD_Date = Format(Date, "dd-mm-yyyy ") & Format(Time, "hh-mm-ss")
'str = CurrentProject.path & "\Backups\" & MD_Date
str = strBackEndPath & "\Backups\" & MD_Date
MkDir str

'--------------------------
'Source = where the data is stored
source = strBackEndPath
'---------------------

MsgBox "The path is " & source, vbOKOnly     'Just to preview the path.

Set fs = CreateObject("Scripting.FileSystemObject")
'Change the file extension as needed
fs.CopyFile source & "\*.accdb", str
Set fs = Nothing
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successfully!", _
        vbInformation, "DATATWINE: Backup Successful"

'--------------------------
'Create a Log of the backup made
    DoCmd.SetWarnings False
    On Error Resume Next
    Me.txtValueMonth.Value = Format$(Me.txtValueDate.Value, "mmmm")
    DoCmd.RunSQL "INSERT INTO tblBackups (ValueDate, ValueMonth, BackupBy, FinancialYear, CurrentUser, BackupFilePath) " _
                & " SELECT  Forms!frmMaintenance_Main!txtValueDate, Forms!frmMaintenance_Main!txtValueMonth, Forms!frmMaintenance_Main!txtUserName, Forms![frmMaintenance_Main]![frmProtocols_sbf]!txtCurrentYear , Forms!frmMaintenance_Main!txtUserName, CurrentProject.path & '\Backups\'" _
                & ""
    DoCmd.SetWarnings True

''---------------------------
''This line compact's the database
'I need a code HERE to pack the new backup file.


''---------------------------
        
Exit_Button_Backup:
  Exit Sub
Err_Button_Backup:
  If Err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox Err.Description, vbExclamation, "Error Creating " & str
  
End If
    Resume Exit_Button_Backup
End Sub

 

[essaytee]

Are you using drive mapping or unc? If drive mapping, could it be that the server is not mapped as you think.

 

[June7]

Looks like TableDefs Connect property returns mapped drive path.

Worth trying server name (UNC) path: \\servername\path\filename

 

[Gasman]

Debug.Print strBackEndPath

What does that show?

 

[essaytee]

Are you using drive mapping or unc? If drive mapping, could it be that the server is not mapped as you think.

Just thinking about my comment, if your table is linked and working, then the drive mapping should be in sync (still prefer the unc method). As Gasman suggested, a debug.print is in order.

 

[sshafiu]

Are you using drive mapping or unc? If drive mapping, could it be that the server is not mapped as you think.

Thanks for the feedback.
I am not using drive mapping. From the message line you will see that the server path has been identified. When I re-direct it to the local drive it works fine.

Sorry I not a programmer but learning from the rest of you. I did not understand the term Inc method. Any education is welcome.

Regards,
SS.

 

[sshafiu]

Debug.Print strBackEndPath

What does that show?

The path is shown clearly.

 

[Gasman]

The path is shown clearly.

Please show the contents of the variable strBackEndPath ?

 

[sshafiu]

Looks like TableDefs Connect property returns mapped drive path.

Worth trying server name (UNC) path: \\servername\path\filename

Thanks for the feedback. Please can you educate me a little more on the UNC method?

 

[essaytee]

Thanks for the feedback. Please can you educate me a little more on the UNC method?

It's where you use the server name in lieu of a drive mapped letter.

https://docs.microsoft.com/en-us/dotnet/standard/io/file-path-formats

What was the result of the debug.print?

 

[sshafiu]

It's where you use the server name in lieu of a drive mapped letter.

What was the result of the debug.print?

the result was:

\\Desktop-pd2nut0\DBFolder\Northern Data Back-End\EmployeesDB_be.accdb

Regards,
SS.

 

[Gasman]

the result was:

\\Desktop-pd2nut0\DBFolder\Northern Data Back-End\EmployeesDB_be.accdb

Regards,
SS.

That was what I was thinking.
I don't think mkdir can work with UNC path.
Certainly the DOS version cannot.

 

[sshafiu]

That was what I was thinking.
I don't think mkdir can work with UNC path.
Certainly the DOS version cannot.

Please any suggestions of what option I should use?

Thanks very much for the intervention.

Regards,
SS.

 

[Gasman]

Well I was wrong. I've just tested it and it works.
However what you have posted is not a path, but a full filename.
Strip off the filename from the path.

From my immediate window

? tt = getaccessbe_pathfilename("tblemployee")
\\DLINK323\usb250\Employee_be.accdb
 tt1 = left(tt,len(tt)-instrrev(tt,"\")+1)
? tt1
\\DLINK323\usb250\
mkdir tt1 & "\testdir2\"

 

[sshafiu]

Well I was wrong. I've just tested it and it works.
However what you have posted is not a path, but a full filename.
Strip off the filename from the path.

From my immediate window

? tt = getaccessbe_pathfilename("tblemployee")
\\DLINK323\usb250\Employee_be.accdb
 tt1 = left(tt,len(tt)-instrrev(tt,"\")+1)
? tt1
\\DLINK323\usb250\
mkdir tt1 & "\testdir2\"

Sorry, Games. I still have the same error of file path not found.

May be I am not able to put it at the right place in the code.

Any further assistance will be very much appreciated.

Regards,
Shafiu.

 

[Gasman]

Post the full Sub again please, within code tags as before.

All you should need though is

' To remove the database  & password prefix
j = InStrRev(strBackEndPath, "=") + 1
strBackEndPath = Mid(strBackEndPath, j)
strBackEndPath = left(strBackEndPath,len(strBackEndPath)-instrrev(strBackEndPath,"\")+1)
Debug.Print strBackEndPath

HTH

 

[The_Doc_Man]

First, the important symptom:

It works when the BE is on the same project path as the FE. When the BE is on a server, it reports error of Path Not Found.

Then, essaytee's link regarding file-path formats ALSO contains a discussion of file normalization, which factors into the question.

The fly in the ointment is that since the path that leads to the FE works, resolution works correctly. Something about the BE-related path does not resolve correctly.

Therefore, one of these two paths contains a point of implied substitution that gets managed during file-path normalization. We have only part of the story here.

1. The error in question "Path Not Found" occurs at what step? (I suspect it is the FileCopy, but want to be sure.)

2. I reviewed the thread twice but have not seen this asked, so... At the time that you get the error, if you are in debug mode because you were single-stepping at the time OR got the error pop-up that allows you to display the failing line of code, you can hover the mouse cursor over each variable and its value will be displayed in a "tool-tip" format. So... in that file-copy line, if you hover over "src" and see the path at that time... can you open a Windows Explorer window and browse to that folder manually? Because you are not calling out an error normally returned by the MkDir (though perhaps it COULD be there, see later discussion).

3. IF I read the code correctly, you are trying to do a FileCopy to str, which is defined by

strBackEndPath = CurrentDb.TableDefs("tblProtocols").Connect
...
strBackEndPath = Mid(strBackEndPath, j)
...
MD_Date = Format(Date, "dd-mm-yyyy ") & Format(Time, "hh-mm-ss")
...
str = strBackEndPath & "\Backups\" & MD_Date

IF this is a "native Access" back-end file, then the contents of strBackEndPath will contain something like "Drive:\path\filename.type" or "\\server\path\filename.type" - which one depends on whether you have the local path or the server path. Because when you do mapping, particularly if there is a formal "share" involved, that server path will be resolved to a UNC format automatically. So let's concatenate, shall we?

str = strBackEndPath & "\Backups" & MD_Date

So str will look like device:\path\filename.type\Backups\dd-mm-yyyy hh-nn-ss

Does THAT folder exist? Because it seems to me that convoluted path string is what you are asking the MkDir to make. To be perfectly honest, I'm not sure why this works at all with either path. And I won't swear to you that you can create a convoluted multi-step path like that in a single MkDir. Look at this article:

https://www.techonthenet.com/access/functions/file/mkdir.php

I think if you have more than one path to be created, you have to build the path one level at a time. This quote from the article appears relevant:

If path is a complex directory structure, the high-level directories must already exist or the MkDir statement will raise an error.

UPDATE: I see that GasMan's post and mine occurred at nearly the same time, and he saw one of the same things I saw - that the path from the connect string contains the filename as well as the folder pathing.

 

[Gasman]

Hi DocMan,

My money would be on the mkdir line as a folder with the filename would not exist.?

In another thread https://www.access-programmers.co.uk/forums/showthread.php?t=304550 we established that the mkdir function would create multiple folders in a path in one command.

As long of course that any parent folders exist, but as one can specify the whole path, that should not be an issue.

 

[June7]

Okay, on my laptop TableDefs Connect returns drive mapping, and apparently on network it returns UNC. Interesting.

? tt = getaccessbe_pathfilename("tblemployee")
\\DLINK323\usb250\Employee_be.accdb
 tt1 = left(tt,len(tt)-instrrev(tt,"\")+1)
? tt1
\\DLINK323\usb250\
mkdir tt1 & "\testdir2\"

Don't see how that path could work.
Looks like you end up with 2 \\ in front of testdir2.
tt1 ends with a \ then you concatenate "\testdir2"

 

[Gasman]

Okay, on my laptop TableDefs Connect returns drive mapping, and apparently on network it returns UNC. Interesting.


Don't see how that path could work.
Looks like you end up with 2 \\ in front of testdir2.
tt1 ends with a \ then you concatenate "\testdir2"

Well spotted with the double \\, small mistake on my part.
However it still creates the folder and does not complain.? New one on me.

mkdir "\\DLINK323\usb250\\testdir4\"