Large database issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with about 50 tables. The database is very large and I
would like to 'archive' some of the tables outside of the db. Is there a way
to tell the largest tables? That way I can see which tables are causing the
db to be so large? (1.5gb) I have compacted. Thanks!
 
What are your tables? If your database is designed properly, you can't just
pull out tables - they should all be used.
 
We have monthly/weekly files that are brought in and appended to the main
table. So it is a collection of files that would need to be exported out and
archived. They are not integral to the function of the db at all.
 
If you pull the data in and append it to your main table, why not delete it
as soon as the append is performed? Otherwise, you are storing redundant
(and unneeded) data in your file.

I'd delete all of them if it were me.
 
Angie

If you are appending "files", rather than data, your database may be
experiencing "bloat" due to how Access stores images. This is commonly seen
when folks try to store pictures in Access.

If you are appending data, not entire files, what volume (?bytes?) do you
append in a week? a month? (not "how much does Access grow", but "how
large are the incoming files?")

Have you made a backup copy of your .mdb file, then run Compact & Repair?

Regards

Jeff Boyce
<Office/Access MVP>
 
Angie said:
I have a database with about 50 tables. The database is very large and
I would like to 'archive' some of the tables outside of the db. Is
there a way to tell the largest tables? That way I can see which
tables are causing the db to be so large? (1.5gb) I have compacted.
Thanks!
Click to expand...

I suggest that you add the new data to the primary table and delete the
new data or table depending on your needs.

I suspect the question is really about archiving data. In most uses it
is better to keep all active and inactive data that you want to maintain in
one table and use a single binary (yes no) field to indicate that it is
active or inactive.

From that point on, all your activity should use queries, forms or
reports to view data and you can then filter the data so you see only
current, inactive or all data. If you like you can add a date inactive to
determine when the data was first made inactive.
 
in most situations; it is unbearable to live with a firm 2gb limit.

I would reccomend upgrading to MSDE, SQL Express or full SQL Server.
 
in most situations; it is unbearable to live with a firm 2gb limit.

I would reccomend upgrading to MSDE, SQL Express or full SQL Server.
Click to expand...

You here? I did not know school was out today.

How many people and businesses do you really think have a problem with
the 2 gig limit?
 
in most situations; it is unbearable to live with a firm 2gb limit.

I would reccomend upgrading to MSDE, SQL Express or full SQL Server.
Click to expand...

Of course. Just as nobody should ever own a 3/4 ton pickup truck.
Anybody who needs to transport stuff should own at least an 18-wheel
semi; it can handle jobs that the pickup simply cannot.

Pickup trucks should be banned, and everyone who sells them should be
jailed for fraud. They're not real trucks!

John W. Vinson[MVP]
 
look.. i have seen people in EXCEL hit the 2gb limit. i mean-- it's
ridiculous

this guys is obvioulsy having poor performance with too many records.
i believe that a single record in a database means that your dataset is
too large for MDB; and you should use a real DBMS. queries on top of
queries don't crap out with a real query engine. MDB is just crap from
what i've seen.

im not saying that MSDE doesn't have the 2gb limit. I'm just saying
that it's a lot more manageable; since you dont have to deal with BLOAT
every 5 minutes.
 
and john? just for the record

bill gates; and microsoft executives have been putting our soldiers at
risk by not securing sql authentication.

mysql-- the free database-- has much much much stronger authentication
options.

like the basics.. only allow a certain user from a particular IP
address.

until SQL comes out with that functionality; i call for the arrest of
Bill Gates and other Executives from Microsoft. they are guilty of
treason-- for selling a database that is impossible to secure against a
simple dictionary attack.

i mean-- they're putting our soldiers; our credit card numbers; and our
families-- at risk
 
Angie

Before you follow any suggestions that you need to migrate to a "real"
database, one with more room, be aware that such conversions are not
painless.

The time you spend now analyzing what data you have, and can expect to add,
and how to model that data in a well-normalized database structure will pay
off, no matter whether you stick with your current system or decide to
upsize.

Consider posting questions about your current design in the tablesdbdesign
newsgroup. And it may still turn out that you have a well-normalized design
for a LOT of data, and need to move up ... I'm just suggesting that this is
not the FIRST move to consider.

Regards

Jeff Boyce
<Office/Access MVP>
 
look.. i have seen people in EXCEL hit the 2gb limit. i mean-- it's
ridiculous

... MDB is just crap from what i've seen.
Click to expand...

Your knowledge of Excel seems to equal your knowledge of Access. Excel
does not have a 2 gig limit to the best of my knowledge.
 
bill gates; and microsoft executives have been putting our soldiers at
risk by not securing sql authentication.
Click to expand...

Time for more meds. Talk to the nice men in the white coats, they will
take care of you.


Note: to the men in white coats: Please remove the computer internet
access. :-)
 
oh it's not painless at all

but a lot less pain than losing data

joseph you're a ****ing idiot and you're just flat out wrong. excel
does have a 2gb limit. anyone using access or excel for more than a
single record should be standing in an unemployment line right now
 
Wow, lots of great replies and a few clinkers :)

I guess my question should be is there VBA that could list my tables and
what each size is? I wish they had the option to show table size in the
properties of each table. Not unlike checking file size on a file on your
desktop (right click/properties)

It would be nice to find the 'worst offenders' and be able to delete them. :)
 
Wow, lots of great replies and a few clinkers :)

I guess my question should be is there VBA that could list my tables and
what each size is? I wish they had the option to show table size in the
properties of each table. Not unlike checking file size on a file on your
desktop (right click/properties)

It would be nice to find the 'worst offenders' and be able to delete them. :)
Click to expand...

Well... it's not that simple.

A Table isn't really all that analogous to a disk file. Your table
will store your data; but there are indexes which take a variable
amount of space (and it's not at all easy to determine how much),
system overhead, lost blocks, uncleaned deleted data (even if you
compact). Even the data itself is tricky to size, since Text fields
are stored without trailing blanks - e.g. a 255 byte Text field might
actually take up only 11 bytes on disk in one record, and 259 (255 in
contents, 4 in overhead) in another. IME the "size of the table" is
simply not a very useful number; if you really need to know, you can
compact the database; note its size; delete the table; compact again;
and compare sizes. Be aware that Access allocates disk in 32KByte
chunks so you won't be able to get the size to any finer resolution
than this!

If you're routinely importing files, you might want to consider having
TWO .mdb files - one for the "real data", and the other for temporary
import files. You can link to both backends from a shared frontend (a
split architecture is a *very* good idea in any case), and run your
append queries from the scratch backend into the production one.
Regular compaction is essential in any case.

John W. Vinson[MVP]
 
Back
Top