How much data Access can handle?

[Sunil]

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

 

[Wayne-I-M]

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

 

[scubadiver]

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]

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?

 

[Albert D. Kallal]

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)......

 

[Sunil]

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

 

[Douglas J. Steele]

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.

 

[Jack Jennings]

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.

 

[Vladimír Cvajniga]

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

 

[George Hepworth]

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]

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.

 

[Vladimír Cvajniga]

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

Vlado

 

[David W. Fenton]

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]

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.

 

[John W. Vinson]

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]

 

[Tony Toews [MVP]]

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

 

[Sunil]

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!!!

 

[David W. Fenton]

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]

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?

 

[Vladimír Cvajniga]

MySQL is free!!! MS SQL Server is not free, haven't heard of PostgreSQL yet.

V.