Solved

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

Posted on 2016-10-16
12
65 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 40

Expert Comment

by:als315
ID: 41845789
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
ID: 41845845
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 16

Expert Comment

by:John Tsioumpris
ID: 41845849
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 40

Expert Comment

by:als315
ID: 41845856
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
ID: 41845860
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 40

Expert Comment

by:als315
ID: 41845862
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
 

Author Comment

by:rick_danger
ID: 41845865
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 40

Expert Comment

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

Author Comment

by:rick_danger
ID: 41845877
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 40

Accepted Solution

by:
als315 earned 500 total points
ID: 41846130
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
ID: 41846322
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
ID: 41846430
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

726 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