Backup Backend Database at the Back end Location

[yashuaking]

Hi I have a split database and a code to backup the back end at the front end however I want the back end to backed up in a folder called "Backups" a the Back End and not at the front end. This is the code am using now to back up the destination at the front end. Please help me change the destination to the back end(Server)

Public Sub BackUpDatabase()

On Error GoTo Err_Handler
    Dim oFSO As Object
    Dim strDestination As String
    Dim strSource As String
    Dim path As String, name As String
    
    path = CurrentProject.path
    name = CurrentProject.name
    Const conPATH_FILE_ACCESS_ERROR = 75
    
 'Get the source of the back end
    strSource = Split(Split(CurrentDb.TableDefs("AssessmentT").Connect, "Database=")(1), ";")(0)
    
    'Determine backup destination
    strDestination = path & "\" & Left(name, Len(name) - 6) & "_backup" & "_" & _
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".accdb"

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

'this creates a backup into destination path
    If Dir(strDestination) = "" Then
    '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
    Kill strDestination & ".cpk"
    
    'Notify users
    MsgBox "Backup file '" & strDestination & "' has been created.", vbInformation, "Backup Completed!"
    
End If

Exit_Button_Backup:
 
  Exit Sub

Err_Handler:
  If Err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & strDestination & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox Err.Description, vbExclamation, "Error Creating " & strDestination
 
End If
    Resume Exit_Button_Backup



End Sub

[zmbd]

Line #17: Alter this to change the path to the location desired...

Line#17 is based on the "name" and "path" in lines #9 and #10
Line #9 would more than likely be the easiest place to put your network or other drive/path information

You can also use: Select a File or Folder using the FileDialog Object use the "folder" method to have the user select the location returning the value to line #9

One other thing I noted in your code is the use of reserved tokens as variables ("name" "path" etc...). I highly advise against doing this as it will eventually cause you a nightmare of troubleshooting ; thus, you might want to review::
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[yashuaking]

Thanks Zmbd I get your point about changing the path to the network location. I tried this and it worked but I realize whenever I have to install the program on another computer, I have to change the code to suite that network path but I do not want it this way. I want the database to detect the source path and create the backup in the folder on the source path.I want the backup to be saved in the Back end folder irrespective of which machine I install on it on. I also appreciate your view about the reserved words. I would work on that. Thanks for the quick answer.

[yashuaking]

This was the code I was using initially and it works fine but whenever I have to install on another computer, I have to go into the code and change the code to suite the new network path so I modified it into the above code which is also limiting me to the front end

Public Function Button_Backup()
Dim str As String
Dim buf As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
 
Const conPATH_FILE_ACCESS_ERROR = 75
On Error GoTo Backup_Button_Backup
 
'Where to backup to. Creates BACKUP folder is it does not exist
If MsgBox("Do You want to backup database?", vbYesNo + vbQuestion, "Falcon Systems") <> vbYes Then Exit Function
buf = path & "\\CREDIT-MANAGER\Falcon Systems\Data\Backups\"
MkDir buf
 
    Resume Backup_Button_Backup
 
Backup_Button_Backup:
 
'Create a folder in BACKUP with YYYY-mm-dd hhmm-ss as the name
 
MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss")
str = path & "\\CREDIT-MANAGER\Falcon Systems\Data\Backups\" & MD_Date
 
'Where is the data to backup
source = path & "\\Credit-Manager\Falcon Systems\Data\"
MkDir str
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile source & "*.accde", str
Set fs = Nothing
 
'Successful
MsgBox "A database backup has been stored by name" & vbCrLf & MD_Date & vbCrLf & "Successfully!", _
        vbInformation, "Falcon Systems Backup Successful"
 
Exit_Button_Backup:
 
  Exit Function
 
'Use this part if you have not used hh-mm-ss
 
Err_Button_Backup:
  If Err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox Err.Description, vbExclamation, "Error Creating " & str
 
End If
    Resume Exit_Button_Backup
End Function

[zmbd]

use the UNC naming convention.

For example:
My IT guys are very kind and my personal network drive is mapped to "Z:" however the UNC path is:
"\\servername\u sers\dept\qaqc\ z_personaldrive \"

(I also updated my first post :) )

[yashuaking]

Ok I'll try it. Thanks very much

[yashuaking]

But is there a way to use a path similar to this one:

strSource = Split(Split(CurrentDb.TableDefs("AssessmentT").Connect, "Database=")(1), ";")(0)

I tried using it as the destination but I received an error. Am thinking I have to modify this code to fall into the split folder instead of file

[redneckfiveo]

yashuaking ~ I know this is an older post, however for folks searching for an answer to your question I have two bits of code that may help.

I use a simple module to get the database backend path. The first one provides the exact directory where the backend resides.

Public Function GetDBBEPath() As String
On Error GoTo GetDBBEPath_Err
     Dim strFullPath As String
     strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("NameLinkedTable").Connect, 11)
     GetDBBEPath = Left(strFullPath, InStrRev(strFullPath, "\"))
     'for troubleshooting Debug.Print GetDBBEPath
GetDBBEPath_Exit:
    Exit Function
GetDBBEPath_Err:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
    VBE.ActiveCodePane.CodeModule & "(GetDBBEPath Function).", vbOKOnly, "Error"
    LogError ("GetDBBEPath Error " & VBE.ActiveCodePane.CodeModule & " " & Err.Number & " " & Err.Description & " " & Err.Source & " " & CreateObject("WScript.Network").userName & " " & CreateObject("WScript.Network").ComputerName)
End Function
[/CODE}
Please make sure you replace "NameLinkedTable" with any one of your linked table names.  The process is simple, if your linked tables are stored at I:\Databases\MyDatabase\Db.accbe, then the function returns ... [B]I:\Databases\MyDatabase
[/B].

For ease, I have included another function which does the same, however steps up one subfolder.  I use this function a lot and have the need to access other folders in the same parent directory.  Please make sure you replace "NameLinkedTable" with one of your linked table names.

For this to work you also need to replace "\FolderName\" with whatever folder your BE file resides.  In the above sample, the \FolderName\ would read "\MyDatabase\"

[CODE}
Public Function GetDBBEPathSub() As String
On Error GoTo GetDBBEPathSub_Err

Dim strFullPath As String
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("NameLinkedTable").Connect, 11)
    GetDBBEPathSub = Left(strFullPath, InStrRev(strFullPath, "\FolderName\"))
     'Debug.Print GetDBBEPathSub
GetDBBEPathSub_Exit:
    Exit Function
GetDBBEPathSub_Err:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
    VBE.ActiveCodePane.CodeModule & "(GetDBBEPathSub Function).", vbOKOnly, "Error"
    LogError ("GetDBBEPathSub Error " & VBE.ActiveCodePane.CodeModule & " " & Err.Number & " " & Err.Description & " " & Err.Source & " " & CreateObject("WScript.Network").userName & " " & CreateObject("WScript.Network").ComputerName)
End Function

When you need to use the folder path, simply call the function and add whatever you need afterwards such as this:

strDatabasePath = GetDBBEPath & "\" & "Folder or file path here"

Also, I didn't remove my crazy Error Handling or error logger. Happy coding!

[neelsfer]

I use this utility i got from the net once upon a time. Customize it for you and check the code. It is a separate utility, but works great. Hold in shift when opening

[twinnyfo]

The .Connect call should be all that is needed. Then, once you have that string, removing the left 10 characters (";DATABASE= ") will get you the entire path and file name. Then, from the end of the file name, look for the first "\" and take the Left() of the remaining string. I do this in a bit of an opposite way to find out the name of the DB to which my DB is linked. But once you have that string, it is merely an issue of manipulating or identifying the characters that you want/don't want.