Zipping a large Access BE database (1 Viewer)

isladogs · Feb 11, 2018

I have a very large Access datafile of UK postcodes which serves as one of the backend databases for my UK Postal Address Finder database which I'm about to release via my website (finally!!)

NOTE:
The installer file for the website includes this large file & several other much smaller files.
It compresses all files whilst creating a single EXE file
My first attempt in this case resulted in an EXE file of about 1.4GB
Removing the password, the EXE size dropped to 127MB

So I tested using Winzip

Approx file size before compression 1.262GB
After compression using WinZip, approx file sixe = 116.1MB (<10% of original)
All good so far

However, I then decided to password protect the datafile.
Negligible change in file size as you would expect
However, the zipped version became 1.2622GB - slightly bigger than the original file.
Using zipx format made little difference.
In other words ... useless for compression

Can anyone explain why 'encrypting' an Access DB with a password causes this effect.
@DocMan: Thanks in advance for your explanation.
My guess is that password protection prevents Winzip doing any compression & that the size increase is just the result of its file scan

JHB · Feb 11, 2018

Have you tried with another DB?

isladogs · Feb 11, 2018

Hi JHB

I'd just done that on a much smaller file when I got an email alert with your reply.
Same result. See hidden comment in first post

jdraw · Feb 11, 2018

I agree with your hidden message. Also could be partly related to the version of winzip.
see this(you probably have already)

isladogs · Feb 11, 2018

Hi Jack

I hadn't seen the link but it confirms my suspicions.
Having tried various compression configurations (zip,zipx, AES 128-bit, AES 256-bit, legacy 2.0 etc) , I found none worked with password protected databases.
So the monster db just has to be distributed without a password - not an issue in this instance as its publicly available data.

However it will have an impact on another database I'm doing next

Mark_ · Feb 11, 2018

Colin,

One of the down sides of using passwords to make data unreadable is that they prevent you from having repeated sets of characters. Most zip programs take advantage of repeated characters to "Compress" the file.

Easy way to think of it, if you have 67 duplicate RGB values in the same line because its part of a background image, the compression software can effectively say "67,RGB" to replace 201 bytes of data. If you password protect it so each RGB value is stored differently the compression software doesn't have the repeated characters.

This is a horribly simple way of phrasing it, but explains the concept of how the zip programs work.

JHB · Feb 12, 2018

ridders said:
..
Same result. See hidden comment in first post

I must confess, I don't know in this case, what you mean about "hidden comment"!

isladogs · Feb 12, 2018

JHB said:
I must confess, I don't know in this case, what you mean about "hidden comment"!

Its white text.
Using your mouse, select the area where there is a gap above the screenshot in post 1.

JHB · Feb 12, 2018

- got it, (I just got new reading glasses the other day, maybe they should have been stronger

)!

isladogs · Feb 12, 2018

My prediction of who would answer proved wrong though.

Mark
Thanks for your explanation. It does make sense. But despite what the message box says, is the database actually encrypted when password protected? I thought it just added one level of security rather than actually encrypting the entire file?

JHB · Feb 12, 2018

ridders said:
...I thought it just added one level of security rather than actually encrypting the entire file?

Security level is the case for a MDB database file, (and this can be zipped with reducing).

https://support.office.com/en-us/ar...password-12aa0e5c-34c6-4957-af3b-b5f5cfa9a766

isladogs · Feb 12, 2018

JHB said:
Security level is the case for a MDB database file, (and this can be zipped with reducing).

https://support.office.com/en-us/ar...password-12aa0e5c-34c6-4957-af3b-b5f5cfa9a766

Thanks for the link.
Password protecting a database is almost instantaneous (though slower to remove it). As its so fast, I wrongly assumed the 'encryption is not compatible with row level locking' message wasn't actually referring to encrypting the whole file.
Anyway, the file in question is no longer password protected and the EXE install file is about 10% of original file size

Mark / JHB
Thanks to you both for your help

Zipping a large Access BE database (1 Viewer)

isladogs

MVP / VIP

Attachments

JHB

Have been here a while

isladogs

MVP / VIP

Attachments

jdraw

Super Moderator

isladogs

MVP / VIP

Mark_

Longboard on the internet

JHB

Have been here a while

isladogs

MVP / VIP

JHB

Have been here a while

isladogs

MVP / VIP

JHB

Have been here a while

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread