What are the capabilities of Access?

[Guest]

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!

 

[Brendan Reynolds]

Here's a link to the on-line help topic on 'Access Specifications'.

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051868081033

 

[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

 

[Granny Spitz via AccessMonster.com]

I'm wondering what is the maximum space of Microsoft Access?

Assuming you're using *my* version of Access you can fit 2 GB. Since yours
is a secret it's hard to tell if yours might fit the same amount but it
probably does.

How many
rows per database

As many rows as you can cram in 2 GB. Big rows, fewer rows. Small rows,
more rows.

how many columns wide per database?

255 columns per table. Up to 32,768 objects per database and some of those
objects can be tables.

 

[Guest]

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

 

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

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]

 

[Kevin3NF]

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

There is no hard coded limit

 

[Brendan Reynolds]

There is no restriction on the number of rows.

 

[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

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]

 

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

 

[Dirk Goldgar]

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.

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

 

[Guest]

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.

 

[Tony Toews [MVP]]

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?

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

 

[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