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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"shazzie" <(E-Mail Removed)> wrote in message
news129CBBE-E1AC-4399-81AD-(E-Mail Removed)...
>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.

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



On Jun 27, 6:22*am, shazzie <shaz...@discussions.microsoft.com> wrote:
> I have a really small database - two small tables, two queries, two formsand
> 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. *

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"shazzie" wrote:

> 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.

> 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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Jerry Whittle" <(E-Mail Removed)> wrote in message
news:611C9231-7867-4EB5-948C-(E-Mail Removed)...
> 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.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> "shazzie" wrote:
>
>> 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.

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

"Sorry about that, Chief."
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Sylvain Lafontaine" wrote:

> > 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.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Jerry Whittle" <(E-Mail Removed)> wrote in message
> news:611C9231-7867-4EB5-948C-(E-Mail Removed)...
> > 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.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> > "shazzie" wrote:
> >
> >> 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.
>
>
>

Jerry Whittle wrote:
> 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 Removed)

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




On Jun 27, 12:12*pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:
> Jerry Whittle wrote:
> > 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
> MPAPos...@FortuneJames.com

(E-Mail Removed) wrote:
> 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
>
>
>
>
> On Jun 27, 12:12 pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
> wrote:
>
>>Jerry Whittle wrote:
>>
>>>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
>>MPAPos...@FortuneJames.com
>
>

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 Removed)

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




On Jun 28, 11:43*pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:
> aaron_ke...@hotmail.com wrote:
> > 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
>
> > On Jun 27, 12:12 pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
> > wrote:
>
> >>Jerry Whittle wrote:
>
> >>>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
> >>MPAPos...@FortuneJames.com
>
> 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
> MPAPos...@FortuneJames.com