Access Size Help No Info Large Size

[scott]

I have an access data base that I have made. It has NO information or data in
it. I have three small bitmap pictures in it and the rest is just empty
cells. I have used this data base for a couple of years and it has grown to
18 mb. I had some pc problems and after getting them fixed and this data base
fixed I ran compact and repair and it dropped down to around 1.5 megs(i did
not lose any data) which seemed more realalistic. Since this happened I made
another copy of this data base with all my data in, I then wiped out all the
data(I did this to have just a back up of the data base form-like a template)
and it is 1.4 megs. I ran a compact and repair and it is still 1.4 megs. I
have a hard time believing it is this large when an 18 meg file went down to
1.5 and a data base with no data is 1.4. Am I doing something wrong? To me it
should be small enough to save on a floppy with out zipping it down.
Thanks in advance
Scott

 

['69 Camaro]

Hi, Scott.

I have an access data base that I have made. It has NO information or data in
it. I have three small bitmap pictures in it

Bitmaps are data.

Since this happened I made
another copy of this data base with all my data in, I then wiped out all the
data(I did this to have just a back up of the data base form-like a template)
and it is 1.4 megs.

There are several situations that make a database file larger than it
seemingly should be. For example, whenever a column is dropped from a table,
unless you had the foresight to run an UPDATE query to set all values to NULL
in that column, the data in those columns remains in the table, but is
inaccessible from Jet. Various versions of the source code (compiled,
uncompiled, previous changes to uncompiled, et cetera) remain in the file
until the file is opened with the /decompile switch, which discards all of
the versions of the source code other than what you see when you open the
modules. In Jet 4.0 databases, whenever objects are created, entries are
also made in the MSysAccessObjects table. When these objects are deleted,
entries still remain in the MSysAccessObjects table. Except for the case of
the /decompile situation, a compact/repair operation can't reduce the file
size.

The best way to get rid of any extraneous data when creating a new template
database is to create a new database file and import all of the objects, sans
any data in the tables. If the new file is more than 1.44 MB, then you'll
need to zip it before putting it on a floppy, or you'll need to span the file
so that it fits on two floppies.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Tom Wickerath]

Hi Scott,

To add to Gunny's answer, I once worked on a database that went from nearly
200 MB down to under 300 KB simply by running a compact and repair. Along
with making the file smaller, another benefit is that any queries will be
decompiled. The next time the queries are run, the most efficient
optimization plan will be determined. More information here:

http://support.microsoft.com/?id=303528#E0YE0ADAAA

Also, if you are using the newer 2002-2003 file format, you may want to
reconsider using the 2000 file format:

Database bloat is not stopped by compacting database with Access 2002 format
http://support.microsoft.com/?id=810415


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Dirk Goldgar]

"'69 Camaro" <[email protected]_SPAM>
wrote in message

For example, whenever a column is dropped from
a table, unless you had the foresight to run an UPDATE query to set
all values to NULL in that column, the data in those columns remains
in the table, but is inaccessible from Jet.

I didn't know that, Gunny. Is this true if a column is deleted via the
user interface (Table Design View), as well as by executing an "ALTER
TABLE DROP COLUMN" SQL statement? Or just one, or just the other?

 

['69 Camaro]

Hi, Dirk.

Is this true if a column is deleted via the
user interface (Table Design View), as well as by executing an "ALTER
TABLE DROP COLUMN" SQL statement? Or just one, or just the other?

It's true for both, because it's the result of how Jet stores the data
within tables. Deleted columns remain defined in the table's definition,
even though the column was dropped. This is why Jet's MaxColumns Property
(my terminology for it because it's proprietary to Microsoft and they don't
offer any documentation to the public) never decrements when columns are
deleted in the table, and why one may encounter an error when attempting to
add another column because the 255 column maximum has already been reached,
even though there aren't currently that many columns showing in the Design
View of the table.

As for new records, the new records receive NULL values in the deleted
columns.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[gee664]

So would it be better to just create an new table with the columns you
actually need and then delete the old table?

 

['69 Camaro]

Hi.

So would it be better to just create an new table with the columns you
actually need

If you wanted to do that manually or with SQL queries, you could. You have
to be careful on how you create that new table. If you just make a copy of
the table within the GUI, then you'll have the identical structure, hidden
deleted columns and all. Or you could create a new database and import all
objects from the old database into the new one, and it would get rid of all
of the extraneous stuff in the database file, not just the deleted columns
of one table.

I don't recall off of the top of my head whether I verified that making a
copy of a table sans the data would recreate the table structure without the
deleted columns, but in any case there wouldn't be any rows with data in
those deleted columns if the deleted columns were still defined in the copy
of the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.