The Right Synthax to backup Backend (1 Viewer)

Moore71

DEVELOPER
Local time
Today, 16:04
Joined
Jul 14, 2012
Messages
158
Hi,
Please what is the right synthax to backup only the backend of my database.
This is the code i try using, but it only backup the frontend and my database is splitted and in different folders on a network:

Public Function db_backup()
Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String

sourceFile = CurrentProject.FullName
path = CurrentProject.path
name = CurrentProject.name
destinationFile = "CurrentDb" & _
Left(name, Len(name) - 6) & "_backup" & "_" & _
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".accdb"

'this removes a file created on the same day
If Dir(destinationFile) <> "" Then
Kill destinationFile
End If

'this creates a backup into destination path
If Dir(destinationFile) = "" Then

Set aFSO = CreateObject("Scripting.FileSystemObject")
aFSO.CopyFile sourceFile, destinationFile, True

MsgBox "A database backup has been stored under " & _
destinationFile
End If
End Function

Thank you in advance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:04
Joined
May 7, 2009
Messages
19,169
Dim strPathBackEnd as string
Dim td as TableDef
Dim db as Dao.Database
Dim strDestination As String

set db=currentdb
set td=db.tabledefs("anyLinkTableNameHere")
strPathBackEnd = Replace(td.Connect, ";DATABASE=", "")

strDestination = REPLACE(strPathBackEnd, ".accdb", "") & year(date) & "_" & Month(date) & "_" & day(date) & ".accdb"

FileCopy strPathBackEnd, strDestination

set td=nothing
set db=nothing
 

Moore71

DEVELOPER
Local time
Today, 16:04
Joined
Jul 14, 2012
Messages
158
Thanks for your quick response, Arnelgp.

I implemented the code but it gives me an error in the following line:
set td=db.tabledefs("anyLinkTableNameHere")

I also change my backend name like this:
set td=db.tabledefs("DinavicNew_be.accdb")
but to no avail. So how do I correct this?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:04
Joined
May 7, 2009
Messages
19,169
you put in the name of a Table (a linked table), whatever table as long as it is linked to your BE database.
 

Moore71

DEVELOPER
Local time
Today, 16:04
Joined
Jul 14, 2012
Messages
158
Thank you once again.
Does that means i will be putting tables name one by one, because I have many tables in the database to backup
 

Moore71

DEVELOPER
Local time
Today, 16:04
Joined
Jul 14, 2012
Messages
158
Thank you very much. It worked well.

But I would like to add, supposing I don't want to backup to CurrentDb and maybe I want to backup to any folder the user set, what is the syntax to implement that?

Thanks once again
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:04
Joined
May 7, 2009
Messages
19,169
here's another version. call it without any destination folder name and it will make backup to the path where your FE is. just remember to replace anyLinkTableNameHere with your linked table name (only one).
Code:
Public Sub subBackUpBE(Optional ByVal strNewPath As String = "")

    Dim strBackEnd As String
    Dim td As TableDef
    Dim db As Dao.Database
    Dim strDestination As String
    Dim lngPos As Long
    
    On Error GoTo Err_Handler
    
    Set db = CurrentDb
    Set td = db.TableDefs("anyLinkTableNameHere")
    strBackEnd = Replace(td.Connect, ";DATABASE=", "")
    
    lngPos = InStrRev(strBackEnd, "\")
    
    If strNewPath <> "" Then
        strNewPath = Replace(strNewPath & "\", "\\", "\")
    
        If lngPos > 0 Then
            strBackEnd = Mid(strBackEnd, lngPos + 1)
        End If
        
        strDestination = strNewPath & strBackEnd
    
    Else
        strDestination = strBackEnd
    End If
    
    strDestination = Replace(strDestination, ".accdb", "") & "_" & Year(Date) & "_" & Month(Date) & "_" & Day(Date) & ".accdb"
    
    If Dir(strDestination) <> "" Then
        If MsgBox(strDestination & " already exists. Do you want to overwrite it?", vbYesNo + vbQuestion) = vbYes Then
            Kill strDestination
            FileCopy strPathBackEnd, strDestination
        End If
    Else
        
        FileCopy strPathBackEnd, strDestination
    End If
    
    Exit Sub
    
Err_Handler:
    Set td = Nothing
    Set db = Nothing
    MsgBox Err.No & " - " & Err.Description, , "BackEnd Backup"

End Sub
 

Moore71

DEVELOPER
Local time
Today, 16:04
Joined
Jul 14, 2012
Messages
158
Once again, thank you for your kind gesture all these while.
I am blessed by your responses. May God Almighty reward your efforts
 

Users who are viewing this thread

Top Bottom