Scalability of Access database vs SQL Server

[Jacqui]

PLEASE I'm looking for an article or published/authoritative source discussing maximum limits for an Access database back-end - I know that for v2 the size limit for an .mdb file was 2GB and assume 2K and XP are the same(?) But then there is always this question whether Access can "handle" large amounts of data, and those "scalability" concerns - The debate over whether it's ok to develop with an Access back-end or go to (and this is often overkill) SQL Server. I can't believe I can't find something tangible in the knowledgebase on this

 

[Marshall Barton]

PLEASE I'm looking for an article or published/authoritative source discussing maximum limits for an Access database back-end - I know that for v2 the size limit for an .mdb file was 2GB and assume 2K and XP are the same(?) But then there is always this question whether Access can "handle" large amounts of data, and those "scalability" concerns - The debate over whether it's ok to develop with an Access back-end or go to (and this is often overkill) SQL Server. I can't believe I can't find something tangible in the knowledgebase on this

Don't confuse yourself, Access is the RDBMS part of a
database system (i.e. the UI and development environment).
The Access box includes two database engines that can be
used on the desktop, Jet, a File Server type (the one you're
thinking of), and MSDE, a Database Server type (desktop
version of MS SQL Server). In addition Access can utilize
many other database systems such as DB2, Oracle, Infomix,
FoxPro, ...

For Jet, the 2 GB limit is the size limit for a single mdb
file, but Access can link to tables in many separate backend
mdb files. There are reports of Access applications using
30 or more backends, many of them over 1GB. With proper
indexing, a query to retrieve a few records out of a table
of a million records can be near instantaneous.

If you have a real possibility of needing to go to SQL
Server at some point in the future, then start out with MSDE
so all your code and stored procedures will move over
intact.

I'm not the athoritive source you asked for, but it's not
clear to me that you asked the right question.

 

[Markus Larsson]

I am administrator of one of those system
We have a total of 6 GB of data in 55 backend files all using Jet
I guess that we have around 300 tables and the biggest contain over 4 millljon records
We got around 40 Access-programs that run against the backend and around 20 concurennt users
So Access can handle more than most thinks

Markus Larsso
Sundsvall, Swede

----- Marshall Barton wrote: ----
For Jet, the 2 GB limit is the size limit for a single md
file, but Access can link to tables in many separate backen
mdb files. There are reports of Access applications usin
30 or more backends, many of them over 1GB. With prope
indexing, a query to retrieve a few records out of a tabl
of a million records can be near instantaneous

 

[Marshall Barton]

W O W ! !

That is truely impressive.

Who would ever have thought that Jet, a little desktop "toy"
database, could ever do all that?

 

[Arvin Meyer]

The biggest I've ever seen was an image database written in Access 2.0. It
is still going strong (no corruption issues in the 2 years since I was last
there) running from an NT4.0 DEC Alpha server. It consists of approximately
80 back-end databases, connected to a single front-end, each with 1 table,
for a total of approximately 30 GB! There are 2 users.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

W O W ! !

That is truely impressive.

Who would ever have thought that Jet, a little desktop "toy"
database, could ever do all that?
--
Marsh
MVP [MS Access]

I am administrator of one of those system.
We have a total of 6 GB of data in 55 backend files all using Jet.
I guess that we have around 300 tables and the biggest contain over 4 millljon records.
We got around 40 Access-programs that run against the backend and around 20 concurennt users.
So Access can handle more than most thinks.

----- Marshall Barton wrote: -----
For Jet, the 2 GB limit is the size limit for a single mdb
file, but Access can link to tables in many separate backend
mdb files. There are reports of Access applications using
30 or more backends, many of them over 1GB. With proper
indexing, a query to retrieve a few records out of a table
of a million records can be near instantaneous.

 

[John Vinson]

I am administrator of one of those system.
We have a total of 6 GB of data in 55 backend files all using Jet.
I guess that we have around 300 tables and the biggest contain over 4 millljon records.

VERY impressive, Markus. If you're willing I'll cite this system when
folks ask about the capacity of Access (with or without attribution,
whichever you prefer)!

 

[Markus Larsson]

Thats okay for me

Marku

----- John Vinson wrote: ----

On Thu, 1 Apr 2004 06:06:11 -0800, "Markus Larsson

I am administrator of one of those system
We have a total of 6 GB of data in 55 backend files all using Jet
I guess that we have around 300 tables and the biggest contain over 4 millljon records

VERY impressive, Markus. If you're willing I'll cite this system whe
folks ask about the capacity of Access (with or without attribution
whichever you prefer)

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi