You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
This is a quick post to lay out the limits in Access 2007 and make a little clearer how the application works and how it scales. The first table below shows the limits of the database engine itself.
Access Database Limits
Access database (*.mdb or *.accdb) file size
2 gigabytes, including all objects in the database (data, forms, reports, indices, macros, modules, etc.)
Total number of concurrent users
255. Note that the practical limit will likely be lower than this based on database design.
Number of tables
This is governed by a limit on the number of objects in the database, which includes tables, forms, reports, queries, etc. The limit on DB objects is 32,768.
2 gigabytes, less the space needed for system objects
Number of characters in a table name
Number of characters in a field name
Number of fields in a table
Number of open tables
2048 Note that this includes internal tables opened by Access.
Number of characters in a text field
Number of characters in a memo field
65,535 when text is entered through the UI
1 gigabyte if text is entered programmatically
Size of an OLE Object field
Number of indexes on a table
Number of fields in an index or primary key
Number of characters in a validation rule
Number of characters in a validation message
Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property is set to Yes.
Number of characters in a field property setting
In addition, Access applications themselves have a number of limits, including:
Number of objects in a database
32,768 Note that this includes tables, forms, reports, queries, macros, modules, indices, and internal objects used by Access.
Number of characters in an object name
Number of characters in a password
Number of characters in a user name or group name
2048 Note that this includes linked tables as well as local ones, and that the number may be somewhat lower due to internal tables opened by Access.
Number of enforced relationships
32 per table, minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query
Number of fields in a recordset returned by a query
Maximum recordset size
255 characters in one or more fields
Number of levels of nested queries
Number of characters in a cell in the query design grid
Number of characters in a parameter in a parameterized query
Number of ANDs in a WHERE or HAVING clause
Number of characters in a SQL statement
Form & Report Limits
Number of characters in a label
Number of characters in a text box
Form or report width
22 inches (55.87cm)
Height of all sections, plus section headers in design view.
200 inches (508 cm) Note an actual report can be arbitrarily long once the data has expanded.
Number of levels of nested forms or reports
Number of fields or expressions that can be sorted or grouped in a report
Number of headers or footers in a report
1 report header / footer
1 page header / footer
10 section headers / footers
Number of printed pages in a report
Number of controls and sections added over the lifetime of a form / report
Number of characters in a SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control.
Macro & VBA Limits
Number of actions in a macro
Number of characters in a condition
Number of characters in a comment
Number of characters in an Action Argument
Number of modules (includes forms and reports with the HasModule property set to True)
The next post will be on Report View, and cover sort, filter, and group functionality from that view.
What about Access PROJECTS (.adp)? Can they work with SQL Server 2005? Access 2003 Projects can no longer use Access designers on SQL 2005 databases, and apparently never will be able to (any more than you can use Access 2000 to work with SQL Server 2000 in this regard: you either have to use Access 2002/XP or 2003). I know that SQL Server 2005 Management Studio is preferred, but there are still things that are much easier to do in Access Projects. For instance, suppose I have a Table that is identical to a new Table I need, except for a field or two. In Access, I simply Copy and Paste, and the Paste dialog asks me if I want to copy the Structure only, Structure and Data, or Data into another existing Table. I say Structure, and voila! I have a new Table with the same structure as the existing one, complete with all Extended Properties set up for things such as Lookups (dropdowns) even in Datasheet view (reduces the need to develop Forms for data entry), checkboxes for Bit fields, Sub-Datasheets, etc. I simply make whatever changes are needed in the new Table! Do you have any idea how many hoops one has to jump through to do the same thing in SQL Server 2005 Management Studio, either Express or full version? I tried to do it, and the resulting Table seems to be okay in SQL Server, but in Access (2003) cannot even be opened in Datasheet mode! Attempts to do so displays “The Stored Procedure has executed successfully, but returned no records.” — say WHAT!? It isn’t even a Stored Procedure! It’s a friggin’ TABLE!! I tried to test this using Test Drive, but the Test Drive copy of Access didn’t seem to have Projects, or maybe had them disabled for security reasons. Doing a File / New would only allow the creation of a blank Access 2007 (Jet) Database, not a Project, even if I tried to force the “.adp” extension on the filename. Before I spend hours downloading the demo (not to mention going through the rigamarole to pay the $1.50 service fee) or $4.95 to order the CD, I’d like a simple, straightforward answer to this question.
Eric, The limits you post DO NOT APPLY to tables stored in SharePoint. Please post the limts (number and data type of columns) for SharePoint storage. For example you can have a maximum of 16 dates, 16 yes/no (bit), columns, etc. when Access is used with SharePoint.
I was hoping that ACCESS 2007 would be written in 64 bit so databases could be larger than 2GB. I guess that was wishful thinking. If SQL was easier to use we wouldn't need for ACCESS to do it. As it is, for anyone with my same problems, linking to the txt flat file has afforded me some extra life on ACCESS. Please come out with a 64bit version of ACCESS.
PS. Improving the ACCESS help feature was important. thx
Dissapointed to find out that sharepoint list do not support referential integrity... ?????
There is one reason why I dread the thought of Access going south to be replaced by a VB express/SQL express package. Continuous forms, I repeat, continuous forms. You never know just how awesome those are until you have tried to hack a datagrid in vb.net. Datagrids bite. Even with the lack of any truly shiny new features, Access is the best database app available with the exception of Oracle Developer (which is quite a bit more expensive)
I wasn't able to find this connected on any of the Office Support dialogues, but I came to a dead end on the database size and got this error message trying to run a query: "Cannot open database 'yaddayadda'. It may not be a database that your application recognizes, or the file may be corrupt"...which is crap, its not corrupt, just too big.
I have been bit by the right click of death when in the properties window. Worst yet is the fact that save on compile is gone. I have lost more work with 2007 than any other. So I have to remember to save before I run. It seems to me you guys are going backward. Please feel free to let me know how I can autosave on compile. Which used to be a feature when the program didn't hang on demand...