Copying a non-shared back-end mdb while opened

[June7]

Don't know what to say. I tested the copy action in immediate window and it worked. But I am just using my home laptop, not an enterprise network. I did have issue at work when IT updated computers after which programmatic copy would no longer work. Very annoying.

[GraeagleBill]

I seem to be "out of luck" with this one. With ALL Forms/Reports closed and only one active general module running, the copy fails with the runtime error message "permission denied".

[June7]

Try it in a new db that doesn't have any modules and doesn't even have links set to backend. Just test in the Immediate Window.


Are you on an enterprise network?

[GraeagleBill]

The copy works fine when the app IS NOT active, but I get the same error if I execute the Filecopy in a new DB's immediate window if I open the independent app first.



The app has a builtin facility for re-linking all of its linked tables when the user wants to switch between business and personal backends. In the AM, I'll add come code to re-link to the alternate backend while I copy the current one and then back again.

[isladogs]

Hi Bill I've never had error 70 when creating a backup of the backend datafile

Though it shouldn't be necessary, you could try adding this line in your error handling routine

If err=70 Then Resume Next

Aso, in case it helps here is a backup routine from one of my apps.
I use very similar code with all my split databases to backup and compact both BE & FE files

Public Function BackupBEDatabase()

On Error GoTo Err_Handler


'creates a copy of the backend database UKAddressFinderBE.accdb to the backups folder with date/time suffix


    Dim fso As Object
    Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
    Dim newlength As Long
    Dim STR_PASSWORD As String
    
    STR_PASSWORD = Nz(DLookup("ItemValue", "tblProgramSettings", "ItemName='Pwd'"), "") 'modify or omit if not needed
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    strFilename = "UKAddressFinderBE.accdb"  'modify
    strFileType = Mid(strFilename, InStr(strFilename, ".")) 'e.g. .accdb
            
    strOldPath = GetLinkedDBFolder & "\" & strFilename
    
    strNewPath = GetBackupsFolder & "\BE\" & _
         Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType
         
    strTempPath = GetBackupsFolder & "\" & _
         Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType
         
   ' Debug.Print strOldPath
   ' Debug.Print strTempPath
   ' Debug.Print strNewPath
    
    If SilentFlag = True Then GoTo StartBackup:
        If MsgBox("This procedure is used to make a backup copy of the Access back end database, UKAddressFinderBE.accdb.           " & vbCrLf & _
            "The backup will be saved to the Backups folder with date/time suffix                   " & vbCrLf & _
                vbTab & "e.g. " & strNewPath & "                            " & vbCrLf & vbCrLf & _
            "This can be used for recovery in case of problems    " & vbCrLf & vbCrLf & _
            "Create a backup now?         ", _
                vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then
                    Exit Function
        Else
            DoEvents
                   
StartBackup:             
            'copy database to a temp file
            fso.CopyFile strOldPath, strTempPath
            Set fso = Nothing
                      
            'compact the temp file
            'if no password
            DBEngine.CompactDatabase strTempPath, strNewPath            
            'or  with password
            DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & STR_PASSWORD & "", , ";PWD=" & STR_PASSWORD & ""


            'delete the tempfile
            Kill strTempPath
                
            DoEvents
            
            'get size of backup
            newlength = FileLen(strNewPath) 'in bytes
             
            'setup string to display file size
            If FileLen(strNewPath) < 1024 Then  'less than 1KB
               strFileSize = newlength & " bytes"
            ElseIf FileLen(strNewPath) < 1024 ^ 2 Then  'less than 1MB
               strFileSize = Round((newlength / 1024), 0) & " KB"
            ElseIf newlength < 1024 ^ 3 Then 'less than 1GB
               strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 2), 1) & " MB)"
            Else 'more than 1GB
                strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 3), 2) & " GB)"
            End If
            
            DoEvents
            
    End If
            
    MsgBox "The Access backend database has been successfully backed up.                " & vbCrLf & _
        "The backup file is called " & vbCrLf & _
            vbTab & strNewPath & "                       " & vbCrLf & vbCrLf & _
            "The file size is " & strFileSize & "              ", vbInformation, "Access BE Backup completed"
    
Exit_Handler:
    Exit Function
    
Err_Handler:
    Set fso = Nothing
    If Err <> 0 Then
      MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & vbCrLf &  Err.description, _
    vbCritical, "Error copying database"
    End If
    Resume Exit_Handler
    
End Function

[orange]

I found an old post that involved error 70. It might be relevant and you may want to research/google file properties inherit based on a comment in the code in the link.

Then again, Colin has offered a backup routine.

[GraeagleBill]

Hey Colin,
Thanks for your thoughts on this caper. I'm leery of the "resume" simply because of the potential system environments where the app might be installed. With a bit of hindsight here, I think it best in my case to employ a command stack (common concept for processing system tasks on large IBM mainframes) that can be processed by a separate utility app. The utility app will read the FIFO stack and perform whatever commands appear. I haven't yet decided if the stack will be table or a txt file but I'll post a copy of the command stack for the current case after I've implemented the utility app.
Thanks again,
Bill

[GraeagleBill]

Colin,
I did suppress the Err 70 in the error handler but obviously that doesn't do anything that will allow the mdb copy to run successfully. While I completely appreciate your sharing your backup code, what I'm attempting to do is package up some functionality where the user can both archive a backend and in the same operation purge aging and unwanted records. My attempt to create a command stack for a utility to process independently of the primary app still encountered the Err 70. Below is a snippet of code that is attempting to release any bounding of the backend and start the utility. The utility starts as intended but again the Err 70 occurs when a copy operation is attempted.

Is there a way to "Close" the backend rather than closing forms containing bound controls? Maybe that would release any DB locks that might be in force?

Close #1               'Close the stack

DoCmd.Close acForm, "frmSettings"
DoCmd.Close acForm, "frmRegister"
DoCmd.Close acForm, "frmArchive"

Set UtilsApp = CreateObject("Access.Application")
UtilsApp.OpenCurrentDatabase "C:\eBud\eBudUtils.mdb", False

Set UtilsApp = Nothing

DoCmd.Quit         'Okay, we're done here.  Give control of the Stack to eBudUtils

The following is a snapshot of one of the stacks where the copy is commanded together with a command to verify the success of the copy by comparing file sizes. The 3rd command restarts the primary app. (The tokens are separated with semi-colons)

Archive;c:\eBud\eBudData(Personal).mdb;c:\eBud\DB-Archives\Personal-Data(1-1-2013 To 1-13-2019).mdb
VSize;c:\eBud\eBudData(Personal).mdb;c:\eBud\DB-Archives\Personal-Data(1-1-2013 To 1-13-2019).mdb
Start;c:\eBud\eBud-Source-V7.0.mdb

[isladogs]

Can we roll back a couple of steps. Is error 70 definitely occurring in the backup routine or could it be part of your other code?
Have you tried using the shift bypass on the FE then running the backup.
If that works, it confirms some other code that normally runs is causing the error.

Also have you tried my backup code just as a test?
If not, please do so.

I've never worked with call stacks so can't advise on that

[GraeagleBill]

Hi Colin,
The failures only occur when a copy is attempted within the primary app's code. Both your backup and my utility work fine if the primary app is completely closed. Failures occur EVEN if ALL forms/reports are closed and my primary app code dynamically re-links ALL non-system links to a different backend. I monitored the coming and going of the companion .ldb files as such re-linking was being done and that all was as expected. There's no difference if I start the primary app using the shift bypass.
Bill

[isladogs]

OK thanks for trying. sorry but I'm out of ideas.

[GraeagleBill]

Yes, me too. I'll have to come up with a scheme where I can lead the user to close the app and start an independent utility that attends to their archiving needs. The main concern is to insure that the archive is protected before the app starts pruning back transaction records. Do you have VBA code in your toolbox that I can use to set the archive file to READ ONLY?

[Micron]

PMFJI and very late to party, and sorry for not reading every line of every post, but wouldn't executing a cmd/bat file get around this? IIRC, it won't care if the file is open, unless something puts a lock on it. You're trying to do it with vba FileCopy command? I'd try a batch file for fun.

[GraeagleBill]

Excellent idea. The BAT command pictured below copies the backend mdf file even when the app is open. I can't seem to find the command switch that will run the xcopy WITHOUT my having to press the "f" key in the open Windows CMD window. The online description of the xcopy command states that the "/i" switch should "do the trick" but it doesn't so unless someone following the thread knows the answer to that mystery I'll have to try some other configuration.

[beilstwh]

It didn't work because the option isn't "/I". You want to use "/Y" which is don't prompt. Don't forget to use exit as the second line in the BAT file.