Run-time error '75': Path/File Access Error (1 Viewer)

[estelleh]

Good morning

I have a button on the main screen that the user presses to backup the database and exit the system. I realise it's not the ideal backup solution, but it's what was requested and works for them. The button creates a sub-directory in existing directory "Database Backups" with the name containing the date and time.

The code in the "Click" event reads as follows:

Dim str As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75
On Error GoTo btnBackup

btnBackup:
'Set Path for Backups
MD_Date = Format(Date, "dd-mm-yyyy ") & Format(Time, "hh-mm-ss")
str = strBackupPath & MD_Date

'Source = where the data is stored
source = CurrentProject.Path & "\"
MkDir str
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 & "successful!", _
        vbInformation, "Backup Successful"

In the initial startup procedure, this happens

strBackupPath = "C:\Users\Estelle\Dropbox\FF Documents\Database Backups\"

My problem is that the code currently backs up all the Access files, because of this line:

fs.CopyFile source & "*.accdb", str

making the backups very big, when all I really need to back up is the highlighted database file.

I have tried various ways of only backing up the '_be' file, but get the file access error 75. The directory is created correctly, but the system crashes when trying to write the file.

This was my latest (failed) attempt

fs.CopyFile source & "*_be.accdb", str

Any thoughts?

TIA

 

[CJ_London]

If you know the name of the BE why not just use that instead of a *?

 

[estelleh]

If you know the name of the BE why not just use that instead of a *

It was one of the options I tried, giving me the same error

 

[CJ_London]

What is the description for the error and your code does not assign a proper value to str

 

[ebs17]

? accesserror(75)

Read the message, it will make sense.

1) It is unbelievable if copying 4 files works, but the same copying of only one file fails.

2) A backend should of course only be copied if there is no data access to it from yourself or from third parties. Simply pressing a button is not a convincing approach.

 

[arnelgp]

do you mean you only need to backup the BE?
copy this to a Module:

Function fnBEPathFromLinkedTable(ByVal linkedTableName As String) As String
'arnelgp
'will only work when the BackEnd is MS Access database
    On Error Resume Next
    Dim db As DAO.Database
    Dim path As String
    Set db = CurrentDb
    fnBEPathFromLinkedTable = Replace$(db.TableDefs(linkedTableName).Connect, ";DATABASE=", "")
End Function

