Is there a maximum size for Access tables?

[Harr777]

Hello,

Quick question: Is there a maximum size for Access tables?

 

[clickster]

I know there is a maximum of 255 columns. I'm not sure about rows, but I wouldn't be surprised if it were the same as an Excel spreadsheet

 

[WildHare]

There is no practical limit to the number of rows in a TABLE, per se, but there is a 2GB limit to the total size of an Access MDB file.

If it gets that large, you're probably using the wrong product...

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[lbpsfl]

I haven't been able to make a table with more than 64K rows.

 

[Harr777]

Thanks for the input, everyone. Just one last thing. Does anyone know at least a ball park figure for the following:
Lets's say you had a table with 16 columns. How many rows with it take to get to 2GB. I'm sure that it's alot, but I wonder if someone has a ball park.

Thanks

 

[ploeryan]

Hi all,

I have some databases which I have inherited. There are a few tables with over 25,000,000 records (rows)in them. These tables work fine but are quite slow to access (probably more of a networking issue). I am in the process of cleansing them at the moment.

Keep smiling

 

[WildHare]

LBPSFL, there is something wrong if you can not get more than 64K records in a table..it's definitely not an Access limitation.

Harr777, the space a record takes up is more a function of the data types of its constituent fields. A record with 5 10-character text fields will take up less space than a record with 5 currency or date/time data types, and far less than one with 5 double-precision values. (Assuming all five fields have valid data)

General rules:
1) Access does NOT pad with blanks, so even if you define a text field as 255 characters in length, only the actual data is stored. IOW, Jet uses variable length records.
2) Double precision numbers use a 'relatively' large amount of storage - don't use them unless you really need their precision.
3) Remember the BYTE data type for any numeric that will always be less than 256 - it's only 1 byte.

JMH


If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[rdodge]

Also, remember, there is a 2000 character size limit per record excluding Memo and OLE Object fields.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[WildHare]

Ron, I'm not so sure this is true anymore. I just created a sample table with 12 255-character text fields and filled each field with all 255 characters, and then copy/pasted until I had about 25,000 records, and everything seemed fine...

By my calcs, each record is 3060 bytes in length, before system overhead.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[rdodge]

That's what it stated according to the Specification help file in Access 2002.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[WildHare]

Odd. I have 2K here at home, and found no such reference. I'll check at work where we have 2K2...although it would seem that my experimentation has shown such a limit is incorrect in the Help file...

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm