Database Size Limits

[Gary]

Is it much of a problem is an Access database (on a network) reaches over 1
gig in size? I can easily see the database I designed reaching that figure
in a few weeks time. Will the database encounter corruption at some stage
with this size ?
I run the compacting/repair utility I run every day.
Some features of the database has each record linking to pictures (OLE
objects) where the pictures are kept on a network folder.

 

[fredg]

Is it much of a problem is an Access database (on a network) reaches over 1
gig in size? I can easily see the database I designed reaching that figure
in a few weeks time. Will the database encounter corruption at some stage
with this size ?
I run the compacting/repair utility I run every day.
Some features of the database has each record linking to pictures (OLE
objects) where the pictures are kept on a network folder.

See...
Help + Specification + Access Specifications

 

[John Vinson]

Is it much of a problem is an Access database (on a network) reaches over 1
gig in size? I can easily see the database I designed reaching that figure
in a few weeks time. Will the database encounter corruption at some stage
with this size ?
I run the compacting/repair utility I run every day.
Some features of the database has each record linking to pictures (OLE
objects) where the pictures are kept on a network folder.

Access is HORRIBLY inefficient at storing pictures in OLE objects: it
stores your image (which might be a very compact .jpg), AND a .bmp
image, AND a thumbnail .bmp image, AND a lot of system overhead for
each picture.

Generally, it's best not to do so at all. Instead, store the images
separately on disk (say as .jpg files), and just store a Hyperlink to
the image, or a text field containing the path and filename.

Yes, if you have A97 the database will crash when it hits 1 GByte;
A2000 and later give you up to 2 GByte, but I'd be very uncomfortable
above 1.5 or so.

John W. Vinson[MVP]

 

[aaron.kempf]

anythign over 100MB should be in Access Data Projects.

You can do the same thing that you're trying to do in Access Data
Projects against SQL Server; and it will scale to 100gb without a
problem.

It costs about $4k per server processor for the workgroup edition-- but
it's worth it.

-Aaron

 

[Larry Linson]

anythign over 100MB should be in Access Data Projects.

Aaron is, of course, entitled to his opinion, even if he is obsessed with
ADPs. However, I feel obligated to point out (1) that changing to an ADP
does nothing to improve capacity or performance if you continue to use an
MDB file as the datastore. And, (2) there are multitudes of Access databases
running with the default Jet database engine, using MDB, that far exceed
100MB. Finally, (3) that the former ADP/ADO product manager for a recent
version of Access points out that MDB-Jet-ODBC-Server is usually to be
preferred to ADP-ADODB-OleDB-SQLServer with the currently supported versions
of Access.

You can do the same thing that you're
trying to do in Access Data Projects
against SQL Server; and it will scale to
100gb without a problem.

It costs about $4k per server processor
for the workgroup edition-- but
it's worth it.

Actually, there is a version of SQL Server called MSDE (Microsoft Data
Engine, or SQL Server Desktop Edition) that comes with Access and entails no
extra cost, but it maxes out at 2GB, just like an MDB. There is also, now, a
free Beta test version of the new SQL Server Express edition of SQL Server
codename Yukon that does not have a 2GB limit. Last I heard, no decision has
been made on a retail price, or whether it will be free, or not.

And, should you feel compelled to go to a server DB for your datastore,
Access with Jet and ODBC will work with any ODBC-compliant server, and most
server DBs, commercial or open-source, are ODBC-compliant. Most of the
contract work I did from 1995 - 2000 was on a system that used Informix with
an Access client.

Larry Linson
Microsoft Access MVP

 

[aaron.kempf]

the former ADP/ADO PM is _WRONG_ and thats why he is a former PM

he didnt' do his job; ADP is QUITE buggy and I think that it's obvious
why he is no longer PM there lol

changing to an ADP doesnt change performance?

have you ever opened a MDB and had to wait 30 seconds for it to pull
the huge table across the network?
in ADP it just sends the results.

ADP is the most important platform in the world; and I am looking
forward to seeing the new version

 

[Albert D.Kallal]

the former ADP/ADO PM is _WRONG_ and thats why he is a former PM

he didnt' do his job; ADP is QUITE buggy and I think that it's obvious
why he is no longer PM there lol

Sorry, there is a good number of the actual developers of ms-access that
actually agree, and state that using odbc to sql server is a preferred
approach.

you can see pictures of me talking to them here:
http://www.members.shaw.ca/albertkallal/MVP/index.htm

changing to an ADP doesnt change performance?

have you ever opened a MDB and had to wait 30 seconds for it to pull
the huge table across the network?
in ADP it just sends the results.

What do you mean by the above? If you got 500,000 records in a jet file
share (a mdb file), and request one record, the whole table IS NOT sent over
the network. The above seems to hint that the whole table is sent, and
nothing could be further from the truth. Why would one wait when the whole
table is not sent?

Again, in plain English:

If you have a mdb file share with your back end sitting on the server, and
request one record (say, by a key id for example), then the whole table is
NOT sent across the network.

If you have a mdb front end via odbc to the back end that is running sql
server, and request one record, then the whole table is not sent across the
network.

And, if you have a ADP project, then again the whole table is not sent over
the network.

Care to explain which of the above 3 will perform better, and more
importantly why? In all three cases, the whole table is NOT transferred
here. I do hope you realize the above.

ADP is the most important platform in the world; and I am looking
forward to seeing the new version

If it is so important to you, why are you spending so much time bad mouthing
it?

Sure, nothing wrong with being a fan of a particular setup. If you like
ADP's, then great. But please keep your ignorance in check, and don't tell
me that the whole table gets sent across the network then I use a split mdb
setup.

There also seems to be a big deal of hypocrisy here on your part as to if
you do in fact like ADP's.

You should stop and think for a minute here. Do you want to just rant,
complain, and cause trouble, or are you actually serious about moving
forward and looking for solutions to your problems?

By being a first class jerk, you are in effect loosing the support of the
very group and people here that could champion your cause in your favor. So,
you might win the war words and the war of complaining...but at the end of
the day, you will not get what you actually need, and that is support of the
people here....


You will find life much better when you work with the people that can help
you...not against them.