database too big

[Greg Blum]

I have been having problems with accessing and making
changes to my database. I found that my datasource is
2,100,000 k (1.99G). I know that 2G is the limit. I have
reduced the size of my tables and that has helped, but I
still can get in trouble. Reducing my table sizes does not
affect the total size of the database.

When I try to compress and repair the database, the
function goes into a "Not Responding" mode in the task
manager.

Is there any way to reduce the size without the
compression function?

 

[Lester]

I don't know if you have your source and your data tables
in the same database. If you do then you can create a
second database for your data and export the data to the
new database then link those tables in the new database.

 

[Tony Toews]

When I try to compress and repair the database, the
function goes into a "Not Responding" mode in the task
manager.

This is be misleading. I see the same thing happening in Access when
making an MDE. I'd suggest letting this run for a while which could
be, say, 10 or 20 minutes. Also check the size of the .db1 file
periodically using Windows Explorer, by hitting F5 Refresh,
occasionaly and ensure it it still growing.

Is there any way to reduce the size without the
compression function?

Yes, you could try importing.

Any idea what made it so large in the first place? Do you have
images in the tables?

You don't want to embed graphics into a table as this causes
significantly bloating of the database. Frequently about one Mb per
graphic.

For more info see the Image Handling Tips page at my website.
http:\\www.granite.ab.ca\access\imagehandling.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Greg Blum]

Thanks Lester,

I think you are correct. I created my ODBC Data Source
with an industrial logging software package. I opened this
source as a database and began writing queries, lots of
queries, on that data as well as some reports. THe data
got voluminous. (i.e. 3 million records). As the data ages
I automatically shed. Even if I move the data I think the
size of the database will still be 2g. Compacting does not
seem to do it.

 

[Tony Toews]

I have let the compaction run to some kind of completion
and db1 becomes 2g as well.
Ouch.

I created the ODBC data source using an industrial logging
software package. I opened a database using this source
and began writing lots of queries and reports on the data.
The data got voluminous (3 million records each in several
tables). I started automatically shedding data which
brought down the size of the tables but by then the MDB
was 2G. Now it acts odd at times by not accepting data or
copying of data.

I am going to try segregating tables to separate databases
and referencing external data in my queries but I think my
MDB size will not change.

The only graphics that I have are some forms with trends
associated.

Any other thoughts?

Nope, graphics and other embedded objects such as Word docs are the
most likely cause of extreme size problems. But if you don't have
such and you do have 3 M records in each of several tables then that
could easily explain such size. We do have to ask though. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm