How much data Access can handle?

Discussion in 'Microsoft Access' started by Guest, Mar 21, 2007.

  1. Guest

    Guest Guest

    Hi,

    i want to know how much data the Access can handle. I have a 2 GB flat file
    with which i want to do some manipulation of data and put it in to different
    Access Tables. I wan to know how much time it will take for Access to import
    the 2 GB flat file to a dummy table and do some manipulation and using some
    queries (ETL) store it in other 2-3 tables.

    Also, is any template is available for estimating the time and cost for
    doing Access Projects?

    Many thanks,
    Sunil
     
    Guest, Mar 21, 2007
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    Press F1 and search on "Specifications"

    You will get this (plus lots more info)

    Number of objects in a database 32,768
    Modules (including forms and reports with the HasModule property set to
    True) 1,000
    Number of characters in an object name 64
    Number of characters in a password 14
    Number of characters in a user name or group name 20
    Number of concurrent users 255



    --
    Wayne
    Manchester, England.



    "Sunil" wrote:

    > Hi,
    >
    > i want to know how much data the Access can handle. I have a 2 GB flat file
    > with which i want to do some manipulation of data and put it in to different
    > Access Tables. I wan to know how much time it will take for Access to import
    > the 2 GB flat file to a dummy table and do some manipulation and using some
    > queries (ETL) store it in other 2-3 tables.
    >
    > Also, is any template is available for estimating the time and cost for
    > doing Access Projects?
    >
    > Many thanks,
    > Sunil
    >
     
    Guest, Mar 21, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    I believe it is 2GB of memory but the amount of data depends on the size,
    number tables, fields, size of fields etc etc.


    "Sunil" wrote:

    > Hi,
    >
    > i want to know how much data the Access can handle. I have a 2 GB flat file
    > with which i want to do some manipulation of data and put it in to different
    > Access Tables. I wan to know how much time it will take for Access to import
    > the 2 GB flat file to a dummy table and do some manipulation and using some
    > queries (ETL) store it in other 2-3 tables.
    >
    > Also, is any template is available for estimating the time and cost for
    > doing Access Projects?
    >
    > Many thanks,
    > Sunil
    >
     
    Guest, Mar 21, 2007
    #3
  4. Guest

    Guest Guest

    Ok. This info is useful for me. Also i need to know how much time it will
    take to import from a 2 GB flat file to a access table?


    "scubadiver" wrote:

    >
    > I believe it is 2GB of memory but the amount of data depends on the size,
    > number tables, fields, size of fields etc etc.
    >
    >
    > "Sunil" wrote:
    >
    > > Hi,
    > >
    > > i want to know how much data the Access can handle. I have a 2 GB flat file
    > > with which i want to do some manipulation of data and put it in to different
    > > Access Tables. I wan to know how much time it will take for Access to import
    > > the 2 GB flat file to a dummy table and do some manipulation and using some
    > > queries (ETL) store it in other 2-3 tables.
    > >
    > > Also, is any template is available for estimating the time and cost for
    > > doing Access Projects?
    > >
    > > Many thanks,
    > > Sunil
    > >
     
    Guest, Mar 21, 2007
    #4
  5. I somewhat doubt that 2 gigs of data will fit in a access file.

    access needs some extra working room...especially if you plan to have any
    indexes etc.....

    I would be hesitate if the file was 1 gig in size.......

    it is possible that your flat file has an enormous amount of blank spaces.
    (ms-access does NOT store those).

    So, you might have some success here..but, the numbers you have are too
    close to the limits of access....

    If you "many", or much of your data is padded with blank characters (ie:
    this is a fixed width file, not a text file with padding removed), then you
    likely can try this....

    If the data is not fixed length, and fields are not padded..then you not
    going anywhere with using ms-access in this case...

    I would suggest perhaps splitting up the data into 500 meg chunks (that
    would be 4 of them)......


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, Mar 21, 2007
    #5
  6. Guest

    Guest Guest

    Thanks Albert. i have ome more quick question. in access help i read that the
    table size can be 2 GB max. i want to confirm whether its table size or mdb
    size? Bcoz i read in another post that the 2GB max is for mdb. so am in a
    diellama :-(

    also is it possible for you to tell us how much time it will take to import
    2 GB flat file data (without any null) and with 10 colums (assume comma
    separeted file) in to a single table.

    assume a conditon with perfect flat file of size 2Gb, whether access table
    will store those records?

    Once again many thanks for your help.

    Thanks,
    Sunil

    "Albert D. Kallal" wrote:

    > I somewhat doubt that 2 gigs of data will fit in a access file.
    >
    > access needs some extra working room...especially if you plan to have any
    > indexes etc.....
    >
    > I would be hesitate if the file was 1 gig in size.......
    >
    > it is possible that your flat file has an enormous amount of blank spaces.
    > (ms-access does NOT store those).
    >
    > So, you might have some success here..but, the numbers you have are too
    > close to the limits of access....
    >
    > If you "many", or much of your data is padded with blank characters (ie:
    > this is a fixed width file, not a text file with padding removed), then you
    > likely can try this....
    >
    > If the data is not fixed length, and fields are not padded..then you not
    > going anywhere with using ms-access in this case...
    >
    > I would suggest perhaps splitting up the data into 500 meg chunks (that
    > would be 4 of them)......
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    >
    >
    >
    >
     
    Guest, Mar 21, 2007
    #6
  7. The 2 Gb limit is on the MDB file.

    Like Albert, I doubt very much that you'll be able to import a 2 Gb file
    into an MDB.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Sunil" <> wrote in message
    news:...
    > Thanks Albert. i have ome more quick question. in access help i read that
    > the
    > table size can be 2 GB max. i want to confirm whether its table size or
    > mdb
    > size? Bcoz i read in another post that the 2GB max is for mdb. so am in a
    > diellama :-(
    >
    > also is it possible for you to tell us how much time it will take to
    > import
    > 2 GB flat file data (without any null) and with 10 colums (assume comma
    > separeted file) in to a single table.
    >
    > assume a conditon with perfect flat file of size 2Gb, whether access table
    > will store those records?
    >
    > Once again many thanks for your help.
    >
    > Thanks,
    > Sunil
    >
    > "Albert D. Kallal" wrote:
    >
    >> I somewhat doubt that 2 gigs of data will fit in a access file.
    >>
    >> access needs some extra working room...especially if you plan to have any
    >> indexes etc.....
    >>
    >> I would be hesitate if the file was 1 gig in size.......
    >>
    >> it is possible that your flat file has an enormous amount of blank
    >> spaces.
    >> (ms-access does NOT store those).
    >>
    >> So, you might have some success here..but, the numbers you have are too
    >> close to the limits of access....
    >>
    >> If you "many", or much of your data is padded with blank characters (ie:
    >> this is a fixed width file, not a text file with padding removed), then
    >> you
    >> likely can try this....
    >>
    >> If the data is not fixed length, and fields are not padded..then you not
    >> going anywhere with using ms-access in this case...
    >>
    >> I would suggest perhaps splitting up the data into 500 meg chunks (that
    >> would be 4 of them)......
    >>
    >>
    >> --
    >> Albert D. Kallal (Access MVP)
    >> Edmonton, Alberta Canada
    >>
    >>
    >>
    >>
     
    Douglas J. Steele, Mar 21, 2007
    #7
  8. Guest

    Guest Guest

    I have an .mdb which has been adding 25Meg flat files each month since
    200606, so is now up to 200Meg. The first 6 months it took under 1 min. The
    last two months I noticed it takes longer to add the 25 Meg chunks (1-3
    mins). By extrapolation and assuming load time/Meg increases with size, I
    would estimate no less than 12mins/100Meg * 20 = 4 hours.

    "Sunil" wrote:

    > Thanks Albert. i have ome more quick question. in access help i read that the
    > table size can be 2 GB max. i want to confirm whether its table size or mdb
    > size? Bcoz i read in another post that the 2GB max is for mdb. so am in a
    > diellama :-(
    >
    > also is it possible for you to tell us how much time it will take to import
    > 2 GB flat file data (without any null) and with 10 colums (assume comma
    > separeted file) in to a single table.
    >
    > assume a conditon with perfect flat file of size 2Gb, whether access table
    > will store those records?
    >
    > Once again many thanks for your help.
    >
    > Thanks,
    > Sunil
    >
    > "Albert D. Kallal" wrote:
    >
    > > I somewhat doubt that 2 gigs of data will fit in a access file.
    > >
    > > access needs some extra working room...especially if you plan to have any
    > > indexes etc.....
    > >
    > > I would be hesitate if the file was 1 gig in size.......
    > >
    > > it is possible that your flat file has an enormous amount of blank spaces.
    > > (ms-access does NOT store those).
    > >
    > > So, you might have some success here..but, the numbers you have are too
    > > close to the limits of access....
    > >
    > > If you "many", or much of your data is padded with blank characters (ie:
    > > this is a fixed width file, not a text file with padding removed), then you
    > > likely can try this....
    > >
    > > If the data is not fixed length, and fields are not padded..then you not
    > > going anywhere with using ms-access in this case...
    > >
    > > I would suggest perhaps splitting up the data into 500 meg chunks (that
    > > would be 4 of them)......
    > >
    > >
    > > --
    > > Albert D. Kallal (Access MVP)
    > > Edmonton, Alberta Canada
    > >
    > >
    > >
    > >
     
    Guest, Mar 21, 2007
    #8
  9. Guest

    Guest Guest

    You' better try MySQL since MDB can't handle more than 2GB...
    unfortunatelly. In this modern era I'd expect 20 GB, at least.

    Vlado

    "Sunil" <> píše v diskusním příspěvku
    news:...
    > Hi,
    >
    > i want to know how much data the Access can handle. I have a 2 GB flat
    > file
    > with which i want to do some manipulation of data and put it in to
    > different
    > Access Tables. I wan to know how much time it will take for Access to
    > import
    > the 2 GB flat file to a dummy table and do some manipulation and using
    > some
    > queries (ETL) store it in other 2-3 tables.
    >
    > Also, is any template is available for estimating the time and cost for
    > doing Access Projects?
    >
    > Many thanks,
    > Sunil
    >
     
    Guest, Mar 21, 2007
    #9
  10. Estimating how long it might take to import the file is hard to do for a lot
    of reasons, including the fact that your computer and network capabilities
    play a role.

    Beyond that, you indicate your want to import the file and then so some
    manipulation. The nature of that manipulation is also a determining factor
    in how long it will take. Not knowing what you have in mind there, no one
    could give you any reasonable guess.

    At the risk of sounding facetious, therefore, I would guess it's going to be
    more than a few seconds, and less than a few days.


    --
    George Hepworth
    2007 Access MVP

    "Sunil" <> wrote in message
    news:...
    > Thanks Albert. i have ome more quick question. in access help i read that
    > the
    > table size can be 2 GB max. i want to confirm whether its table size or
    > mdb
    > size? Bcoz i read in another post that the 2GB max is for mdb. so am in a
    > diellama :-(
    >
    > also is it possible for you to tell us how much time it will take to
    > import
    > 2 GB flat file data (without any null) and with 10 colums (assume comma
    > separeted file) in to a single table.
    >
    > assume a conditon with perfect flat file of size 2Gb, whether access table
    > will store those records?
    >
    > Once again many thanks for your help.
    >
    > Thanks,
    > Sunil
    >
    > "Albert D. Kallal" wrote:
    >
    >> I somewhat doubt that 2 gigs of data will fit in a access file.
    >>
    >> access needs some extra working room...especially if you plan to have any
    >> indexes etc.....
    >>
    >> I would be hesitate if the file was 1 gig in size.......
    >>
    >> it is possible that your flat file has an enormous amount of blank
    >> spaces.
    >> (ms-access does NOT store those).
    >>
    >> So, you might have some success here..but, the numbers you have are too
    >> close to the limits of access....
    >>
    >> If you "many", or much of your data is padded with blank characters (ie:
    >> this is a fixed width file, not a text file with padding removed), then
    >> you
    >> likely can try this....
    >>
    >> If the data is not fixed length, and fields are not padded..then you not
    >> going anywhere with using ms-access in this case...
    >>
    >> I would suggest perhaps splitting up the data into 500 meg chunks (that
    >> would be 4 of them)......
    >>
    >>
    >> --
    >> Albert D. Kallal (Access MVP)
    >> Edmonton, Alberta Canada
    >>
    >>
    >>
    >>
     
    George Hepworth, Mar 21, 2007
    #10
  11. Not entirely true. A SINGLE Access mdb is limited to ~2 gb. However, Access
    permits linking of tables in other mdb files, tables in SQL Server, etc.,
    etc. Therefore, the practical limit on the amount of data a single Access
    mdb can manage is much larger.


    --
    George Hepworth
    2007 Access MVP
    "Vladimír Cvajniga" <> wrote in message
    news:%...
    > You' better try MySQL since MDB can't handle more than 2GB...
    > unfortunatelly. In this modern era I'd expect 20 GB, at least.
    >
    > Vlado
    >
    > "Sunil" <> píše v diskusním příspěvku
    > news:...
    >> Hi,
    >>
    >> i want to know how much data the Access can handle. I have a 2 GB flat
    >> file
    >> with which i want to do some manipulation of data and put it in to
    >> different
    >> Access Tables. I wan to know how much time it will take for Access to
    >> import
    >> the 2 GB flat file to a dummy table and do some manipulation and using
    >> some
    >> queries (ETL) store it in other 2-3 tables.
    >>
    >> Also, is any template is available for estimating the time and cost for
    >> doing Access Projects?
    >>
    >> Many thanks,
    >> Sunil
    >>

    >
     
    George Hepworth, Mar 21, 2007
    #11
  12. Guest

    Guest Guest

    Yes, that's true. I should have said:
    You'd better try MySQL since MDB can't store more than 2GB.

    Vlado
    "George Hepworth" <> píše v diskusním příspěvku
    news:OTD5$...
    > Not entirely true. A SINGLE Access mdb is limited to ~2 gb. However,
    > Access permits linking of tables in other mdb files, tables in SQL Server,
    > etc., etc. Therefore, the practical limit on the amount of data a single
    > Access mdb can manage is much larger.
    >
    >
    > --
    > George Hepworth
    > 2007 Access MVP
    > "Vladimír Cvajniga" <> wrote in message
    > news:%...
    >> You' better try MySQL since MDB can't handle more than 2GB...
    >> unfortunatelly. In this modern era I'd expect 20 GB, at least.
    >>
    >> Vlado
    >>
    >> "Sunil" <> píše v diskusním příspěvku
    >> news:...
    >>> Hi,
    >>>
    >>> i want to know how much data the Access can handle. I have a 2 GB flat
    >>> file
    >>> with which i want to do some manipulation of data and put it in to
    >>> different
    >>> Access Tables. I wan to know how much time it will take for Access to
    >>> import
    >>> the 2 GB flat file to a dummy table and do some manipulation and using
    >>> some
    >>> queries (ETL) store it in other 2-3 tables.
    >>>
    >>> Also, is any template is available for estimating the time and cost for
    >>> doing Access Projects?
    >>>
    >>> Many thanks,
    >>> Sunil
    >>>

    >>

    >
     
    Guest, Mar 21, 2007
    #12
  13. Sunil <> wrote in
    news::

    > "scubadiver" wrote:
    >
    >>
    >> I believe it is 2GB of memory but the amount of data depends on
    >> the size, number tables, fields, size of fields etc etc.

    >
    > Ok. This info is useful for me. Also i need to know how much time
    > it will take to import from a 2 GB flat file to a access table?


    A Jet MDB can not be larger than 2GBs.

    But if you are trying to import a 2GB flat file, it won't work. You
    should find a different database engine for storing your data. You
    can still create your application front end in Access, though.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    David W. Fenton, Mar 21, 2007
    #13
  14. Sunil <> wrote in
    news::

    > also is it possible for you to tell us how much time it will take
    > to import 2 GB flat file data (without any null) and with 10
    > colums (assume comma separeted file) in to a single table.


    It will probably take until about the time Hell freezes over.

    That is, I'm about 99% sure that you won't be able to.

    I would recommend you choose a different database engine for working
    with that amount of data. You can still use Access to create your
    application for working with and manipulating that data. It is very
    often the case that Access is used as front end to manipulate large
    quantities of data stored in server databases because it's so easy
    to do in Access.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    David W. Fenton, Mar 21, 2007
    #14
  15. On Wed, 21 Mar 2007 00:06:03 -0700, Sunil <> wrote:

    >Hi,
    >
    >i want to know how much data the Access can handle. I have a 2 GB flat file
    >with which i want to do some manipulation of data and put it in to different
    >Access Tables. I wan to know how much time it will take for Access to import
    >the 2 GB flat file to a dummy table and do some manipulation and using some
    >queries (ETL) store it in other 2-3 tables.
    >
    >Also, is any template is available for estimating the time and cost for
    >doing Access Projects?
    >
    >Many thanks,
    >Sunil


    As others have suggested, a single Access JET database will be very
    problematic. Depending on the nature of the manipulation, you could end up
    needing to store far more than the initial 2GByte.

    That doesn't rule out Access as a tool, though! You could use SQL/Server or
    MySQL as a data storage medium, with tables linked to an Access frontend.
    Access could import the text file into the server tables, and run queries
    against them.

    John W. Vinson [MVP]
     
    John W. Vinson, Mar 21, 2007
    #15
  16. Jack Jennings <> wrote:

    >I have an .mdb which has been adding 25Meg flat files each month since
    >200606, so is now up to 200Meg. The first 6 months it took under 1 min. The
    >last two months I noticed it takes longer to add the 25 Meg chunks (1-3
    >mins). By extrapolation and assuming load time/Meg increases with size, I
    >would estimate no less than 12mins/100Meg * 20 = 4 hours.


    Have you compacted that MDB after doing the file adds?

    You may already know this but I thought I'd mention it. The reason that it is taking
    longer is because the indexes on the table(s) are getting larger and larger. Every
    time you insert a record Access, or more properly Jet, has to insert the index values
    in sequence. One way of speeding this up may be to delete the indexes, do your
    insert and then recreate the indexes. Now this may not become time effective for a
    year or two or so.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
    Tony Toews [MVP], Mar 22, 2007
    #16
  17. Guest

    Guest Guest

    Thanks Guys, thank you very much for your prompt responses.

    As John suggested, the Db size will surely grow as the time goes. We are in
    a intial discussion of some project in that we will get 2GB file twice in a
    year. Also i agree that Access can be used as a front end application, but
    not surely as a data storage space.

    Once again thanks Guys. Have a gr8 day!!!

    "John W. Vinson" wrote:

    > On Wed, 21 Mar 2007 00:06:03 -0700, Sunil <> wrote:
    >
    > >Hi,
    > >
    > >i want to know how much data the Access can handle. I have a 2 GB flat file
    > >with which i want to do some manipulation of data and put it in to different
    > >Access Tables. I wan to know how much time it will take for Access to import
    > >the 2 GB flat file to a dummy table and do some manipulation and using some
    > >queries (ETL) store it in other 2-3 tables.
    > >
    > >Also, is any template is available for estimating the time and cost for
    > >doing Access Projects?
    > >
    > >Many thanks,
    > >Sunil

    >
    > As others have suggested, a single Access JET database will be very
    > problematic. Depending on the nature of the manipulation, you could end up
    > needing to store far more than the initial 2GByte.
    >
    > That doesn't rule out Access as a tool, though! You could use SQL/Server or
    > MySQL as a data storage medium, with tables linked to an Access frontend.
    > Access could import the text file into the server tables, and run queries
    > against them.
    >
    > John W. Vinson [MVP]
    >
     
    Guest, Mar 22, 2007
    #17
  18. "Tony Toews [MVP]" <> wrote in
    news::

    > The reason that it is taking
    > longer is because the indexes on the table(s) are getting larger
    > and larger. Every time you insert a record Access, or more
    > properly Jet, has to insert the index values in sequence. One way
    > of speeding this up may be to delete the indexes, do your insert
    > and then recreate the indexes. Now this may not become time
    > effective for a year or two or so.


    It might be a good idea to re-evaluate the indexing, and remove
    unnecessary indexes. If you've created relationships with other
    tables, there may be hidden indexes (creating a relationship creates
    a hidden index on the PK and FK in the tables in the relationship),
    and you could then delete the non-hidden duplicate index. Also, the
    advice on sparsely populated indexes (i.e., a small number of unique
    values in the field, and/or a majority of Null values) has always
    been that they don't give much benefit. My experience has been the
    opposite, that indexes on, say, Boolean fields have noticeable
    performance benefits. But the benefit may not be as great as for
    non-sparse indexes.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    David W. Fenton, Mar 22, 2007
    #18
  19. Vladimír Cvajniga <> wrote in
    news:#:

    > You' better try MySQL since MDB can't handle more than 2GB...
    > unfortunatelly. In this modern era I'd expect 20 GB, at least.


    Why a toy database like MySQL? Why not a real one, like SQL Server
    or PostgreSQL?

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    David W. Fenton, Mar 22, 2007
    #19
  20. MySQL is free!!! MS SQL Server is not free, haven't heard of PostgreSQL yet.

    V.
    "David W. Fenton" <> píse v diskusním príspevku
    news:Xns98FB68369595Df99a49ed1d0c49c5bbb2@127.0.0.1...
    > Vladimír Cvajniga <> wrote in
    > news:#:
    >
    >> You' better try MySQL since MDB can't handle more than 2GB...
    >> unfortunatelly. In this modern era I'd expect 20 GB, at least.

    >
    > Why a toy database like MySQL? Why not a real one, like SQL Server
    > or PostgreSQL?
    >
    > --
    > David W. Fenton http://www.dfenton.com/
    > usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    =?ISO-8859-1?Q?Vladim=EDr_Cvajniga?=, Mar 22, 2007
    #20
    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. Karl Burrows
    Replies:
    4
    Views:
    270
    Karl Burrows
    Jun 2, 2005
  2. Guest

    How much data can access database store?

    Guest, Jul 19, 2005, in forum: Microsoft Access
    Replies:
    2
    Views:
    3,095
    Albert D.Kallal
    Jul 19, 2005
  3. Arvin Meyer [MVP]

    Re: How much is too much--specifically for Arvin Meyer

    Arvin Meyer [MVP], Jan 25, 2007, in forum: Microsoft Access
    Replies:
    0
    Views:
    215
    Arvin Meyer [MVP]
    Jan 25, 2007
  4. Guest
    Replies:
    2
    Views:
    1,757
    John Vinson
    Jan 30, 2007
  5. Zoop
    Replies:
    16
    Views:
    7,865
    Douglas J. Steele
    Jun 30, 2008
Loading...

Share This Page