Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-19-2019, 01:35 PM   #1
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 613
Thanks: 28
Thanked 112 Times in 101 Posts
sxschech is on a distinguished road
Backup a Backend - enhanced

I saw theDBGuy's link post #2 to backup a backend. https://www.access-programmers.co.uk...ghlight=backup
Good timing as I was about to add a backup option to one of my dbs. Wasn't sure if should post in the original thread or in a new thread. Added some parameters to the existing code so that can do one or more of the following:
-backup to the same path as the linked file
-backup to a subfolder of the selected path
-backup to a folder other than the linked table or currentproject.path
-use the same name as the linked file + date
-specify a different name for the backup file + date

Since linked table is now a parameter, this will allow for backing up multiple backends if there are linked tables from more than one file. I left the date hard coded (in my code I didn't include the time portion), but this could be modified to pass as a parameter if the date is not needed as part of the backup name.

Code:
Public Sub BackUpAndCompactBE(LinkedTableName As String, LinkedPath As Boolean, Optional SubFolder As String, Optional BackupFolder As String, Optional BackupName As String)
'Originally found through https://www.access-programmers.co.uk/forums/showthread.php?t=305939&highlight=backup
'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Modified by theDBguy on 5/27/2019
'Source: http://www.accessmvp.com/thedbguy
'http://www.accessmvp.com/thedbguy/codes.php?title=backup
'20190719
'Added optionals:
'   linkedpath - save to same folder as the linked file location
'   subfolder - save the backup in a subfolder of the selected path
'   backupfolder - save the backup in a completely different location
'   backupname - use a name different than the source name
'20190719
On Error GoTo errHandler

    Dim oFSO As Object
    Dim strDestination As String
    Dim strSource As String
    Dim strTableName As String
    Dim strFileName As String
    Dim strDestinationPath As String
    
    strTableName = LinkedTableName 'name of your linked table
        
    'Get the source of your back end
    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _
            "Database=")(1), ";")(0)
    
    'Get the name of the backup file
    If BackupName = "" Then
        strFileName = "\" & FileNameNoExt(strSource)
    Else
        strFileName = "\" & BackupName '"\SCR_BE" 'name of your backup file
    End If
    
    'Determine which path to use
    If LinkedPath = True Then
        strDestinationPath = FILEPATH(strSource)
    Else
        strDestinationPath = CurrentProject.Path
    End If
    
    'Determine your destination
    If SubFolder = "" Then
       strDestination = strDestinationPath & strFileName & "_" & Format(Now, "yyyymmdd") & ".accdb"
    Else
         strDestination = strDestinationPath & SubFolder & strFileName & "_" & Format(Now, "yyyymmdd") & ".accdb"
    End If
    'Change backup folder if not using Project or Linked Path
    If BackupFolder <> "" Then
        If Right(BackupFolder, 1) <> "\" Then
            BackupFolder = BackupFolder & "\"
        End If
        strDestination = Replace(strDestination, FILEPATH(strDestination), BackupFolder)
    End If
    
    '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
Code:
Function FileNameNoExt(strPath As String) As String
 'https://sqlaccxl.wordpress.com/2013/03/06/vba-function-to-extract-file-name-withwithout-file-extension-or-path-only/
    Dim strTemp As String
    strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1)
    FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1)
End Function
 
'The following function returns the filename with the extension from the file's full path:
Function FileNameWithExt(strPath As String) As String
    FileNameWithExt = Mid$(strPath, InStrRev(strPath, "\") + 1)
End Function
 
'the following function will get the path only (i.e. the folder) from the file's full path:
Function FILEPATH(strPath As String) As String
    FILEPATH = Left$(strPath, InStrRev(strPath, "\"))
End Function

Function FileExtension(strPath As String) As String
'Get the extension of the file name
'https://social.msdn.microsoft.com/Forums/en-US/d112ca5d-2304-4707-bade-b27869c9359f/vba-excel-getting-file-extension?forum=isvvba
'20190129
    FileExtension = Split(strPath, ".")(UBound(Split(strPath, ".")))
End Function

sxschech is offline   Reply With Quote
The Following User Says Thank You to sxschech For This Useful Post:
theDBguy (07-19-2019)
Old 07-19-2019, 02:16 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,651
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Backup a Backend - enhanced

Hi. Thanks for the enhancements. I might incorporate that the next time I update the website. I actually have some changes I’ve been meaning to add, like handling password protected BEs. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-19-2019, 02:24 PM   #3
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 613
Thanks: 28
Thanked 112 Times in 101 Posts
sxschech is on a distinguished road
Re: Backup a Backend - enhanced

Glad to be able to contribute to your useful code.

sxschech is offline   Reply With Quote
Old 07-19-2019, 03:18 PM   #4
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 339
Thanks: 529
Thanked 24 Times in 23 Posts
Tera will become famous soon enough
Re: Backup a Backend - enhanced

Just out of curiosity.
Why not simply use SSMS (Sql Server Management Studio)'s backup. Several click to add a backup job and add the job to a schedule and you're done. You won't need to bother anything anymore.
Tera is offline   Reply With Quote
Old 07-20-2019, 01:20 AM   #5
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,562
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Backup a Backend - enhanced

Quote:
Originally Posted by Tera View Post
Just out of curiosity.
Why not simply use SSMS (Sql Server Management Studio)'s backup. Several click to add a backup job and add the job to a schedule and you're done. You won't need to bother anything anymore.
I thought this was for an Access BE ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Tera (07-20-2019)
Old 07-20-2019, 05:12 AM   #6
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 339
Thanks: 529
Thanked 24 Times in 23 Posts
Tera will become famous soon enough
Re: Backup a Backend - enhanced

Quote:
Originally Posted by Gasman View Post
I thought this was for an Access BE ?
Oh sorry. It seems you're right. A misunderstanding on my side
Tera is offline   Reply With Quote
Old 07-20-2019, 09:24 PM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Backup a Backend - enhanced

i always use third party program. Goodsync, set and forget. it has un attended feature. allows backup on file change. allows backup even when the file is in use.


__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
back end , backup

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Backup a backend nobby General 3 07-21-2019 08:44 AM
The Right Synthax to backup Backend Moore71 Modules & VBA 7 09-30-2015 07:59 AM
Automatic Backend Backup code Moore71 General 1 02-05-2014 05:05 AM
Backup Backend from frontend rainman89 Modules & VBA 17 07-07-2007 03:48 PM
backend backup razaqad General 11 03-30-2006 09:04 AM




All times are GMT -8. The time now is 02:54 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World