What are the capabilities of Access?

J

Jenny1661

Hi,
I'm wondering what is the maximum space of Microsoft Access? ie How many
rows per database, and how many columns wide per database? Thanks!
 
K

Kevin3NF

No explicit number on row limit...size of the database is capped at 2GB, 255
fields in a table.

More specs available in Access help topic: specifications
 
J

Jenny1661

Yeah, I have seen that already, but I can't seem to locate the number of rows
per database in those numbers. I need to know because I have a excel and have
a database too large to fit in one tab. I want to purchase Access if it has
sufficient row space. Thanks again
 
J

John Vinson

Hi,
I'm wondering what is the maximum space of Microsoft Access? ie How many
rows per database, and how many columns wide per database? Thanks!
Click to expand...

If you have Access installed, look for help on Specifications.

There is no explicit limit on the number of rows; any single .mdb file
is limited to 2 GByte. A table can have no more than 255 fields (an
absurdly huge limit for any properly normalized table structure, 50
fields is a *very* wide table); more subtly, and sometimes more of a
real-life problem, you're limited to 2000 characters *actually used*
in any single record. This can be nasty because you can easily create
a Table with 20 fields, each a 255-byte Text field; happily put data
into it for months; and then get the error when some verbose date
actually *uses* all the space in those fields.

In practice, I know of Access databases handling upwards of 10,000,000
rows in the largest table. A million or so is not uncommon.

John W. Vinson[MVP]
 
K

Kevin3NF

millions....way more than Excel will ever dream of holding

There is no hard coded limit
 
J

John Vinson

Yeah, I have seen that already, but I can't seem to locate the number of rows
per database in those numbers. I need to know because I have a excel and have
a database too large to fit in one tab. I want to purchase Access if it has
sufficient row space. Thanks again
Click to expand...

Access can hold a thousand times as many records as an Excel
spreadsheet, more or less depending on total database size.

BUT!!!!

Access is emphatically *not* Excel on Steroids. Excel is a
spreadsheet, the best of the genre; Access is a relational database.
THEY ARE DIFFERENT and require different logic and different design.
If your only purpose for moving to Access is to get a bigger
spreadsheet, you'll be very frustrated. If you recast your application
as a properly designed, properly normalized database application,
you'll likely be pleased - but it will be a considerable amount of
work.

John W. Vinson[MVP]
 
S

Steve Schapel

Jenny,

I agree with John here. You need to work out whether you need a
spreadsheet or a database. If it is database functionality that you
need, then Access is the way to go. If it turns out that it is actually
spreadsheet functionality that you need, something to take into account
is that Excel 2007 is just around the corner, with provision for a
million rows per worksheet.
 
D

Dirk Goldgar

John Vinson said:
In practice, I know of Access databases handling upwards of 10,000,000
rows in the largest table. A million or so is not uncommon.
Click to expand...

I'm working on one right now with over 15.5 million rows in the most
detailed table.
 
H

Harry Cederbaum

Hi Steve:

Maybe you or anybody else can help me.

I am using MSAccess 2003 as a front end to view the database tables of an
online MS SQL Server application. I know from the application's
administrative interface that I have over 13,000 records in the particular
table I'm wanting to see, but only 10,000 show up in Access.

From the discussion below I see that MSA supports hundreds of thousdands of
rows. Could you advise why I might only be seeing EXACTLY 10,000?

Thank you.
 
T

Tony Toews [MVP]

Harry Cederbaum said:
From the discussion below I see that MSA supports hundreds of thousdands of
rows. Could you advise why I might only be seeing EXACTLY 10,000?
Click to expand...

Access will only fetch the first 10,000 records by default when in
datasheet view. What happens when you move to the bottom of the
datasheet view and Page Down? I suspect you'll see an additional
10,000 (or fewer) records.

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
 
A

Aaron Kempf

no this is the OPTIONAL record limit thing in ADP


a) in the record navigation bar-- the button all the way to the right will
let you choose a different record count for a particular table / form /
query

b) you can set the default (which I believe also resets this values for
existing forms and tables)

c) you can do this programmatically in SQL Server 'SET ROWCOUNT 20000' if
you want
 
Top