"Sam" <(E-Mail Removed)> wrote in message
news:C1E0E9BC-814B-4CBA-BF17-(E-Mail Removed)...
> MS-Excel has a maximum number of records of 65000 in each sheet. Does
> Access
> also have any pre-defined limits on the number of records?

No. Maxium size of a JET database is 2 GB. See 'specifications' in the help
file for more details.

>I am trying to
> evaluate if Access will be useful to handle over 2 million records in a
> single table.

It depends, among other things, on the size of the records. 2 million
records containing a handful of integer fields is one thing, two million
records containing lots of 255 character text fields is something else.

--
Brendan Reynolds
Access MVP

Brendan;

I fully agree.. but a LOT of systems require using text for keys; it's
not like we're all building a 'desktop data warehouse'

I don't believe that it's approprate to choose one database engine for
desktop development and another for enterprise-level.

I believe that there are definite efficiencies of scale to using SQL
Server for everything.
No linked tables; no copying data around.. no compact / repair.

you get real ETL tools.. you can have real scheduling capabilities.

If you have more than 10000 rows or you have a lot of new rows coming
in; if you only have 500 rows but you rewrite them every day? you'll be
better off in SQL Server

-Aaron


Brendan Reynolds wrote:
> "Sam" <(E-Mail Removed)> wrote in message
> news:C1E0E9BC-814B-4CBA-BF17-(E-Mail Removed)...
> > MS-Excel has a maximum number of records of 65000 in each sheet. Does
> > Access
> > also have any pre-defined limits on the number of records?
>
> No. Maxium size of a JET database is 2 GB. See 'specifications' in the help
> file for more details.
>
> >I am trying to
> > evaluate if Access will be useful to handle over 2 million records in a
> > single table.
>
> It depends, among other things, on the size of the records. 2 million
> records containing a handful of integer fields is one thing, two million
> records containing lots of 255 character text fields is something else.
>
> --
> Brendan Reynolds
> Access MVP

and the maximum for a single table is 1gb.. right?

that's a LOT more restrictive than 2gb per table

is it the same way in 2007?

-Aaron



(E-Mail Removed) wrote:
> Brendan;
>
> I fully agree.. but a LOT of systems require using text for keys; it's
> not like we're all building a 'desktop data warehouse'
>
> I don't believe that it's approprate to choose one database engine for
> desktop development and another for enterprise-level.
>
> I believe that there are definite efficiencies of scale to using SQL
> Server for everything.
> No linked tables; no copying data around.. no compact / repair.
>
> you get real ETL tools.. you can have real scheduling capabilities.
>
> If you have more than 10000 rows or you have a lot of new rows coming
> in; if you only have 500 rows but you rewrite them every day? you'll be
> better off in SQL Server
>
> -Aaron
>
>
> Brendan Reynolds wrote:
> > "Sam" <(E-Mail Removed)> wrote in message
> > news:C1E0E9BC-814B-4CBA-BF17-(E-Mail Removed)...
> > > MS-Excel has a maximum number of records of 65000 in each sheet. Does
> > > Access
> > > also have any pre-defined limits on the number of records?
> >
> > No. Maxium size of a JET database is 2 GB. See 'specifications' in the help
> > file for more details.
> >
> > >I am trying to
> > > evaluate if Access will be useful to handle over 2 million records in a
> > > single table.
> >
> > It depends, among other things, on the size of the records. 2 million
> > records containing a handful of integer fields is one thing, two million
> > records containing lots of 255 character text fields is something else.
> >
> > --
> > Brendan Reynolds
> > Access MVP

I use an Access 2000 DB with over 1 million records. Each record has about a
dozen or so fields, mostly text. What got me looking for info on a maximum
number of records in Access is that my DB is slowing down quite considerably
(small wonder, I guess). Even simple queries may take several minutes to
produce results. Still, it works...
I'll watch out for the 2GB limit and start thinking about what to do with
the older data. I'd sure hate to have to delete them.

"Brendan Reynolds" wrote:

>
> "Sam" <(E-Mail Removed)> wrote in message
> news:C1E0E9BC-814B-4CBA-BF17-(E-Mail Removed)...
> > MS-Excel has a maximum number of records of 65000 in each sheet. Does
> > Access
> > also have any pre-defined limits on the number of records?
>
> No. Maxium size of a JET database is 2 GB. See 'specifications' in the help
> file for more details.
>
> >I am trying to
> > evaluate if Access will be useful to handle over 2 million records in a
> > single table.
>
> It depends, among other things, on the size of the records. 2 million
> records containing a handful of integer fields is one thing, two million
> records containing lots of 255 character text fields is something else.
>
> --
> Brendan Reynolds
> Access MVP
>
>
>

On Thu, 27 Sep 2007 12:47:01 -0700, Fedor <(E-Mail Removed)>
wrote:

>I use an Access 2000 DB with over 1 million records. Each record has about a
>dozen or so fields, mostly text. What got me looking for info on a maximum
>number of records in Access is that my DB is slowing down quite considerably
>(small wonder, I guess). Even simple queries may take several minutes to
>produce results. Still, it works...

Do you have Indexes on the fields that you're using for searching and
sorting?

John W. Vinson [MVP]