PC Review


Reply
Thread Tools Rate Thread

Re: Are there limits on the number of records in an Access Table?

 
 
Brendan Reynolds
Guest
Posts: n/a
 
      8th Dec 2006

"Sam" <(E-Mail Removed)> wrote in message
news:(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


 
Reply With Quote
 
 
 
 
aaron.kempf@gmail.com
Guest
Posts: n/a
 
      8th Dec 2006
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:(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


 
Reply With Quote
 
 
 
 
aaron.kempf@gmail.com
Guest
Posts: n/a
 
      8th Dec 2006
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



http://www.pcreview.co.uk/forums/(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:(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


 
Reply With Quote
 
=?Utf-8?B?RmVkb3I=?=
Guest
Posts: n/a
 
      27th Sep 2007
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:(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
>
>
>

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      27th Sep 2007
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]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple subreports with limits on the number of records per page aarondorsey@gmail.com Microsoft Access Reports 0 25th Jan 2007 11:01 PM
Re: Are there limits on the number of records in an Access Table? Anthony Microsoft Access 1 8th Dec 2006 03:34 AM
Are there limits on number of variables / calculations =?Utf-8?B?S2VldG1hbg==?= Microsoft Excel Programming 0 30th May 2006 06:02 PM
Import from Excel limits Number of Records Paul Microsoft Outlook Contacts 1 6th Sep 2004 01:44 AM
Are there limits to the number of removable storage devices? Robert Tharp Windows XP Hardware 1 28th Oct 2003 12:31 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:15 PM.