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

Discussion in 'Microsoft Access' started by Brendan Reynolds, Dec 8, 2006.

  1. "Sam" <> wrote in message
    news:...
    > 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 Reynolds, Dec 8, 2006
    #1
    1. Advertisements

  2. Brendan Reynolds

    Guest

    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" <> wrote in message
    > news:...
    > > 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
     
    , Dec 8, 2006
    #2
    1. Advertisements

  3. Brendan Reynolds

    Guest

    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



    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" <> wrote in message
    > > news:...
    > > > 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
     
    , Dec 8, 2006
    #3
  4. Brendan Reynolds

    Guest Guest

    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" <> wrote in message
    > news:...
    > > 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
    >
    >
    >
     
    Guest, Sep 27, 2007
    #4
  5. On Thu, 27 Sep 2007 12:47:01 -0700, Fedor <>
    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]
     
    John W. Vinson, Sep 27, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Access 97 number data type limits

    Guest, Feb 20, 2004, in forum: Microsoft Access
    Replies:
    1
    Views:
    393
    Brendan Reynolds
    Feb 20, 2004
  2. Guest
    Replies:
    2
    Views:
    603
    RuralGuy
    Sep 8, 2005
  3. Mike
    Replies:
    8
    Views:
    310
  4. Guest

    Access table field limits

    Guest, Feb 19, 2006, in forum: Microsoft Access
    Replies:
    4
    Views:
    173
    John Vinson
    Feb 20, 2006
  5. Guest

    Access 2003 pivot table limits

    Guest, Sep 25, 2006, in forum: Microsoft Access
    Replies:
    0
    Views:
    617
    Guest
    Sep 25, 2006
Loading...

Share This Page