Records Limitations in Access 2003

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?
 
K

Kevin3NF

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

Larry Linson

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
 
T

Tony Toews

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
 
Top