Very Computer

In another thread I asked for suggestions for reducing an ADP's file size.
In it's compressed state my ADP has about 35MB and the ADE has about 12MB. A
suggestion was to remove the graphics and load them dynamically. As I did
not use these very much, the saving was not too big.

Now I had another idea, and the result was impressive. I created a new ADP,
set the database connection and imported all objects (forms, reports,
macros, modules). After that the ADP has a size of 6MB and the ADE has a
size of 5 MB. I tested the application and anything seems to be ok.

Does anybody know, how these savings can be explained? What made up the 29MB
of ADP storage? Can anybody reproduce this? Is this behaviour a well known
fact about ADPs, is it documented somewhere?

Michael G. Schneider

ridiculous bugs
same nonsense happens with mdb's

Quote:> In another thread I asked for suggestions for reducing an ADP's file size.
> In it's compressed state my ADP has about 35MB and the ADE has about 12MB.
A
> suggestion was to remove the graphics and load them dynamically. As I did
> not use these very much, the saving was not too big.

> Now I had another idea, and the result was impressive. I created a new
ADP,
> set the database connection and imported all objects (forms, reports,
> macros, modules). After that the ADP has a size of 6MB and the ADE has a
> size of 5 MB. I tested the application and anything seems to be ok.

> Does anybody know, how these savings can be explained? What made up the
29MB
> of ADP storage? Can anybody reproduce this? Is this behaviour a well known
> fact about ADPs, is it documented somewhere?

> Michael G. Schneider

Quote:> ridiculous bugs
> same nonsense happens with mdb's

Any KB about this?

Michael G. Schneider

Thanks for the tip: my ADP goes from 17.3MB to 8.28MB! The ADE size is
unchanged. After a few test ot appears to do the same as using the
/Decompile switch. Be carefull with this switch, see Michka explanations:

http://www.trigeminal.com/usenet/usenet004.asp?1033

and about compact:

http://www.trigeminal.com/usenet/usenet023.asp?1033

--
HTH

Michel Jasmin

Quote:> In another thread I asked for suggestions for reducing an ADP's file size.
> In it's compressed state my ADP has about 35MB and the ADE has about 12MB.
A
> suggestion was to remove the graphics and load them dynamically. As I did
> not use these very much, the saving was not too big.

> Now I had another idea, and the result was impressive. I created a new
ADP,
> set the database connection and imported all objects (forms, reports,
> macros, modules). After that the ADP has a size of 6MB and the ADE has a
> size of 5 MB. I tested the application and anything seems to be ok.

> Does anybody know, how these savings can be explained? What made up the
29MB
> of ADP storage? Can anybody reproduce this? Is this behaviour a well known
> fact about ADPs, is it documented somewhere?

> Michael G. Schneider

Not bugs at all, just a lot of useless junk that doesn't always get deleted
when compacting a database. For the smallest, cleanest file, first
defragment your hard-drive (to get rid of the useless junk lying around in
it) then create a new, empty, database and import the objects into it. The
ADE created from this will be still a bit smaller since it removes all the
spaces and comments from your code and tokenizes it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com

Quote:> Not bugs at all, just a lot of useless junk that doesn't always get
deleted
> when compacting a database. For the smallest, cleanest file, first
> defragment your hard-drive (to get rid of the useless junk lying around in
> it) then create a new, empty, database and import the objects into it. The
> ADE created from this will be still a bit smaller since it removes all the
> spaces and comments from your code and tokenizes it.
> --
> Arvin Meyer, MCP, MVP
> Microsoft Access
> Free Access downloads:
> http://www.datastrat.com

If a tool offers a "compact database", it should actually compact the
database. If in a 35MB file there 29MB of "useless junk", this is not ok.

I do not have the slightest idea, how defragmenting the hard-drive might
have an influence on the file size. Defragmenting re-orders a file's parts,
but it does not delete any part of a file. Doesn't it?

Michael G. Schneider

I have to agreed with Mr Schneider's comments
Also, I have never had any of my file systems store 6MB of data using 35MB
of disk space.
If it did, I would certainly say there was a bug in the file system

Quote:

> > If a tool offers a "compact database", it should actually compact the
> > database. If in a 35MB file there 29MB of "useless junk", this is not
ok.

> > I do not have the slightest idea, how defragmenting the hard-drive might
> > have an influence on the file size. Defragmenting re-orders a file's
> parts,
> > but it does not delete any part of a file. Doesn't it?

> > Michael G. Schneider

> I have to agreed with Mr Schneider's comments
> Also, I have never had any of my file systems store 6MB of data using 35MB
> of disk space.
> If it did, I would certainly say there was a bug in the file system

amend above statement to "store single 6MB file data using 35MB disk space".
since minimum allocation is a disk sector, a one byte file could take up a
whole sector.

Quote:

> > > If a tool offers a "compact database", it should actually compact the
> > > database. If in a 35MB file there 29MB of "useless junk", this is not
> ok.

> > > I do not have the slightest idea, how defragmenting the hard-drive
might
> > > have an influence on the file size. Defragmenting re-orders a file's
> > parts,
> > > but it does not delete any part of a file. Doesn't it?

> > > Michael G. Schneider

> > I have to agreed with Mr Schneider's comments
> > Also, I have never had any of my file systems store 6MB of data using
35MB
> > of disk space.
> > If it did, I would certainly say there was a bug in the file system

> amend above statement to "store single 6MB file data using 35MB disk
space".
> since minimum allocation is a disk sector, a one byte file could take up a
> whole sector.

This 35MB vs. 6MB thing has absolutely nothing to do with disk fragmentation
or with sector arrangement on a hard drive. When the ADP file was in it's
35MB state, each and every byte of those 35MB was valid. Not a single byte
could be deleted on filesystem level.

If the 35MB file were copied to a new harddisk with perfect sector
arrangement, the file would still be 35MB in size.

It is a problem of MS Access "compress database" that those 29MB were not
freed.

Michael G. Schneider

Hi Michael,

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

As I understand your question, you want to know why importing all the
objects from you ADP to a new ADP caused the size to decrease by a
significant amount.

From just the comments you posted, it isn't clear if the original ADP at 35
MB had been compacted, unless this is what you are referring to by
"compressed." Usually, once a file has been compacted it is about as small
as it can get without using some kind of compression software such as
WinZip.

When we import all objects into a new ADP container, we are effectively
compacting the database file.

Any time we delete an object in a database file, the object is not actually
removed from the file until the database is compacted. You did indicate
that you had some images, perhaps embedded in forms or reports? If you were
to delete the forms or reports that contained these images and recreated
them without the images, the space for both the old and new objects is
still being taken up until the file is compacted.

For example, I just took a new blank database file with a blank form and
embedded a 10 KB image. The database file jumped to 465 KB. After deleting
the form, the database file was slightly larger. After compacting the
database, the file size was back to about 100 KB. Depending upon the type
of image file and how and where it is inserted into the database, the
database can grow by as much as 5 MB per image which will remain even after
the object is deleted and until the compact is done.

Here is a list of the benefits of running CompactDatabase:

Note: It's highly recommended that databases are compacted often, daily if
possible.

- Reclaims disk space created by object and record deletions. When objects
or records are deleted from the database, the space they occupied is marked
as available for new additions to the database. However, the size of the
database never shrinks unless the database is compacted.

- Reorganizes a table's pages so they reside in adjacent database pages.
This improves performance because the table data is sequential, allowing
for better use of the read ahead cache.

- Resets incrementing AutoNumber fields so the next value allocated will be
one more than the highest value in the remaining records.

- Regenerates the table statistics for the indices, which the Query
optimizer depends heavily on these for optimal query plans. These
statistics can become out-of-date over time, typically if transactions were
rolled back, or if the database was abnormally terminated.

- Flags all queries so that they will be recompiled the next time the query
is executed. This is important because database statistics can change and a
previously compiled query may have an inaccurate query plan. For example a
database may greatly increase or decrease in size.

In addition to compacting the database on a regular basis, the most current
update of JET 4.0 contains modifications to help reduce database size and
is available for download by referencing article:

Q282010 - ACC2002: Updated Version of Microsoft Jet 4.0 Available

    http://support.microsoft.com/support/kb/articles/q282/0/10.asp

Q239114 - ACC2000: Updated Version of Microsoft Jet 4.0 Available

    http://support.microsoft.com/support/kb/articles/q239/1/14.asp

If you compact the database, then import all objects into a new ADP
container, I would not expect to see much of a difference in the file size.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Regards, Frank Miller
Microsoft Support

This posting is provided AS IS with no warranties, and confers no rights.

Peer-to-peer newsgroups are available to help you interact with other users
of our products including Microsoft " Most Valuable Professionals" - MVP's.
For more information please see
http://communities.microsoft.com/newsgroups/default.asp?icp=GSS&slcid=us

It does compact the database. Sometimes it doesn't get it all. Often deleted
objects are corrupted, and the compact process just passes them over. There
are always bit which are not cleaned. You may call it a bug, and it would be
hard to disagree with you, but the fact remains that importing into a new
file will make the smallest possible files size.

Quote:> I do not have the slightest idea, how defragmenting the hard-drive
> might have an influence on the file size. Defragmenting re-orders a
> file's parts, but it does not delete any part of a file. Doesn't it?

When you delete files on a hard drive, they are not deleted. Actually, the
pointer to the file is removed. A DOS editor can still see much of the data
that's there. Eventually it will be over-written making it even harder to
see. (There are experts who can recover files that have been over-written).
An MDB grabs 2K pages for the data. Records from the same table are stored
on each page until the 2K limit is reached. If a record starts a new page in
an area which has "deleted" fragments from and old file, that file can fill
the space until the database is compacted. Defragmenting a disk usually (not
always) presents a clean segment of the disk to new files. Therefore the old
data isn't there to "pad" the remaining space in the record.