'arnel
Public Sub sbForceMKDir(ByVal path As String)
    Dim var As Variant, i As Integer
    Dim new_path As String
    var = Split(path, "\")
    On Error Resume Next
    For i = 0 To UBound(var)
        new_path = new_path & var(i)
        MkDir new_path
        new_path = new_path & "\"
    Next
End Sub

'arnel
Public Function fnFileNameOnly(ByVal path As String)
    Dim i As Integer
    fnFileNameOnly = path
    i = InStrRev(path, "\")
    If i <> 0 Then
        fnFileNameOnly = Mid(path, i + 1)
    End If
End Function

now modify your code to this:

Dim str As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75

'arnel
'NOTE: change Table1 to any Linked tablename to your backEnd.
Const conLINKED_TABLE = "Table1"

On Error GoTo btnBackup

btnBackup:
'Set Path for Backups
MD_Date = Format(Date, "dd-mm-yyyy ") & Format(Time, "hh-mm-ss")
str = strBackupPath & MD_Date

'Source = where the data is stored
source = CurrentProject.path & "\"

Call sbForceMKDir(str)

Set fs = CreateObject("Scripting.FileSystemObject")

Dim strBE As String
strBE = fnBEPathFromLinkedTable(conLINKED_TABLE)

'Change the file extension as needed
fs.CopyFile strBE, str & "\" & fnFileNameOnly(strBE)

Set fs = Nothing
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successful!", _
        vbInformation, "Backup Successful"

 

[estelleh]

do you mean you only need to backup the BE?
copy this to a Module:

Function fnBEPathFromLinkedTable(ByVal linkedTableName As String) As String
'arnelgp
'will only work when the BackEnd is MS Access database
    On Error Resume Next
    Dim db As DAO.Database
    Dim path As String
    Set db = CurrentDb
    fnBEPathFromLinkedTable = Replace$(db.TableDefs(linkedTableName).Connect, ";DATABASE=", "")
End Function

'arnel
Public Sub sbForceMKDir(ByVal path As String)
    Dim var As Variant, i As Integer
    Dim new_path As String
    var = Split(path, "\")
    On Error Resume Next
    For i = 0 To UBound(var)
        new_path = new_path & var(i)
        MkDir new_path
        new_path = new_path & "\"
    Next
End Sub

'arnel
Public Function fnFileNameOnly(ByVal path As String)
    Dim i As Integer
    fnFileNameOnly = path
    i = InStrRev(path, "\")
    If i <> 0 Then
        fnFileNameOnly = Mid(path, i + 1)
    End If
End Function

now modify your code to this:

Dim str As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75

'arnel
'NOTE: change Table1 to any Linked tablename to your backEnd.
Const conLINKED_TABLE = "Table1"

On Error GoTo btnBackup

btnBackup:
'Set Path for Backups
MD_Date = Format(Date, "dd-mm-yyyy ") & Format(Time, "hh-mm-ss")
str = strBackupPath & MD_Date

'Source = where the data is stored
source = CurrentProject.path & "\"

Call sbForceMKDir(str)

Set fs = CreateObject("Scripting.FileSystemObject")

Dim strBE As String
strBE = fnBEPathFromLinkedTable(conLINKED_TABLE)

'Change the file extension as needed
fs.CopyFile strBE, str & "\" & fnFileNameOnly(strBE)

Set fs = Nothing
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successful!", _
        vbInformation, "Backup Successful"

Thank you - I will give that a try

 

[ebs17]

Regardless of the reasons for the current error: Anyone who copies a backend without exclusive access to this backend, i.e. during ongoing operation, runs the increased risk that this copy will not really be usable.

 

[arnelgp]

Regardless of the reasons for the current error: Anyone who copies a backend without exclusive access to this backend, i.e. during ongoing operation, runs the increased risk that this copy will not really be usable.

I did not see any problem on my test.

 

[ebs17]

What exactly is your test?

Have you tested whether the copy of the backend is completely error-free and consistent during ongoing writing processes? And of course this in a series.
If I create a copy as security, I must have confidence in that security. The pain when you fail is enormous.

Copying anything shouldn't be a problem, FSO.CopyFile doesn't require exclusive access, unlike VBA.FileCopy and other.

 

[arnelgp]

What exactly is your test?

pay attention to:

I have a button on the main screen that the user presses to backup the database and exit the system.

 

[ebs17]

exit the system

...is finishing work with the current frontend.

You can do something like this with full access to the backend (as part of a longer-running action query/transaction??), but also via an unbound form without any access to the backend.
The attentive developer must of course also take multi-user operation into account and not just look at the front end being viewed.

As written: Copying something is just a small and easily solvable task. I think you should pay attention to whether the result of copying is as useful as you expect.

 

[Pat Hartman]

You can add code to
1. Make sure all bound objects are closed in the FE
2. Make sure that there is no .laccdb in the BE folder

 

[The_Doc_Man]

You can add code to
1. Make sure all bound objects are closed in the FE
2. Make sure that there is no .laccdb in the BE folder

I believe that unless the app dynamically opens the BE after-the-fact, or if the opening form is not bound in any way, any code included in the FE will always see the lock-support file, certainly until the last bound form is closed and the last manually opened recordset is closed. And even then I'm not sure of the timing for the closure of the .LACCDB file. It would also depend on Access releasing the Windows-level file locks to it.

 

[Pat Hartman]

certainly until the last bound form is closed and the last manually opened recordset is closed.

Isn't that what I said to do?? The lock file closes when the last lock is released. Therefore, if some other FE has some recordset open, the lock file remains present.

 

[estelleh]

Regardless of the reasons for the current error: Anyone who copies a backend without exclusive access to this backend, i.e. during ongoing operation, runs the increased risk that this copy will not really be usable.

It is a single user system

 

[Pat Hartman]

I'm sure you've never accidentally opened two copies of the FE.

The point of making a backup is to ensure that you have a recovery position. So, why would you argue about taking safety precautions?

 

[ebs17]

It is a single user system

Can you safely control in which phase your user presses the button?
You can be sure: users do EVERYTHING they possibly can.

 

[The_Doc_Man]

Can you safely control in which phase your user presses the button?
You can be sure: users do EVERYTHING they possibly can.

Absolutely true. Before my Navy job, I worked at a pipeline control company. Our users were the BEST stress-testers our software ever had. If there WAS a way to break something, they broke it. Particularly since back then, the night operators on a western pipeline were lucky if their IQ got as high as their body temperature in degrees Fahrenheit. Or so I was told.

 

[estelleh]

Can you safely control in which phase your user presses the button?

I can The button is on the main screen which does nothing except give the user options of where to go. One of the options is "Backup and Exit" which does exactly that, so the backup will always be the last thing that happens before the system closes.

The backup system works fine and the user is very happy that it does exactly what she requested. My question was simply why "*.accdb" works, but "factory_be.accdb" gives me an error. The only reason I tried to change the code was that the front end (unneccesary) and the back end (necessary) get backed up every time, but I have engineered a work around that allows only the back end to be backed up.