Forum Discussion

RBStewart's avatar
RBStewart
Copper Contributor
Mar 14, 2022

Problem Creating a usable zip file using VBA. Does anyone have an alternative?

I am hitting a problem creating a usable zip file using VBA.  I am using the following code block to create a zip file. I found it in virtually all internet posts that I came across and for that, I ...
access
App
developer
office 365
RBStewart's avatar
RBStewart
Copper Contributor

Thank you  karenlorr_uk 

While my needs are different, the code I have (with variations to fit my needs) is roughly the same as yours. However, my problem is in the zip file that gets created when the value of 'ZipFile' in the code block below is a path onto a flash drive or MicroSD card. 

 

    Open ZipFile For Output As #FileNumber
    Print #FileNumber, Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, vbNullChar)
    Close #FileNumber

The created zip file has some sort of access gate that requires an unknown, non-existent disk. This happens on virtually every flash drive and MicroSD on which I have tried to create such a zip file.

 

My vb code workaround is simply to copy a pre-existing, empty zip file that was created on an internal hard drive onto the portable storage device. Such a copy works fine. I can access it, send files to it extract files from it as needed, and without the pesky gate calling for a non-existent disk.

 

Thank you for taking the time to offer this suggestion.

karenlorr_uk's avatar
karenlorr_uk
Copper Contributor
Mar 17, 2022
You're right - and I don't know why.
I have just created a small sample database with just 1 module and form and I get the same warning message as you (this was on a new USB - so n hidden folders) if the path is directed to a zip folder. But it works fine on a hard drive.

I get the same problem even after a full format of the USB.

So the VBA works OK and it's not an Access VBA problem.

Sorry I can't be of any more help with this. Maybe it would be a good idea to post in the Windows forum and see if there are any work-arounds. Of course you could just write the backup to your hard drive and then, in the same module, move the folder to the USB, but this starts to get messy and there really shouldn't be a need to do this.
  • isladogs's avatar
    isladogs
    MVP
    Mar 21, 2022

    karenlorr_uk 

    Two questions about your SendThe Backup function

    1. Why are you creating an empty folder with the same name as the zip file? 

    It seems to be unnecessary

    These 3 lines can be removed & the zip file is still created correctly

       If Dir(ZipPath, vbDirectory) = "" Then
        MkDir ZipPath
   End If

     

    2. The final line doesn't compile so I removed it

    ZipCurrentProject = Err.Number

    Does it have any purpose?

Resources