Several years ago, I was looking at a database in a DOS editor (NOT
recommended practice as you can easily corrupt a database) and I found an
old deleted email from my brother with my mother's funeral arrangement in
it. Needless to say it wasn't a pleasant experience. Upon compacting the
file was "cleaned".

Actually, there is a way to approximate importing the objects into a new
empty database. It requires using the undocumented /decompile switch, then
compacting the database. It is undocumented because it is possible to lose
data, or even corrupt a database, so if you use it, ALWAYS WORK ON A COPY.
It is used like this:

"C:\Program Files\Microsoft Office\Office10\msaccess.exe" /decompile
"C:\Projects\Path To the Database.mdb"

Obviously, you need to adjust it to your paths.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com

Quote:> As I understand your question, you want to know why importing all the
> objects from you ADP to a new ADP caused the size to decrease by a
> significant amount.

> From just the comments you posted, it isn't clear if the original ADP at
35
> MB had been compacted, unless this is what you are referring to by
> "compressed." Usually, once a file has been compacted it is about as small
> as it can get without using some kind of compression software such as
> WinZip.

Hi Frank,

thank's a lot for the answer. As I do not speak English natively, and as I
do not use an English version of MS Access, I probably mixed up the words
"compress" and "compact".

Yes, I did compact the ADP file. "Compact" being a MS Access internal
command on the menubar, maybe labeled "Tools ! database utilities ! compact
database and repair" (translated from German).

After compaction the file size was 35MB. After importing all objects into a
new ADP the file size was 6MB.

It would be good, if the compact-command were able to achieve such a
compaction also. After all, the compact-command leaves 29MB of unnecessary
contents untouched.

Michael G. Schneider

Quote:> > I do not have the slightest idea, how defragmenting the hard-drive
> > might have an influence on the file size. Defragmenting re-orders a
> > file's parts, but it does not delete any part of a file. Doesn't it?

> When you delete files on a hard drive, they are not deleted. Actually, the
> pointer to the file is removed. A DOS editor can still see much of the
data
> that's there. Eventually it will be over-written making it even harder to
> see. (There are experts who can recover files that have been
over-written).
> ... [snipped] ...

I do not quite know, whether you agree with me on "defragmentation does not
alter a file's size". So here are some more remarks.

If a create a new file composed of the three letters ABC, the file size is
3. This means, if I open the file and read through it's contents, there will
be three bytes.

This does not say anything about how many bytes are used on the hard disk.
Maybe the file is stored entirely inside the file directory (the MFT), so it
doesn't take up any space at all. Or it is saved in one allocation unit,
taking up some kilobytes. These numbers do not count for our discussion. We
are only interested in the actual file size, as e.g. reported by the DIR
command.

This file size is a persistent property of the file. It does not change when
copying the file from one location to another. And it also does not change,
if a defragmentation is done. If it would change on a defragmentation, this
would be very bad - as reading the file before and after the defragmentation
would give different results.

So performing a defragmentation will increase the performance for accessing
the hard disk. But it will not have any impact on the file size of an ADP.

Defragmentation is also rather useless for ADP comnpaction, as
defragmentation only influences one specific hard disk. If I defragment my
hard disk, and the ADP file is then layed out optimally, copying it to the
user's hard disk will probably create a messed-up file layout again.

Michael G. Schneider

Hi Michael,

It has not been my experience that there would be a difference,
particularly not such a large difference, between a compacted and imported
database unless, as Arvin points out, the database contains damaged
objects. Frequently, the damaged objects are also deleted objects so we
don't see any problems when we use the file.

Often, we will use the import process when we suspect a damaged database.
In some cases, the import process will identify a damaged object, but more
frequently, it will just leave the damaged object behind (particularly if
the damaged object is also a deleted object) and only import valid objects.
Occasionally, it will import a damaged object (depending upon the type of
damage) without identifying any problem.

The import process is particularly useful when trying to convert a database
file that was not compacted or compiled prior to conversion and won't
convert properly. The JET engine can read the older file formats and can,
by importing the objects, produce a valid conversion when the normal
conversion process trips over damaged objects in the database.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Regards, Frank Miller
Microsoft Support

This posting is provided AS IS with no warranties, and confers no rights.

Peer-to-peer newsgroups are available to help you interact with other users
of our products including Microsoft " Most Valuable Professionals" - MVP's.
For more information please see
http://communities.microsoft.com/newsgroups/default.asp?icp=GSS&slcid=us

Quote:> It has not been my experience that there would be a difference,
> particularly not such a large difference, between a compacted and imported
> database unless, as Arvin points out, the database contains damaged
> objects. Frequently, the damaged objects are also deleted objects so we
> don't see any problems when we use the file.

Are you interested in finding out, why there is a huge difference - 35MB vs
6MB - in this case? If so, are there any utilities for reporting about the
ADP's internal structure? Can we find out anything about this ADP, whithout
sending the ADP to you?

To my knowledge there were no damaged objects inside the ADP. I have never
encountered any problems, and I have regularly done the compaction.

Michael G. Schneider