Run-time error '75': Path/File Access Error

E

estelleh

Member
Local time
Today, 01:24
Joined
Jul 16, 2021
Messages
56
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:

Code: 
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
Code: 
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:
Code: 
fs.CopyFile source & "*.accdb", str
making the backups very big, when all I really need to back up is the highlighted database file.
FF.JPG

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
Code: 
fs.CopyFile source & "*_be.accdb", str

Any thoughts?

TIA
 
If you know the name of the BE why not just use that instead of a *?
 
What is the description for the error and your code does not assign a proper value to str
 
Code: 
? 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.
 
do you mean you only need to backup the BE?
copy this to a Module:
Code: 
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:
Code: 
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"
 
arnelgp said:
do you mean you only need to backup the BE?
copy this to a Module:
Code: 
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:
Code: 
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"
Click to expand...
Thank you - I will give that a try :)
 
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.
 
ebs17 said:
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.
Click to expand...
I did not see any problem on my test.
 
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.
 
exit the system
Click to expand...
...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.
 
Last edited:
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
 
Pat Hartman said:
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
Click to expand...

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.
 
The_Doc_Man said:
certainly until the last bound form is closed and the last manually opened recordset is closed.
Click to expand...
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.
 
ebs17 said:
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.
Click to expand...
It is a single user system
 
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 said:
Can you safely control in which phase your user presses the button?
You can be sure: users do EVERYTHING they possibly can.
Click to expand...

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.
 
ebs17 said:
Can you safely control in which phase your user presses the button?
Click to expand...
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.
 
You must log in or register to reply here.

Similar threads

A
Solved VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file
2
Replies
24
Views
888
isladogs
isladogs
NickCoe(UK)
Getting the true location of VBA reference files in 365 ctr.
Replies
17
Views
699
NickCoe(UK)
NickCoe(UK)
N
Solved How to remove this error "the execution this software stopped due to run time" error in MS Access VBA
Replies
11
Views
555
Minty
Minty
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
934
The_Doc_Man
The_Doc_Man
M
Solved How to search with an apostrophe without throwing up run-time error '3075'
Replies
15
Views
454
MediaHolic
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom