Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    capacity (a2000)

    I was asked recently about setting up an Access database to handle comma delimited text imports because the imports had grown too large for Excel to handle, i.e., nearly a million records. Before I get any deeper in thinking about how to set it up, my initial thought is that it would be smarter to upsize from Access and use SQL Server instead because of the sheer number of records.

    I'm use to working with small databases with records numbering in the 1000s or 10,000s, so this one is outside my range of experience. I'm looking for guidance from someone with experience with dbs of this size. Based on the little I've told you, what do you think?

    esw

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: capacity (a2000)

    It really depends on the size of each individual record, but if you are approaching a million records, SQL Server would be a much better bet. For one thing, Access databases in Jet 4.0 have a max size of 2GB, so you might start bumping up against that pretty soon. In addition, if you are running queries across the network, it would really hammer your network and make things pretty sluggish. For that size of table, I would recommend the full version of SQL Server, not the Desktop or MSDE version.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: capacity (a2000)

    A lot depends on what you intend to do with the data once it is in the db. I used Access 2.0 with tables consisting of record counts in the 100,000's. But all I did was simple reporting and filtering. For more complex tasks I would definitely recommend SQL Server would be your best bet and may be worth the effort in the long run.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: capacity (a2000)

    It is not really the total number of records, but the number of records in each individual table that really matters. The more BIG tables you have, the more difficult it will be to maintain good performance with Access. Good indexing will help, but at a point in time your performance may not be what you'd want. If you have the choice of upsizing to SQL server now, I'd probably lean towards that solution.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •