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.
There seems to be little to no expansion of the limits of any one component part. I have to say that I'm a little disappointed at the stagnation of Access in the light of the stunning new developments of the other apps in the office suite. Other than a few new templates and some cosmetic improvments, I see little new. I know...I know, SharePoint integeration. The reality is that SharePoint is not all that widely available to the majority of Access users. Unless you're in the IT department, you will likely not be able to implement SharePoint solutions. And if you are in the IT department, you probably frown on Access anyway (I speak from many years of experience trying to convince IT managers that Access is not a toy application). On top of stagnation, the SQL server development team is no longer going to support Jet. Access will have its own version of the Jet database engine. This move, coupled with open commercial licensing for SQL Express, Visual Studio Express, and other XML based technologies, essentially knocks Access down a few rungs on the food chain. Many Access developers fear that this is a demotion that puts Access on the slow train towards the “unsupported land”. Now before I get berated for fear mongering. I know that Access will continue to exist for many years down the road. However, Access developers have always had to fight the notion that Access is a toy application; not suitable for full scale development. This new move doesn’t make things any easier for Access developers. In my mind, there were three good reasons to use Access: 1: Volume of data
2: The need for a relational database
3: The need for a small applications that can be turned around quickly Let’s see what’s new in 2007: 1: Excel now has the capacity to hold 1M rows and over 16K columns. 2: SQL Server Express, and Visual Studio Expres are now available for commercial use. This gives developers more robust choices for both application developement and backend database. 3: Analysis Services is now built into Excel giving users direct access to the ultimate relational database - SQL server. Not to mention easy to use connectivity to other OLTPs. In the future, it will make far more sense to create a robust Visual Studio/SQL2005/Excel application than to create an Access application. I still believe that Access still makes sense for small organizations who don’t have the resources or the development skills for these more robust tools, but in the end, things don’t look good for Access' future prospects. Every year Access, looks older and older. While other technologies get a breath of life and a new face, Access gets a few new templates and a blog that is barely kept up to date. My prediction: Access will be taken out of the Office Suite a few versions down the road; to replaced by SQL Server Express or some new hybrid app.
A couple of thoughts with the Beta 2 Navigation Pane: * In prev versions of Access, you could have the database window display in detail view which would let you see created date, comments, etc for each item in the list. In the Beta I couldn't find a way to do this. It is very useful to see a list of each object with its date created and comments. * To create a new database object, I instinctively right clicked an existing object (e.g. a table), but the context menu did not have an option to create a new object of that type. This seems counter-intuitive. * The nav bar view itself is cumbersome if I am wanting to see all objects in my database. While I can have it display objects by type in a stacked list, in reality this type of interface is a pain to use. You have to expand/collapse each object category and scroll up/down, etc. Tabs are much more efficient for this type of navigation. Other first impressions: Everything looks graphically sweet (really, really nice) to start off but it is not long until you open a dialog or wizard, etc and you get some old style battleship grey form appear (aka "a video nasty"). This isn't just OS dependent system message boxes, but whole areas of functionality. This is not just limited to Access either. Even though the functionality may be rock solid, the sad thing is, it gives an overall impression of tacky and half done. And, unfortunately, perceptions frequently matter more than fact!
I ran into a limit late last week with my VBA code in a small project I've been working on... after about 1500 lines in a single procedure, Access started pouting "Procedure too long"! What's the official maximum length?
Just a small aside on maximum procedure length ... I've recently been converting a large Access 2 application to Access 2003 and have encountered the 'Procedure too long' message after converting. Though I've never seen it stated what the maximum procedure length is, it does seem that it got reduced somewhere between A2 and A2k. Thanks for the blog: I like the sound of almost everything I've read about Access 2007.
Hi I was wondering, since the Access 2007 table limits will stay at 255 fields, how will Access 2007 will handle the import of a large 16K columns Excel 2007 spreadsheet? Having Access break the import file into multiple tables that I could link later would work for me. Thanks
I agree with the tabbed format for database objects is better. The big view area at top where you have to hit ctl f1 to hide it is a pane. When I develop an access application, the user only needs a menu or switchboard and the forms for interface. Is there a way to auto hide this on startup? Someone please emaim me and let me know. firstname.lastname@example.org Also, I'm glad to see DAO is still available. DAO is very important to smaller networked databases. ADO is more for larger client/server apps. DAO is very efficient when used where it was intended. PLease please Microsoft, always keep DAO. Aand to conclude, I like the backward compatibility, I'd hate to have to redo thousands of lines of code.
If these limits did not increase with the new .accdb format, what are the benefits of using it? As for .NET 2.0 + SQL Server vs Access, this actually mirrors an older discussion in this blog, blogs.msdn.com/.../480870.aspx I agree Access could have been evolved to be more of a frontend and middleware level for SQL Server, even though I realize that the ability to deploy applications by providing a single file is super important for many people (including myself), so JET Red has to stay. It would have been easier if Access folks didn't have to spawn yet another private version though. What I'd appreciate is full interoperability and data interchange between SQL Server and Access. SQL Server lacks some features present in JET engine (query processor, file attachment and OLE data types etc), so there should be a kind of "JET middleware" layer to work around it. That way, you'll get the robustness and scalability of of SQL Server with the ease of deployement provided by .MDB/.ACCDB file. The latter could just become a "database package" format: you develop your application within Access as usual, but if you need to scale it, you just move your data to a SQL Server store with a click of a button. And when you need to move the data or redeploy it in another location, you just send it back to Access file (provided the limits are not excluded) or maybe some other archival format. BTW, OLE DB is the preferred method of accessing databases in Office suite as of now, yet it doesn't support some key Access features like user functions in queries. Which means I need to use DDE for Word Mail merge and it doesn't seem to work in Office 2007. How come that Access ODBC driver does not support a full feature set? This doesn't really live up to the claims...
Access and Excel 2007 form an inverted Venn diagram: their lack of feature overlap leaves many users in the lurch. I need a file database for data crunching, but the 255 field constraint, among others, renders Access useless. Excel 2007 corrects this limitation (with 16k columns), yet, despite filters, it has neither the query nor join functionality I need. Would using SQL Server Express as the backend for Access solve this? Or perhaps switching to Paradox? It is a shame that MS is sticking with obsolete technology.
Incidentally, the introduction a new file format (accdb), which will break compatibility anyway, would be the perfect opportunity to increase the above limits.
I'm checking on the proceedure limit in VBA, I'll let you know when I get it. There are several posts that come down to what happens with really wide tables (e.g. from Excel) in Access. Access displays a alert then can import the first 255 cols. Yes, we realize that there are situations where this is a problem, but we just weren't able to address it this time. One thing to keep in mind, though, is that because Excel is not relational, data files are architected very differently between the 2 apps. XL files tend to be very wide because you need to separate out all repeating data into columns (e.g. January, February, etc.). In Access you'd normalize the data and have longer & skinnier tables than you see in Excel. We'd like to do more work to help you get from one to the other, but in general super-wide tables aren't as appropriate for relational databases as they are for flat files. What's new in .accdb file format? The primary change is support for complex data, which you can see in the post from October 13th. There are a number of other smaller features, but complex data is the main one. We looked at increasing the limits this time because as Francis notes, we had the file format change anyway, but didn't have the time to take that on. We certainly understand the desire, though, and will look to this for the future.
The new version of the JET engine (ACE) introduces some improvements in performances and/or stability?
Any chance we can get Access 2007 to have a bigger database size limit? 2 GB doesn't seem to cut it anymore with the large volumes of data. It would be helpful to increase the limit to say, 5 GB so I don't need to split my access databases into two when I reach the 2 GB limit.
We have spent a fairly large amount of time in ACE stability and looking at perf. We are interested in your feedback in this area as you get a chance to experience the product. Matt,
Like Erik said above--this is something we wanted to do but didn't get done. You might want to look at SQL Server 2005 Express as it is free and supports 4 gigs www.microsoft.com/.../features.mspx.
What's new in .accdb file format ... The primary change is support for complex data ... ugh ... you create a new of version of Jet to make things work with Share Point but you won't tell us if we can distribute it with runtime ... apparently this is not your choice and is up to marketing. Why didn't you just upgrade Jet instead of creating a seperate engine?
Thanks for the response, Erik! I would like to point out, though, that normalization is not always possible, e.g. for survey data. These data are generally stored in flat structure. There may be hundreds, if not thousands of variables (fields), for each case (record). These fields can only be pared down to <=255 after exploratory analysis (looking for relationships) has been completed. It's a chicken-and-egg problem: [irrelevant] data can't be chucked until after it's been checked for relationships, but it can't be checked until after a lot of it has been chucked. Matt, you might want to look at FileMaker Pro 8. It can handle up to 8 terabytes databases (with 256 million fields per record.) Specs on page 54 of: www.filemaker.de/downloads pdf/specials/FM8_Reviewer_Guide.pdf