Records Limitations in Access 2003

  • Thread starter Thread starter NKK
  • Start date Start date
N

NKK

Can anyone tell me how many records a single table will
support in Access 2003? I know FoxPro claims 1 billion
records. However I don't see anything on Access 2003,
except it is limited to 2 GIGS. If I had a 100 million
records in a table, they are 15 numbers, would access
function? And how long would someone imagine it would
take to search. I have complicated how I could populate a
table with that much data? Any suggestions there?
 
No explicit limit, other than the file size you already know about.

100,000,000 rows is going to blow Access out of the water. It's too late
for me to do the math, but a long INT field is 4 bytes, and a double is 8.
Max size of a row is 2000 bytes.

Searching here is going to be helped by proper indexing, which we cannot
tell you much about without knowing your data.

Move it to SQL Server if you can.
 
Kevin3NF,

Thanks for the response....

I have done a little calculation based on your reply.

If I have 100,000,000 records. Each record is only one field which is also
the index. I would have to use the double type because the string will be
10 characters (which will be all numbers). I really don't expect more than
75 million possibilities which are records. But at 100 million the database
would be sitting at 800 MB well under the 2 Gigs. Do you think that would
work. I have to consider the indexing as you mentioned because if the
program comes up with a duplicate I want it to spit out the random serious
and not store the value. I am having a really difficult figuring how I
would export a list of values that long. I can write a quick VB.NET program
to populate the database. But I am concerned about how I will export it to
something so I can test an import on let's say 50 million records. I have
actually talked to someone who has 50 million plus in a flat-text file.
However he is out of the country until January 5th. I was hoping to work on
this over the next few days. Maybe I can find someone else who has a file
that size that would be willing to let me test with it. I made one from
Excel copy the max number of rows and generated a file that had about
788,000 of these strings and it worked kind of slow. It took 2 hours to
import it, but I was using Access 2000. So I don't know if the engine used
in Access 2003 is any different but I think I will try.

Thanks again.
 
I'm wondering to what use you would put a table with only one field, of that
size. I don't argue that you don't have a valid need, but my imagination
must be limited, as I can't figure what it would be.

If you can share with us what the requirements are, maybe someone can
suggest an alternative to this structure (but, maybe not, too).

The index will take as much space as the record, and I'd guess more (the
detail structure of index isn't published by Microsoft, but it's clearly
going to have pointers in addition to the data itself).

And, there's nothing that prevents you from using the numeric string in the
field, and as an indexed field.

Larry Linson
Microsoft Access MVP
 
Larry Linson said:
The index will take as much space as the record, and I'd guess more
Click to expand...

Not necessarily. You'd think the index, in this situation would be
the same size as the table as it's a one table field plus four bytes
per record. Or same size plus 400 Mb. Who knows about overhead.

However it is possible that the entire key is not stored with each
index. That is when going down the binary tree structure looking for
a given value possibly the duplicate part of the key is only retained
"higher up" in the binary tree.

That is if you have 12345678 and 12345679 possibly the 12345678 is
stored and then a token indicated 7 bytes from the previous index
entry and then the 9.

I must admit I'm not sure I'm making sense here. <smile> Furthermore
I doubt this is how it's done but it's a possibility.

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
 
Back
Top