Huge DB, shouldn't be that big, doesn't compact

  • Thread starter Thread starter vavroom
  • Start date Start date
V

vavroom

Hello again,

I have a db that shouldn't be big, but weights in at 18MB just now. It
was big before I started work on it, did a compact and repair, and came
down to 2MB. I basically redrew the thing, and deleted the old tables,
queries, and forms, looked at DB size, and it was 18MB again. So I did
another compact/repair, only this time, it's staying at 18MB, which
really shouldn't be that big.

Any ideas what might be stopping the file reduction?
 
Arvin, I just tried that when I came to see your response. Still porky
file. :(

I don't get it. It's not like I have that many records, nor even
code...
 
Hum, I have a application with about 160 forms, and 27,000 lines of code.
The whole size after a compact is only 12 megs. As a mde it is only 6.8 megs
in size.

Perhaps you starting using some picture backgrounds for forms...they are not
compressed...and they can bloat things in a hurry....
 
Albert said:
Hum, I have a application with about 160 forms, and 27,000 lines of code.
The whole size after a compact is only 12 megs. As a mde it is only 6.8 megs
in size.
Click to expand...

I know!!! I've built much bigger applications that never reached that
size even after 50K records...
Perhaps you starting using some picture backgrounds for forms...they are not
compressed...and they can bloat things in a hurry....
Click to expand...

No, I've not used one single image in forms or anywhere.
 
Is it possible that a primary key in a table that contains 16
characters would bloat the db size?
 
Is it possible that a primary key in a table that contains 16
characters would bloat the db size?
Click to expand...

Only by approximately 32 characters times the number of records in the
table. Not a huge amount (32MByte if you have a million records)...

John W. Vinson[MVP]
 
Ahh, sorry for not following up sooner, I worked on a different, non
Access based project this weekend (shameless plug: http://caughtya.org
)
But have you tried importing into a new, clean, empty database? That should
cure the problem.
Click to expand...

Yes, I did. And it didn't :(

Hmmm.
 
If you imported everything into a new, empty database, and it didn't reduce
the siz, the only other possibility that I can think og is that there ate 1
or more images embedded in the project. Images can eat up incredible amounts
of space in a database.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
If you imported everything into a new, empty database, and it didn't reduce
the siz, the only other possibility that I can think og is that there ate 1
or more images embedded in the project. Images can eat up incredible amounts
of space in a database.
Click to expand...

I'm aware of how much size images eat, and I shy away from them because
of that :(

So, here's what I've done.
1- Split the database
2- Imported tables, one at a time, checking for DB size.
3- Following this, I isolated the "culprit" to one table.

This is the structure of this table:
fldConcatID - Text - Primary key - Indexed, no dups - 17
fldLName - Text - not indexed - 25
fldFName - Text - not indexed - 25
fldInitials - Text - not indexed - 5
fldEmail - Text - not indexed - 50
fldDept - Text - not indexed - 50
fldPhone - Text - not indexed - 10
fldCamp - Text - not indexed - 50
fldCol - Number - not indexed - double

The table contains 23K records, give or take. Alone in a DB, the DB
weighs in at 3.64Mb Seems too much, doesn't it?
 
Actually, that table could be as large as 6,578,000 bytes. If you figure
that each field carried the maximum number of characters and that an index
an a text field can double it's size, and that Unicode doubles the bytes of
each character in a text field, it would be that large.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin said:
Actually, that table could be as large as 6,578,000 bytes. If you figure
that each field carried the maximum number of characters and that an index
an a text field can double it's size, and that Unicode doubles the bytes of
each character in a text field, it would be that large.
Click to expand...

All right, solved then. Don't particularly like it, but... not much
of a choice.

Thanks for helping me problem solve this Arvin :)
 
MDB is for flaming wimps

use Access Data Projects and you don't have unmanageble limits like
this

for starters-- you have a SERVER so you have a MUCH better query
engine-- well-- you HAVE a query engine

and you don't have a 2gb limit with SQL 2005 Express
 
The table contains 23K records, give or take. Alone in a DB, the DB
weighs in at 3.64Mb Seems too much, doesn't it?
Click to expand...

Well, just adding up the sizes of the text fields, you get about 240
characters per record.

240 X 23,000 = 5,336,000

So, that is 5 megs. Considering that all fields are not full, and you are in
at the mid 3 meg range..that sounds reasonable to me....

You original database of 18 megs sounded too large, but 3.5 megs for 23,000
records sounds just fine...
 
and with SQL Server it is easy to determine how LARGE each table is.

so you don't need to do all this crap; you just run a query

-Aaron
 
and with SQL Server it is easy to determine how LARGE each table is.
Click to expand...

Yes, but that's assuming that one has access to such a server, in each
of the location that needs to run the database... <shrug>
 
Sql Server is free-- for the express edition or for MSDE.
Either way-- if you care enough to build a database; don't use a CRAP
engine like MDB. MDB is a database without an engine; a car without an
engine; a boat without a paddle.



SQL Server has been free-- in one form or the other---- for the past 6
years.

Oracle and IBM finally realized this about a year ago.

SQL Server Express is a BADASS solution.
and the development tools for 2000 or 2005 cost $49. Toad for Oracle--
it costs what; 2 grand??

-Aaron
 
Sql Server is free-- for the express edition or for MSDE.
Click to expand...

Have you ever worked in a corporate environment where you don't get to
decide what applications are run, what servers you have access to, and
you have to work with the tools provided you? Where if you request a
new tool, you *might* get a decision from the powers that be, in 3, 4
or 6 months down the road?

Sometimes, it's not a question of price.

Had it been my choice, I'd have done a web based php/mysql application
where every location could just log in and do things directly in. It
wasnt' my choice. MS Access is the only database software we have
access to. Period.

And no, I'm not gonna quit because Access is crap ;)
 
have you ever listened to RAGE AGAINST THE MACHINE?

don't put up with their slow ass

install MSDE on your desktop-- it's a free component that is included
on the OFFICE DISC.

Call your little tech support wuss down and ask for a component from
the office disk.

-Aaron
 
Back
Top