what makes a database really big?

[shazzie]

I have a really small database - two small tables, two queries, two forms and
two reports. It is over 8mb in size. The first table contains 9 respondents
and the second table contains 8 questions (there will never be more than 8
questions). It was 19mb in size!!! and I have now compacted and repaired it
- why is it still so big?? I created another database with massive amounts
of information, many tables, 25 queries and about 15 reports and it is only 4
mb in size.

 

[Allen Browne]

Embedded graphical elements would be the first thing to look for. Have you
set the Picture property of any forms/reports? Or added an Image control? Or
do you have OLE fields with embedded objects?

As you modify the database objects (in design view), Access makes copies of
them. This space is not automatically released, until you compact.
Similarly, modifying the code can lead to spurious binary that doesn't get
released. A decompile will solve that.

After a compact, decompile, compact sequence, you could create a new (blank)
database and import everything. Typically this new database will be smaller
than the one you just compacted.

For suggestions on basic database maintenance and recovery techniques, see:
http://allenbrowne.com/recover.html

 

[a a r o n . k e m p f]

bloat makes it big.

Access is inherently a bloated database. If you're having size
problems, then move to SQL Server.

are you using the TEXT datatype?
how many _RECORDS_ do you have?

-Aaron

 

[Jerry Whittle]

Too add to what Mr. Browne said, it's possible that complicated queries,
forms, and reports can add to size. For example a query that does a lot of
sorting and grouping can add size as Access needs some elbow room to do the
sorts. Same goes for Union queries and crosstabs. If these queries drive
forms and reports, the same goes. Actually reports may themselves need some
extra work space.

If you do a compact and repair, then run the database only to see it balloon
back up, chances are good that it needs this extra internal space to run.

Actually 19 mb is a drop in the bucket for Access. It could be 100,000 times
larger and still run. I wouldn't worry about it.

 

[Sylvain Lafontaine]

Actually 19 mb is a drop in the bucket for Access. It could be 100,000

times
larger and still run. I wouldn't worry about it.

Not sure about this number: 19mb multiplied by 100.000 times give a size of
1.9TB (Terabyte, also without taking into account the difference between the
decimal and binary values); obviously above the size limit of 2GB for a mdb
file.

 

[Jerry Whittle]

In my best Maxwell Smart impression: "Would you believe 100 times?"

"Sorry about that, Chief."

 

[James A. Fortune]

In my best Maxwell Smart impression: "Would you believe 100 times?"

"Sorry about that, Chief."

LOL

"That's it, 99! TBO -- The Big One!"

That is, at least as far as Access is concerned .

James A. Fortune
(e-mail address removed)

 

[aaron_kempf]

technically, he has questions in one table.. and respondents in
another.. I'm under the assumption that ANSWERS don't go in the
questions table.

So he's stuck with a ONE gb limit; not a 2gb limit; right?

-Aaron

 

[James A. Fortune]

technically, he has questions in one table.. and respondents in
another.. I'm under the assumption that ANSWERS don't go in the
questions table.

So he's stuck with a ONE gb limit; not a 2gb limit; right?

-Aaron

I've never really tried to hit the size limit in Access so I can't speak
on the matter based on experience. Maybe I would want to try that if I
want to run a Sieve of Eratosthenes to get a bunch of primes using an ID
and a Y/N field. Access can link to multiple Access databases anyway so
the limits are more operational than physical. Anyone actually getting
anywhere near the physical limit of an mdb has probably gone way beyond
the practical limit without even linking to more mdb's. Yes, they're
probably being overrun by chaos . Besides, getting too literal about
the actual limit gets in the way of the humor. Plus, he didn't state
that it is 100 times (resulting in about 1.9 GB); he only asked if you
would believe 100 times . In the original series Maxwell would
sometimes go through several iterations before arriving at something
close to the (lame) truth. Moving to unicode also affected things.

James A. Fortune
(e-mail address removed)

 

[a a r o n _ k e m p f]

moving to unicode didn't effect anything.

In a real mans database-- SQL Server-- you can specify whether you
want varchar or nvarcar (unicode).
It's all about using the best datatypes for the job.

Access won't let you use optimal datatypes.
SQL Server does.

-Aaron

 

[James A. Fortune]

moving to unicode didn't effect anything.

It affected how many characters you can fit into an Access database for one.

In a real mans database-- SQL Server-- you can specify whether you
want varchar or nvarcar (unicode).
It's all about using the best datatypes for the job.

Access won't let you use optimal datatypes.
SQL Server does.

Those points were valid, but not interesting nor funny. I think that a
lot of Access programmers will migrate to SQL Server in some measure in
time but I don't think that your arguments will do much to change how or
when that happens. It's almost like you're being paid to advertise SQL
Server but the one paying you only cares that the name keep coming up
over and over, not that cogent reasons for switching are offered. When
it's really time to switch, they want us to think "SQL Server" before
PostgreSQL or MySQL or anything else. I think that SQL Server Express
was a great marketing idea, but I will look at all the options instead
of just a default option planted in my brain by marketers. Of all
companies, Microsoft knows best the value of being a default option .
But I also admit that the availability of helpful newsgroups, tools
providing for ease of migration and the ubiquity of the software on
internet servers will be important factors in my decisions.

James A. Fortune
(e-mail address removed)

 

[a a r o n _ k e m p f]

SQL Server is the only platform that works _OPTIMALLY_ with MS Access

MS Access is a great product.
But the DATABASE?

Access sucks.

And mySQL ? it's great-- for poor people.
I just dont' think that there's a point to anything other than SQL
Server.

Maybe if you were 'good enough' with SQL Server-- then maybe you
wouldn't have a problem getting them to spend _NOTHING_ on a database
license.

-Aaron

 

[a a r o n _ k e m p f]

and for the record? SQL Server Express-- it's a little bit more than
an idea.
SQL Server Express is the child of MSDE 2.0 which is the child of MSDE
1.0.

SQL Server has been _FREE_ for a decade-- and infinitely more scalable
than MS Access.
Free and more scalable.

Why double-think it?


-Aaron

 

[James A. Fortune]

And mySQL ? it's great-- for poor people.

That's marketing jargon. Have you ever used mySQL?

I just dont' think that there's a point to anything other than SQL
Server.

As incredible as it may seem, I'm not convinced that you really like SQL
Server.

Maybe if you were 'good enough' with SQL Server-- then maybe you
wouldn't have a problem getting them to spend _NOTHING_ on a database
license.

I've used SQL Server. It worked so well on an ASP application I did
that I didn't even need to dig into SQL Server optimization -- and I
just used simple Access techniques. Don't mistake taking time
converting to SQL Server as a dislike of SQL Server.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

and for the record? SQL Server Express-- it's a little bit more than
an idea.
SQL Server Express is the child of MSDE 2.0 which is the child of MSDE
1.0.

SQL Server has been _FREE_ for a decade-- and infinitely more scalable
than MS Access.
Free and more scalable.

Why double-think it?

Why double-market it?

PostgreSQL is also free and more scalable than Access. Plus, it's been
free longer. Evaluation is not double thinking.

I suppose drug dealers give free samples also .

Let the products stand or fall on their own merits. Marketers turn me
off from their products.

James A. Fortune
(e-mail address removed)

Any stock that is being advertised has no fundamental support. It may
rise momentarily, but when I see a stock advertised it's a sign for me
to stay away. -- Robert Trapp