Results 1 to 14 of 14
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    How to backup and zip backup copy of database

    Hi guys,


    i am using the following vba code to backup my database.

    Code:
    Dim Source As String
    Dim Target As String
    Dim retval As Integer
    Dim BckFileDateTime As Variant
    
    
    'The Source database to backup. Set to the current database
    Source = CurrentDb.name
    
    
    'Set current date and time
    BckFileDateTime = Format(Date, "dd-mm-yyyy ") & Format(time, "hh-mm-ss")
    
    
    'The path where we save the backup file. Set to current source path + a folder called dbBackup
    Target = CurrentProject.Path & "\dbBackup\DbBackup_@_"
    
    
    'Add the current date and time to the filename
    Target = Target & BckFileDateTime & ".accdb"
    
    
    ' create the backup
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(Source, Target, True)
    Set objFSO = Nothing
    
    
    'Opens the folder of the file you just created
    Application.FollowHyperlink CurrentProject.Path
    The above works as expected. Now i would like to compress/zip the backup file, and then delete the initial non compressed/zipped file.

    i have tried the following but not working.

    Code:
    Dim sWinZip As String
    Dim sZipFile As String
    Dim sZipFileName As String
    Dim sFileToZip As String
    
    
    sWinZip = "C:\Program Files\WinZip\winzip64.exe" 'Location of the WinZip program
    sZipFileName = "sample" & ".zip"
    sZipFile = CurrentProject.Path & "\dbBackup\" & sZipFileName
    'sFileToZip = sBackupPath & sBackupFile
    
    
    Call Shell(sWinZip & " -a " & sZipFile & " " & sFileToZip, vbHide)
    
    
    Beep
    MsgBox "Backup was successful and saved @ " & Chr(13) & sZipFileName, vbInformation, "Backup Completed"
    Wanted to use windows explorer to perform the compression/zipping but don't know how to go about it, that's why i sticked to * sWinZip = "C:\Program Files\WinZip\winzip64.exe" 'Location of the WinZip program *

    Can someone possibly help me massage the initial backup code to include the compress/zip functionality using the windows explorer?

    Thank you.

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Why not create a powershell script and run it from Access VBA?

    https://social.msdn.microsoft.com/Fo...a?forum=isvvba

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This is what I use to create zip folder and insert file:
    Code:
    Dim strZip As String
    Dim strExtract As String
    Dim objApp As Object
    
    Set objApp = CreateObject("Shell.Application")
    strZip = gstrBasePath & "Editing\ConstructionExtract.zip"
    strExtract = gstrBasePath & "Editing\ConstructionExtract.accdb"
    
    'create empty zip folder
    'found this on web, no idea what the Print line does but if it isn't there, this won't work
    Open strZip For Output As #1
    Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
    'copy file into zip folder
    'variable for source file doesn't seem to work in this line so not using strZip variable
    'also double parens not in original example code but won't work without
    objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Editing\ConstructionExtract.accdb"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    wow, I thought is could easier than that!!!
    Thank you @ranman and @june 7. will dig in it, hopping to get it right.
    I will post the full code (backup - zip - permanently deleted initial backup .accdb file (plus hide zipped file if possible)) in case am able to make it work as expected.

  6. #6
    Shadow9449 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Feb 2017
    Posts
    38
    Just another option:

    I've been using Albert Kallal's solution for over 10 years and it works perfectly.

    http://www.kallal.ca/zip/index.htm

    If the other solutions are working for you then great. Otherwise maybe check it out and see if it helps...

    Cheers

  7. #7
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by ezybusy View Post
    Hi guys,
    i am using the following vba code to backup my database.

    Code:
    Dim Source As String
    Dim Target As String
    Dim retval As Integer
    Dim BckFileDateTime As Variant
    
    
    'The Source database to backup. Set to the current database
    Source = CurrentDb.name
    
    
    'Set current date and time
    BckFileDateTime = Format(Date, "dd-mm-yyyy ") & Format(time, "hh-mm-ss")
    
    
    'The path where we save the backup file. Set to current source path + a folder called dbBackup
    Target = CurrentProject.Path & "\dbBackup\DbBackup_@_"
    
    
    'Add the current date and time to the filename
    Target = Target & BckFileDateTime & ".accdb"
    
    
    ' create the backup
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(Source, Target, True)
    Set objFSO = Nothing
    
    
    'Opens the folder of the file you just created
    Application.FollowHyperlink CurrentProject.Path
    The above works as expected. Now i would like to compress/zip the backup file, and then delete the initial non compressed/zipped file.

    i have tried the following but not working.

    Code:
    Dim sWinZip As String
    Dim sZipFile As String
    Dim sZipFileName As String
    Dim sFileToZip As String
    
    
    sWinZip = "C:\Program Files\WinZip\winzip64.exe" 'Location of the WinZip program
    sZipFileName = "sample" & ".zip"
    sZipFile = CurrentProject.Path & "\dbBackup\" & sZipFileName
    'sFileToZip = sBackupPath & sBackupFile
    
    
    Call Shell(sWinZip & " -a " & sZipFile & " " & sFileToZip, vbHide)
    
    
    Beep
    MsgBox "Backup was successful and saved @ " & Chr(13) & sZipFileName, vbInformation, "Backup Completed"
    Wanted to use windows explorer to perform the compression/zipping but don't know how to go about it, that's why i sticked to * sWinZip = "C:\Program Files\WinZip\winzip64.exe" 'Location of the WinZip program *

    Can someone possibly help me massage the initial backup code to include the compress/zip functionality using the windows explorer?

    Thank you.
    Hi Mr. ezybusy
    Thanks a lot I was searching for a long time the method to backup a db and you solved it somewhat. But I just want to backup the tables of my db but not all database. Is there any method so that I my create backup the same file which include only tables of the db.

  8. #8
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by June7 View Post
    This is what I use to create zip folder and insert file:
    Code:
    Dim strZip As String
    Dim strExtract As String
    Dim objApp As Object
    
    Set objApp = CreateObject("Shell.Application")
    strZip = gstrBasePath & "Editing\ConstructionExtract.zip"
    strExtract = gstrBasePath & "Editing\ConstructionExtract.accdb"
    
    'create empty zip folder
    'found this on web, no idea what the Print line does but if it isn't there, this won't work
    Open strZip For Output As #1
    Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
    'copy file into zip folder
    'variable for source file doesn't seem to work in this line so not using strZip variable
    'also double parens not in original example code but won't work without
    objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Editing\ConstructionExtract.accdb"
    Hi June7 I have tried your method its create empty zip/rar file but its not copying the db file into it I think I am making mistake somewhere. I think I am mistaking in last lines
    'copy file into zip folder
    'variable for source file doesn't seem to work in this line so not using strZip variable
    'also double parens not in original example code but won't work without
    objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Editing\ConstructionExtract.accdb"

  9. #9
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Quote Originally Posted by ijaz8883 View Post
    Hi Mr. ezybusy
    Thanks a lot I was searching for a long time the method to backup a db and you solved it somewhat. But I just want to backup the tables of my db but not all database. Is there any method so that I my create backup the same file which include only tables of the db.
    There are many ways you can do that.
    however if you still want to use the code i pasted above, then do the following:
    - split your database (separate front end from back end).
    - Rename tha backend db.
    - open front end application and relink all tables to the backend tables.
    - then in the code change the following line:

    Code:
    'The Source database to backup. 
    Source = CurrentDb.name
    to

    Code:
    'The Source database to backup. 
    Source = CurrentProject.Path & "\nameofbackendDb.accdb"
    Not tested it yet, but i think it should do the job.

    I am still struggling with the compression and deletion the initial backup file, but for the meantime if you want to prevent people from messing up with the backup file then change the following line:

    Code:
    Target = Target & BckFileDateTime & ".accdb"
    to

    Code:
    Target = Target & BckFileDateTime & ".bckupfileaccdb"
    that way, you change the extension of the backup file to something that unrecognized by the system. So that when you want to work with it, you just check file name extension from windows, then change the file's extension to .accdb.

    not really clean as method, but that's what i am holding on till i get the way out for the compression and if possible password protection of the compressed file.

  10. #10
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Quote Originally Posted by ijaz8883 View Post
    Hi June7 I have tried your method its create empty zip/rar file but its not copying the db file into it I think I am making mistake somewhere. I think I am mistaking in last lines
    'copy file into zip folder
    'variable for source file doesn't seem to work in this line so not using strZip variable
    'also double parens not in original example code but won't work without
    objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Editing\ConstructionExtract.accdb"
    Check solution provided by Shadow9449 on following link: http://www.kallal.ca/zip/index.htm

    I have not had time to test it yet, but the description seem to be what we are looking for.

    In case you are able to get it right, kindly paste the code here so that we all benefit.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You should paste your actual code for analysis, not my example. Surely you modified to use your file path/name.

    I just tested my code and worked perfect.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Hi Mr. June7 & Mr. ezybusy

    I have made the file successfully by Backup DB and Backup BackEnd Db and also file in Zip mode with your help
    but Now I am facing hard to delete the file created for zip the DB. I have made saparate button to delete the file but its not enough.
    Error=1. File is not deleting which is created for Zip however file is inserted into zip perfectly.
    Error=2 When I click on Hyperlink option No its give error and ask to debug etc....... Otherwise if click yes its done.
    I may also make an msg box here but I also want to remove this error in case of hyperlink error.
    "Advance sorry for my cheap coding"
    BackupDBFolder.zip

    Lets Check My DB and let me suggest what to do more???
    I Hope this file will also useful for next users

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Apparently you have not run Debug > Compile. If you did, the first message you get should be:

    "Function on left-hand side of assignment must return Variant or Object"

    Line that errors is: MsgBox = "File created successfully". Remove the = sign.

    Should have Option Explicit in the header of every module. Set VBA editor to do this automatically with new module: Tools > Options > check Require Variable Declaration.

    Manually add line to existing modules then run Debug > Compile.

    Step debug code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Thanks a lot Mr. June7
    I have solved the problem with msgbox
    Now I am trying to getpath() function for work this zip function but its making problem in last line I shall try tomorror if its soved otherwise ask it.
    Thanks again.
    Sorry ezybusy I interrupted in your question section but its was necessary for me hope you will never mind it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA unable to be read from a backup copy
    By breezett93 in forum Access
    Replies: 1
    Last Post: 08-23-2017, 03:24 PM
  2. Backup Database
    By DPS in forum Access
    Replies: 5
    Last Post: 10-30-2015, 08:20 AM
  3. Backup database
    By mathhero in forum Access
    Replies: 2
    Last Post: 10-30-2015, 04:49 AM
  4. Database backup automation
    By aat in forum Access
    Replies: 2
    Last Post: 09-17-2010, 07:24 PM
  5. Making a backup copy of table or database
    By wasim_sono in forum Access
    Replies: 0
    Last Post: 03-09-2006, 05:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums