Solved

Access VBA script to unzip a SQL Server backup file to a selected folder

Posted on 2016-10-16
12
34 Views
Last Modified: 2016-10-17
I need a VBA script to unzip a SQL Server backup file from a "tar" zipped file
I will send parameters to the script containing:
  • The location of the zipped (tar) file
  • The name of the file
  • The name of the database file within the zip file. Please note that this zipped file contains several backed up databases, but I only want one of them.
  • The location of the folder to extract the file to.

The script will unzip the file, using 7-Zip to the selected location.

All suggestions gratefully received.
0
Comment
Question by:rick_danger
  • 6
  • 5
12 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
You can use Shell command to execute any program. For 7zip you can use this sub:
Public Sub un7zip(ITarL As String, ITarF As String, ODir As String, F As String)
'ITarL - location of archive, ITarF - archive name, ODir - output folder, F - File name
Dim cmd As String, retval As Double
cmd = "c:\Program Files\7-Zip\7z.exe e " & Chr(34) & ITarL & ITarF & Chr(34) & " -o" & Chr(34) & ODir & Chr(34) & " " & Chr(34) & F & Chr(34)
retval = Shell(cmd, vbNormalFocus)
End Sub

Open in new window

Check and correct path to file 7z.exe in your system
Usage sample:
Call un7zip("d:\downloads\", "Doc1.tar", "d:\tmp\test 1\", "Test.accdb")
0
 

Author Comment

by:rick_danger
Comment Utility
Thanks, but I have a few problems.

Line 4 is the cmd line. If I stop the code and view the string it is trying to execute I get this:
c:\Program Files\7-Zip\7z.exe e "C:\Dropbox\Apps\plesk-backup\zipfile.tar" -o"C:\CCL\MGH Backup" "C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\admin_MGH_2\"

This seems wrong to me. Can you help further please?
0
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
If your tar contains one file then the problem is the F...you have set it wrong...the F is the name you want the file to have on the output folder
If your tar has multiple files the keep only the part up to
ODir & Chr(34) 

Open in new window

0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Yes, F is file name in archive. I've changed sequence of last two parametrs. Try this:
Public Sub un7zip(ITarL As String, ITarF As String, F As String, ODir As String )
I don't see file name with extension in your result. Can you upload small sample (with real file names, but with dummy content)?
0
 

Author Comment

by:rick_danger
Comment Utility
These are my parameters:
ITarL = "C:\Dropbox\Apps\plesk-backup\"
ITarF = "zipfile.tar"
ODir = "C:\CCL\MGH Backup"
F = "C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\admin_MGH_2\"
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
F = "C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\admin_MGH_2\"
In your question was:
The name of the database file within the zip file
F should be file name inside your archive. Is it also a folder?

Can you open your archive in 7-zip GUI and show screenshot?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:rick_danger
Comment Utility
You are of course correct, but I thought it would need the path of the file, otherwise I wasn't sure how to go down to the correct level in the folder.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
7-zip GUI should show correct file name. Do you have folders inside your archive?
0
 

Author Comment

by:rick_danger
Comment Utility
Yes.
The folders are like this:
C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\

Then there are several folders beneath this level. The folder I am interested in is admin_MGH_2, which has the zip file: backup_dbdump_1610160701.zip

Sorry but I think I have mislead you a little. Can you still help?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
You should add "-r" to the end on unzip command:
Public Sub un7zip(ITarL As String, ITarF As String, F As String, ODir As String)
'ITarL - location of archive, ITarF - archive name, ODir - output folder, F - File name
Dim cmd As String, retval As Double
cmd = "c:\Program Files\7-Zip\7z.exe e " & Chr(34) & ITarL & ITarF & Chr(34) & " -o" & Chr(34) & ODir & Chr(34) & " " & Chr(34) & F & Chr(34) & " -r"
retval = Shell(cmd, vbNormalFocus)
End Sub

Open in new window

and use following parameters:
ITarL = "C:\Dropbox\Apps\plesk-backup\"
ITarF = "zipfile.tar"
F = "backup_dbdump_1610160701.zip"
ODir = "C:\CCL\MGH Backup"
0
 

Author Comment

by:rick_danger
Comment Utility
Great - I have it working now.

Just one problem. After extracting the file, it asks
"Would you like to replace the existing file" whether it is there or not. So even the first time I run it, it asks the question.

I have the options of
"(Y)es / (N)o / (A)lways / (S)kip all / A(u)to rename all / (Q)uit?"

I answer A and it all works, but is there a way of suppressing this?
0
 

Author Closing Comment

by:rick_danger
Comment Utility
I got around that last problem by using the "-y " parameter at the end of the string. According to the documentation, it answers "Yes" to all questions.

Thanks for this solution, it works really well!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now