When does file size get too large?

[Jake F]

I work with a database that is at 2 gb. I'm wondering if that's getting too
large or at what point I need to clean it up and break it down. It was
created awhile back and was added to by different people so I don't want to
remove anything unless necessary. I believe what is taking most of the space
is the VBA, but that can't be cut down as far as I know. Basically when do I
need to create a new database or clean this one up? Thanks.

 

[Dirk Goldgar]

I work with a database that is at 2 gb. I'm wondering if that's getting
too
large or at what point I need to clean it up and break it down. It was
created awhile back and was added to by different people so I don't want
to
remove anything unless necessary. I believe what is taking most of the
space
is the VBA, but that can't be cut down as far as I know. Basically when
do I
need to create a new database or clean this one up? Thanks.

2GB is the maximum size of an Access database. If you've reached that size,
you can expect problems in normal operation.

Have you ever compacted this database? If not, you may find that the
database is much smaller after compaction. I suggest you make a backup copy
first, then use the Tools -> Compact & Repair... menu item to compact the
database and see what the size is now.

It's very unlikely that the VBA is taking up most of the space. The most
common factor, aside from a very large number of records, is the storing of
images directly in the database. Are you doing that, by any chance?

 

[Jake F]

I compacted and it is still the same size. There are not images on here and
the data comes mostly from linked tables. The database is filled with
queries, reports, a dozen forms, some macros, and lots of VBA for one-click
operations. I believe I can cut down a lot of the queries but that's
probably it. Should I go to using a new DB? Thanks.

 

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

Jet craps out at about 25mb.

I've seen it hundreds of times over the past 15 years-- Jet just
doesn't cut it for any business-- because it's impossible to forecast
(with certainty ) the growth of your data.

If your database is popular (and you use jet) then it runs like crap.

build it nice or build it twice.
Move to SQL Server, kid.

-Aaron
MCITP: DBA SQL 2005

 

[Dirk Goldgar]

I compacted and it is still the same size. There are not images on here
and
the data comes mostly from linked tables. The database is filled with
queries, reports, a dozen forms, some macros, and lots of VBA for
one-click
operations. I believe I can cut down a lot of the queries but that's
probably it. Should I go to using a new DB? Thanks.

Although I can't see your database, I cannot imagine that you have 2GB worth
of VBA in there. It's very odd that a database without images, in which
most tables are linked, is up to 2GB, compacted. So I'm not sure what's
going on. Are you sure the database isn't 2MB, not GB?

You might try creating a new database and importing all objects into it. If
you do your import in stages, you may be able to find out what exactly is
taking up all the space.

Aaron's assertion that "Jet craps out at about 25mb" is totally false, by
the way.

 

[George Hepworth]

Honesty is always the best policy, IMO. Followed in close second place by
lucidity.

The OP states that [he] "work with a database that is at 2 gb". Last time
I did the math, 2 gb was WAAAAYYYYYYYY MOOOOORRRRRE than 25 mb.

So, honestly, can anyone say that Jet can't handle more than 25 mb, given
the facts at hand?

That said, the size of this database IS a legitimate concern and one that
needs to be addressed lucidly. Why? Because Access does have a 2 gb size
limit on files. This one seems to be right there.

Jake:

SO, the clue to answering the question lucidly is to address the part of
your statement where you indicate that you believe VBA is taking up most of
the space. The implies you have a single mdb file in which both the
interface objects (forms, reports, queries, code modules, etc.) and the data
(the tables) are stored. This is generally not a good idea, especially when
you have multiple users in the database.

If that's the case, you should, at a minimum create a split database, moving
the tables to a "back end" mdb and linking to them from the "front end". If
you have not already done so, you should also do a compact and repair on
both front end and back end. Doing so regularly is a good practice.

HTH
George


message
Jet craps out at about 25mb.

I've seen it hundreds of times over the past 15 years-- Jet just
doesn't cut it for any business-- because it's impossible to forecast
(with certainty ) the growth of your data.

If your database is popular (and you use jet) then it runs like crap.

build it nice or build it twice.
Move to SQL Server, kid.

-Aaron
MCITP: DBA SQL 2005

 

[Jake F]

I'm not sure is splitting would help. There are a couple simple tables
(couple fields, under 20 rows of data) contained in the DB, but that's it.
The rest are linked to an ODBC which I can only link to. I've
compacted/repaired and it didn't shrink the size at all. Also, the file size
is 2,097,144 KB which puts me just under 2 GB. I think creating a new DB and
moving it in sections is going to be my best bet. Moving to and SQL server
would be great, but that's not an immediate solution because that's money on
a new program and training which means more money. So two access DBs will
have to suffice for now. Thanks all.

Honesty is always the best policy, IMO. Followed in close second place by
lucidity.

The OP states that [he] "work with a database that is at 2 gb". Last time
I did the math, 2 gb was WAAAAYYYYYYYY MOOOOORRRRRE than 25 mb.

So, honestly, can anyone say that Jet can't handle more than 25 mb, given
the facts at hand?

That said, the size of this database IS a legitimate concern and one that
needs to be addressed lucidly. Why? Because Access does have a 2 gb size
limit on files. This one seems to be right there.

Jake:

SO, the clue to answering the question lucidly is to address the part of
your statement where you indicate that you believe VBA is taking up most of
the space. The implies you have a single mdb file in which both the
interface objects (forms, reports, queries, code modules, etc.) and the data
(the tables) are stored. This is generally not a good idea, especially when
you have multiple users in the database.

If that's the case, you should, at a minimum create a split database, moving
the tables to a "back end" mdb and linking to them from the "front end". If
you have not already done so, you should also do a compact and repair on
both front end and back end. Doing so regularly is a good practice.

HTH
George


message
Jet craps out at about 25mb.

I've seen it hundreds of times over the past 15 years-- Jet just
doesn't cut it for any business-- because it's impossible to forecast
(with certainty ) the growth of your data.

If your database is popular (and you use jet) then it runs like crap.

build it nice or build it twice.
Move to SQL Server, kid.

-Aaron
MCITP: DBA SQL 2005

I work with a database that is at 2 gb. I'm wondering if that's getting
too
large or at what point I need to clean it up and break it down. It was
created awhile back and was added to by different people so I don't want
to
remove anything unless necessary. I believe what is taking most of the
space
is the VBA, but that can't be cut down as far as I know. Basically when do
I
need to create a new database or clean this one up? Thanks.

 

[Jake F]

I moved almost everything to a new DB and it's back down to 22,000 kb.
Something made it jump from that to 2 gb, but I'm not sure what. Either way,
thanks for the help.

I'm not sure is splitting would help. There are a couple simple tables
(couple fields, under 20 rows of data) contained in the DB, but that's it.
The rest are linked to an ODBC which I can only link to. I've
compacted/repaired and it didn't shrink the size at all. Also, the file size
is 2,097,144 KB which puts me just under 2 GB. I think creating a new DB and
moving it in sections is going to be my best bet. Moving to and SQL server
would be great, but that's not an immediate solution because that's money on
a new program and training which means more money. So two access DBs will
have to suffice for now. Thanks all.

Honesty is always the best policy, IMO. Followed in close second place by
lucidity.

The OP states that [he] "work with a database that is at 2 gb". Last time
I did the math, 2 gb was WAAAAYYYYYYYY MOOOOORRRRRE than 25 mb.

So, honestly, can anyone say that Jet can't handle more than 25 mb, given
the facts at hand?

That said, the size of this database IS a legitimate concern and one that
needs to be addressed lucidly. Why? Because Access does have a 2 gb size
limit on files. This one seems to be right there.

Jake:

SO, the clue to answering the question lucidly is to address the part of
your statement where you indicate that you believe VBA is taking up most of
the space. The implies you have a single mdb file in which both the
interface objects (forms, reports, queries, code modules, etc.) and the data
(the tables) are stored. This is generally not a good idea, especially when
you have multiple users in the database.

If that's the case, you should, at a minimum create a split database, moving
the tables to a "back end" mdb and linking to them from the "front end". If
you have not already done so, you should also do a compact and repair on
both front end and back end. Doing so regularly is a good practice.

HTH
George


message
Jet craps out at about 25mb.

I've seen it hundreds of times over the past 15 years-- Jet just
doesn't cut it for any business-- because it's impossible to forecast
(with certainty ) the growth of your data.

If your database is popular (and you use jet) then it runs like crap.

build it nice or build it twice.
Move to SQL Server, kid.

-Aaron
MCITP: DBA SQL 2005

I work with a database that is at 2 gb. I'm wondering if that's getting
too
large or at what point I need to clean it up and break it down. It was
created awhile back and was added to by different people so I don't want
to
remove anything unless necessary. I believe what is taking most of the
space
is the VBA, but that can't be cut down as far as I know. Basically when do
I
need to create a new database or clean this one up? Thanks.

 

[Dirk Goldgar]

I moved almost everything to a new DB and it's back down to 22,000 kb.
Something made it jump from that to 2 gb, but I'm not sure what. Either
way,
thanks for the help.

Did you mention what version of Access you're using? I seem to recall that
there is a bug in the compact function in Access 2002 and 2003 when used
with the Access 2002 file format. I think this KB article describes it:

http://support.microsoft.com/kb/810415

 

[GenlAccess]

Jet craps out at about 25mb.

You are ignorant of Jet or you are a liar, or both. No matter which, what
you wrote is dead wrong.

I've seen it hundreds of times over
the past 15 years --

This would support the "liar" option.

Jet just doesn't cut it for any business

Uncounted thousands of businesses run very nicely with no database other
than Jet.

-- because it's impossible to forecast
(with certainty) the growth of your
data.

Totally meaningless in context of "why Jet wouldn't be suitable for any
business."

-Aaron
MCITP: DBA SQL 2005

Give us a link to prove your claim to be Microsoft Certified. The
misinformation you spread doesn't indicate that you know enough to converse
intelligently on the subject much less pass a certification test.

Genl Access

 

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

Genl Access;

I don't need to prove anything to you-- I've got plenty of asshole
stalkers in this group-- people that follow me around and write
letters to my bosses about stuff I write WHEN I AM AT MY OWN HOUSE.

Screw you for having the audacity to question me.
You play with your baby sized database.. and you bother to question
me.. _WHY_?

Just because you have no penis? Is that the problem?

Or what gives, smegma breath?

-Aaron

 

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

Jet craps out with 25 mb of data.

I've been making a pretty decent living these past decades-- upsizing
baby-sized Jet database to a 'real mans database' after you jet kids
throw up your hands and blame it on the network.

Jet doesnt' work over VPN, WAN, WAN, OR WIRELESS.

IN OTHER WORDS-- IT DOESN"T FIT THE NEEDS OF MOST BUSINESSES.

-Aaron Kempf
MCITP: DBA SQL 2005