Error renaming a backend DB

[GraeagleBill]

I have a new general module that needs to relink to a temporary backend so as to free up the current backend making it available for renaming. Module heading explains the whole purpose. Anyway, I get an error when trying to rename. I thought relinking to a temporary DB would free up the current, but perhaps further action is required? I don't see an explicit DoCmd to close just a backend DB?

Public Sub RevertDB(strCurDB As String, strTempDB As String, frmClose As String, BkUpDB As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  07/16/2024
'  Revert current backend to a previous level from "Round Robin" backup collection.
'  Close invoking form, re-link backend to a temporary DB (usually the sandbox DB),
'  rename the current client's DB so as to preserve it, replace the backend DB from
'  the backups collection and re-link.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strPreservedDB As String
Dim strAction
Dim FSO As Object


'On Error GoTo RevertErr


DoCmd.Close acForm, frmClose         'Close the invoking form


Call ReLink(strTempDB, True)


strPreservedDB = Replace(strCurDB, "Data.mdb", "Data(Preserved).mdb")
strAction = "renaming"
Name strCurDB As strPreservedDB




Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile BkUpDB, strCurDB
Set FSO = Nothing


Call ReLink(strCurDB, True)


ExitSub: Exit Sub


RevertErr:
MsgBox "Error " & Err.Number & " while attempting to " & strAction & " DB" & vbNewLine & _
       "Description is " & Err.Description
GoTo ExitSub
       
End Sub

I suppressed the error handler so I could get the following screenshots

[kd2017]

Was the backend DB opened/locked by the front end when you tried it?

[Micron]

You're trying to rename a be when it is still open? Just because you unlink from it doesn't mean it closes. You cannot use Name statement on an open file. Did you debug the variables to ensure the paths were valid? Did you copy the output (at least up to the last folder) and try pasting it into a File Explore dialog to be sure?

[GraeagleBill]

Closing the backend was, I thought, the focus of my OP. I looked for some form of DoCmd.Close acdatabase, MyBackend but only found what amounted to a DoCmd.Quit. If there's a way to close the backend I'd sure like to know how to do that.

[Micron]

try

Dim OtherDB As Object
Set OtherDB = GetObject("C:\etc\etc\etc.SomeOtherDb.accdb") 'or mdb?
OtherDB.Application.Quit

I have to assume that code won't be in the db you're trying to close, otherwise you'd use Quit as you say. It doesn't test if the db is actually open. No idea what the error number would be if it was not.

[moke123]

Are you trying to back up the backend?

https://www.accessmvp.com/thedbguy/c...p?title=backup

[GraeagleBill]

BUMMER! Same error.

snip

Dim ClientDB As Object
'On Error GoTo RevertErr


DoCmd.Close acForm, frmClose         'Close the invoking form


Call ReLink(strTempDB, True)


strPreservedDB = Replace(strCurDB, "Data.mdb", "Data(Preserved).mdb")
strAction = "renaming"




Set ClientDB = GetObject(strCurDB)
ClientDB.Application.Quit
Name strCurDB As strPreservedDB          <<<<<<<<<<<<

Snip

[Micron]

At this point I can only point you back to questions 2 and 3 in my first post. If I was sure about that, I would open an unrelated be or even an unsplit db and try that code on it.
Also https://learn.microsoft.com/en-us/of...error-error-75

[GraeagleBill]

So that I don't waste anyone else time, I think I know how to solve this issue using an operating system technique. I'll post back with the solution after I prove myself right about the idea.

[moke123]

Just some slight changes to Brent's code will do what you want.
I added arguments for the table, backend name and backend file extension.

Public Sub BackUpAndCompactBE(vTable As String, BEName As String, FType As String)

'vTable is the name of a table in your backend
'BEName is the name for the saved backend
'FType is the file type of the backend ie. Accdb or Mdb

'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Modified by theDBguy on 5/27/2019
'Source: http://www.accessmvp.com/thedbguy


On Error GoTo errHandler


    Dim oFSO As Object
    Dim strDestination As String
    Dim strSource As String
    Dim strTableName As String
    Dim strFileName As String


    strTableName = vTable  'name of your linked table
    strFileName = "\" & BEName      'name of your backup file


    'Get the source of your back end
    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _
            "Database=")(1), ";")(0)
    
    'Determine your destination
    strDestination = CurrentProject.Path & strFileName & "." & FType '" (" _
           ' & Format(Now, "yyyymmddhhnnss") & ").accdb"
    
    'Flush the cache of the current database
    DBEngine.Idle
    
    'Create a file scripting object that will backup the db
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.CopyFile strSource, strDestination
    Set oFSO = Nothing
    
    'Compact the new file, ...
    Name strDestination As strDestination & ".cpk"
    DBEngine.CompactDatabase strDestination & ".cpk", strDestination
    'Uncomment the following line and comment the previous line
    'if your backend file is password protected or if you want the backup to have a password
    'DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=YourPassword"
    Kill strDestination & ".cpk"
    
    'Notify users
    MsgBox "Backup file '" & strDestination & "' has been created.", _
            vbInformation, "Backup Completed!"
    
errExit:
    Exit Sub
    
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit
    
End Sub

[GraeagleBill]

Okay, the app at issue supports multiple clients, (one BE DB per client). When the app starts-up, the app interrogates the command string in search of the client name so that the appropriate BE tables be linked. Thus, no BEs are opened until that operation is completed. I've implemented a command stack in the app's front-end that will get serviced before anything else is done. The client UI, for backup restores, now simply puts the "Revert" command on the stack together with the name of the requested backup DB and the client user is instructed to restart the app before the app quits itself. With this approach, none of the conflicts due to an open BE are encountered. Another advantage of this approach is the simple addition of a undo command being placed on the stack. The "Revert" command processor is included here below.

Thanks to all that gave this OP their valuable time,
Bill

Public Sub RevertDB(strClient As String, strBkUpDB As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  07/16/2024 - "Revert" command processing
'  Followinbg a "preserve" renaming of the clients BE DB, revert to a backend copy
'  the client revert UI choose from the "Round Robin" backup collection.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strAction
Dim FSO As Object
Dim strClientDB As String
Dim strPreservedDB As String


On Error GoTo RevertErr


strClientDB = TMSRoot & "TMS-Clients\" & strClient & "\" & strClient & " DATA.mdb"


strPreservedDB = Replace(strClientDB, "Data.mdb", "Data(Preserved).mdb")
strAction = "rename"
Name strClientDB As strPreservedDB


strAction = "restore BE"
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile strBkUpDB, strClientDB
Set FSO = Nothing


ExitSub: Exit Sub


RevertErr:
MsgBox "Error " & Err.Number & " while attempting to " & strAction & " DB" & vbNewLine & _
       "Description is " & Err.Description
GoTo ExitSub
       
End Sub