Does the 2007 version still have the 2mb size limit?

[RayH-Radar]

Hi,

I have run into a problem by busting the szie limit of Access 2003. Longer
term, I plan to migrate the database to SQL server, but I need to get around
this problem pretty quickly. I know I could also spread the tables across
different databases, but agin, this could be quite a lengthy process. I was
wondering if upgrading to 2007 might provide a quick fix - or is this likely
to take some time to put convert / test.

 

[madmad]

Hi,

I have run into a problem by busting the szie limit of Access 2003.
Longer
term, I plan to migrate the database to SQL server, but I need to get
around
this problem pretty quickly. I know I could also spread the tables across
different databases, but agin, this could be quite a lengthy process. I
was
wondering if upgrading to 2007 might provide a quick fix - or is this
likely
to take some time to put convert / test.

 

[madmad]

Hi,

I have run into a problem by busting the szie limit of Access 2003.
Longer
term, I plan to migrate the database to SQL server, but I need to get
around
this problem pretty quickly. I know I could also spread the tables across
different databases, but agin, this could be quite a lengthy process. I
was
wondering if upgrading to 2007 might provide a quick fix - or is this
likely
to take some time to put convert / test.

 

[Albert D. Kallal]

There's actually no change in the size limits for access 2007, so that's not
a way out.

Perhaps you should try to identify where in the application all the space is
being used up?

The size limit of access 2002 is 2 gigs.

So, if you take a average name in a mailing list, add a few extra
fields...your record size might be about 120 characters on average in size.

So, the number of records you can store is:

2 gig = 2,147,483,648 Bytes
1 Character = 1 Byte
120 Characters = 120 Bytes

2 gig / 120 = 32 million records

So I'm assuming you must have some tables with 10 million records in them?

It's also possible that perhaps your storing images inside of the database,
and the thumbnails are taking up a huge amount of space. That thumbnail
image that access uses in fact often takes up more storage space than the
actual image itself. the solution this case is to actually used a path name
in link to the image file outside of MS access, and not store the images
inside an actual table.

On the other hand, maybe perhaps you just have some many records, that you
out grown MS access, and need to use SQL server.

You might want to mention the size of some your tables. Furthermore, after
you do a compact and repair, how large is the file?

 

[Rick Brandt]

Hi,

I have run into a problem by busting the szie limit of Access 2003.
Longer term, I plan to migrate the database to SQL server, but I need
to get around this problem pretty quickly. I know I could also
spread the tables across different databases, but agin, this could be
quite a lengthy process. I was wondering if upgrading to 2007 might
provide a quick fix - or is this likely to take some time to put
convert / test.

Its 2GB, not 2MB and yes 2007 has the same limit.

 

[Chip]

Hi,

I have run into a problem by busting the szie limit of Access 2003.  Longer
term, I plan to migrate the database to SQL server, but I need to get around
this problem pretty quickly.  I know I could also spread the tables across
different databases, but agin, this could be quite a lengthy process.  Iwas
wondering if upgrading to 2007 might provide a quick fix - or is this likely
to take some time to put convert / test.

I have run into the same probelm on my main database at work.
Unfortunately, I'm no MVP here, but my input would be definately check
the users of your Dbase. In my application, we archived a lot of
paperwork and "linked' to our Access dbase. This was in an effort to
"go paperless." When I adopted it, not only was the application
improperly set up, but I found users were actually linking the whole
files (pdf's in our case) to the entries. So I had to go through and
delete out those entries and relink the links instead of th whole
PDF's. I havnt completely gotten that done, so I'm still hovering at
2gig's. It has caused a lot of errors in my dbase and we've lost a
lot of support from our users. Many people have said, I'm not using
it cuase it causes errors. And I only have 12 people in my office, so
my advise is to nip your size issue in the bud, before you loose
valuable credibility in your office...

Chip

 

[SmartbizAustralia]

Sounds like you should be looking at SharePoint especially if your
organisation has a windows 2003 server box...
Access 2007 works great with sharepiont plus you get better searching
and even the special express version has a higher limit.

You might find though, that just by moving older files into other
databases. and/or dividing databases up into departments etc you can
go beyond 2G....

Everyone is right when they say you might have to buy a licenced
version of sql server to handle a larger database - or even think
about mysql - it's amazing the facebook figures on how they use this.

Either way, it can be a false economy to have everything free.....in
terms if time to develop/maintain/pain.

IF you decide to buy sql server - go 64 bit as it just runs faster and
most boxes nowadays are 64 bit

Tom Bizannes
Sydney, Australia
Microsoft Access and SharePoint Specialist

 

[BPC]

Hi,

I have run into a problem by busting the szie limit of Access 2003. Longer
term, I plan to migrate the database to SQL server, but I need to get around
this problem pretty quickly. I know I could also spread the tables across
different databases, but agin, this could be quite a lengthy process. I was
wondering if upgrading to 2007 might provide a quick fix - or is this likely
to take some time to put convert / test.

One tip I picked up to reduce file sizes is to set Unicode compression to
Yes for all Text fields. This has made a big difference to several of my
files.

 

[bensalem]

One tip I picked up to reduce file sizes is to set Unicode compression to
Yes for all Text fields. This has made a big difference to several of my
files.