Microsoft Access Team Blog : Access 2007 Limits

News

Tell us your story how Access has helped you pursue your potential or business‘s success.

Comments

Mike Alexander said:

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.

# June 5, 2006 9:48 PM

CyrusB said:

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!

# June 5, 2006 10:19 PM

Dan Dautrich said:

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?

# June 6, 2006 12:20 AM

Brian Pearson said:

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.

# June 6, 2006 5:55 AM

Jamie Baranec said:

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

# June 6, 2006 12:51 PM

Jimmie Whitaker said:

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. kpsklab@att.net
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.

# June 8, 2006 2:31 PM

DmitryKo said:

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,
http://blogs.msdn.com/access/archive/2005/10/13/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...

# June 9, 2006 2:50 AM

Francis said:

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.

# June 9, 2006 12:08 PM

Francis said:

Incidentally, the introduction a new file format (accdb), which will break compatibility anyway, would be the perfect opportunity to increase the above limits.

# June 9, 2006 2:47 PM

Erik Rucker said:

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.

# June 12, 2006 3:58 PM

zen said:

The new version of the JET engine (ACE) introduces some improvements in performances and/or stability?

# June 12, 2006 6:07 PM

Matt said:

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.

# June 12, 2006 6:38 PM

Clint Covington said:

Zen,
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 http://www.microsoft.com/sql/editions/express/features.mspx.

# June 13, 2006 2:32 AM

Stevbe said:

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?

# June 13, 2006 8:56 AM

Francis said:

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: http://www.filemaker.de/downloads/ pdf/specials/FM8_Reviewer_Guide.pdf

# June 13, 2006 11:48 AM

Francis said:

Correct link:
www.filemaker.com/downloads/pdf/revguide_fmp8.pdf

# June 13, 2006 11:52 AM

Jeff Coulson said:

Just a slow exhale in hopes that M. Alexanders predictions are wrong, although with his knowledge and experience compared to what I know, I am outclassed to challenge his opinion. I am just one of those "little guys" enjoying the challenges of a learning curve you can slowly win with practice in an application. Keep it alive Mr. Rucker.

# June 14, 2006 4:05 PM

Ken said:

Okay--I've finally got Office 2007 to install, I had big problems with it in the beginning but I've got it up and running. Click on the "Solution to office 2007 reinstallation problem" thread to see what I was going through:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.office.setup&lang=en&cr=US

Here are my comments so far:

3. The Nav panel is a pretty good idea except for one thing: If you have a database with lots of objects you have to scroll down vertically quite a way to get to everything, even with groups. Can we arrange the Icons in rows in the final build please? IOW, I would like the objects to flow in rows as we resize the nav panel to the right.

2. If we use "windowhide" to hide the what was the database window in the older versions, we lose the nav panel. No big whoop, because F11 brings it up, but it was kind of a pain in the neck to find that out.

3. In several places, the help mentions a "startup" option which I cannot find.

And finally:

4. "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!"

Hear Hear. I found out about this in the linked table wizard.

Still checking it out but am overall pleased.

Oh, one more thing: Can we change the size of icons on the ribbon? I want to make them smaller.

# June 15, 2006 9:12 AM

Sorting It All Out said:

A few days ago, Eric Rucker posted about the various limits in Access 2007 in this post, including my...

# June 25, 2006 4:11 PM

Chris Green said:

Hi

Perhaps Off Topic here but one of the limitations of Access 2003 adp is the inability to create queries in the same fashion as earlier versions of Access. i.e. queries of queries with form parameters. I'm just embarking on a project to update 20 Access 2000 db's to SQLServer 2000 with an adp front end and i would give a reasonably large appendage to be able to upsize these queries to stored procedures and a large appendage to be able to continue to write queries in the manner of 2000 and earlier and have them translated into stored procedures at the end of the process. If Access 2007 could do this you've got a convert.

# June 27, 2006 7:13 AM

Helen Feddema said:

Where is the object model diagram for the new Access DAO object model? I haven't been able to find one in Help for Access 2007.

# June 27, 2006 12:16 PM

John O'Quin said:

I am setting up using Access front-end to connect to Oracle server data. Cannot believe that there is no odbc to seamlessly translate Access SQL to Oracle, thus necessitating pass-thru qrys coded in Oracle !

Also would like to echo some of the other comments lamenting the fact that Microsoft is doing nothing to help Access any more. Access could be a really great front-end that us users would like to continue to leverage. I don't understand why MS is giving up on this product instead. If I have to go to a whole new product, why bother to stay with Microsoft?

# June 27, 2006 5:25 PM

John O'Quin said:

# June 27, 2006 5:25 PM

Dave Holbon said:

I’ve just started work on a project designed to run on a hand held PC or PDA using Windows CE, Visual Studio 2005 and a bunch of emulators for the hand held devices. I’ve been with Access since the first version and used ver 2 for many years.

Access has not progressed that much since ver 2 which was a great (and very quick) environment in which to work. Like Visual Basic which appears to have been discontinued in favor of what appears to me to be a bundle of plug-in modules sitting on top of another host of layered modules called .NET. This has not made life easier but harder and has “at a stroke” barred all but a few of the largest programming community that ever existed on the planet, from the game.

VB.NET and all the other “managed” languages are simple enough to use once you understand the underlying objects of which there are both too many and to few. All the old API calls so frowned upon in the past still have to used do to limitations in the .NET environment. Using the current version of Access (2003) is like using many different programs at once, it’s disjointed and clumsy. Access 2007 has gone some way to at least make it look like it’s the same program but woe betide anyone that opens more then two instances whilst coding and is also using Visual Studio 2005 in the background.

I think an opportunity has been missed with access; it’s the natural front end to SQL Server albeit a cut down version and could have been used as the middleware to glue some of the components in Visual Studio together or better still used as a tool for peer-to-peer databases distributed both locally and via the web.

Access 2007 has made some steps in this direction but I feel that it’s come to the end of its natural life and this means that there is an enormous hole in the market that no doubt some enterprising company will shortly fill.

# June 28, 2006 10:18 AM

Hans said:

I hope Access 2007 can support trigger.
Thanks.

# July 5, 2006 5:43 AM

Wolf said:

What's the point in restricting the width of forms and reports to 22 inches?

# August 4, 2006 3:50 AM

Phillip Fries said:

I have to say that the database features of Office are truely taking a dive on development. Not only does Access fail to coordinate its features/capacity with Excel, even MSQuery is still limited to 256 columns of data. Office is starting to look like a cheaply built house; a nice facade facing the street (Excel/Word), but austere construction behind and underneath (Access, MSQuery).

# August 11, 2006 11:28 AM

YisMan said:

I fully agree with the commentors here. Access 2007 has missed the boat. instead of patching the jet engine, MS should have built it to directly, default-ily connect to SQL Server Express/Regular.

One point no one seems to mention is why not also make VBA more like VB. why do we have to learn 2 environments? how many times do you press F11 to debug in Access or vice versa. Don't you wish you can dimension and initialize variables on the same line?

These, among other points mentioned in this blog, could have easily turned Access into an intermediate & powerful solution below VS, but easily upgraded to VS due to similiarities.

I must note, though, that there are several improvements along this line. Mainly docking and anchoring which make it much much easier to develop professional-looking apps, and "livelier" reports, something becoming pretty standard in business level programs.

# August 14, 2006 7:02 AM

Jake said:

Agree with all the above comments. Access has passed it's sell by date and is looking very aged underneath the cover. Two gigabytes of data limit is of use to no-one unless you are holding details of your small stamp collection. I paid £199 for a new stand alone version of access a couple of years ago. How anyone can justify this price tag now is beyond me. Rather than waste any more time on trying to 'bull up' new versions of access, just come clean and admit it is on the way out, giving current users plenty of time to migrate their systems.

# August 26, 2006 1:49 AM

Walker said:

I have a couple of technical questions that will help me decide if I am to continue plans to build a system in Access 2003 with SQLServer 2005 hosting the db which will be upgraded to Access 2007 once it becomes available.

I am putting this here as reading over the comments above I am becoming uncomfortable about proceding with my next project, but I have not been able to find antthing anywhere that address what are a few key issues for me.

Opinion are appreciated. But I would also like links to offical MS advise/recommendations as I have struggled to find any myself.

So here they are.

1. What is the storage size or footprint within the Access db of a linked table in terms of the 2gb limit for access objects. All I read in my investigation is that is is less than if the table was in Access itself. But I have not been able to find any specifics or the means to calculate it if it is dependancies on physical characterists of the linked table itself.

2. If I was, for example, to derive values based on what is stored in a link table during runtime, is it possible to do this on the SQL Server side and have Access just pick up the result, or do I have to do the calc in Access ?

3. How are Access limits effected by doing calcuation during run time vs having the values pre calcuated in the linked table ? That is, how does the increased size of the linked table effect performance and throughput ?

# September 2, 2006 2:39 AM

Chris said:

What about replication and particularly internet replication

# September 8, 2006 5:18 PM

Joel said:

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.

# September 27, 2006 3:37 PM

Mike said:

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.

# October 11, 2006 3:20 AM

Rick Weideman said:

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

# January 26, 2007 2:02 PM

Skip said:

Dissapointed to find out that sharepoint list do not support referential integrity... ?????

# January 27, 2007 12:28 PM

mk said:

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)

# February 5, 2007 3:20 PM

reinert pc tech» Blog Archive » Access 2007 Developer said:

PingBack from https://blogs.creighton.edu/tgh11568/?p=29

# June 15, 2007 1:48 PM

Dating said:

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

# May 31, 2008 7:46 AM

Weddings said:

# June 6, 2008 7:26 AM

New Comments to this post are disabled

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.
Table Limits
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.
Table size	2 gigabytes, less the space needed for system objects
Number of characters in a table name	64
Number of characters in a field name	64
Number of fields in a table	255
Number of open tables	2048 Note that this includes internal tables opened by Access.
Number of characters in a text field	255
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	1 gigabyte
Number of indexes on a table	32
Number of fields in an index or primary key	10
Number of characters in a validation rule	2048
Number of characters in a validation message	255
Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property is set to Yes.	4,000
Number of characters in a field property setting	255

Access Database Limits
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	64
Number of characters in a password	14
Number of characters in a user name or group name	20
Table Limits
Number of open tables	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.
Query Limits
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	32
Number of fields in a recordset returned by a query	255
Maximum recordset size	1 gigabyte
Sort limit	255 characters in one or more fields
Number of levels of nested queries	50
Number of characters in a cell in the query design grid	1,024
Number of characters in a parameter in a parameterized query	255
Number of ANDs in a WHERE or HAVING clause	99
Number of characters in a SQL statement	Approx 64,000
Form & Report Limits
Number of characters in a label	2,048
Number of characters in a text box	65,535
Form or report width	22 inches (55.87cm)
Section height	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	7
Number of fields or expressions that can be sorted or grouped in a report	10
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	65.535
Number of controls and sections added over the lifetime of a form / report	754
Number of characters in a SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control.	32,750
Macro & VBA Limits
Number of actions in a macro	999
Number of characters in a condition	255
Number of characters in a comment	255
Number of characters in an Action Argument	255
Number of modules (includes forms and reports with the HasModule property set to True)	1,000

Microsoft Access Team Blog

Recent Posts

Tags

News

Archives

Access 2007 Limits

Next Post

Comments

Mike Alexander said:

CyrusB said:

Dan Dautrich said:

Brian Pearson said:

Jamie Baranec said:

Jimmie Whitaker said:

DmitryKo said:

Francis said:

Francis said:

Erik Rucker said:

zen said:

Matt said:

Clint Covington said:

Stevbe said:

Francis said:

Francis said:

Jeff Coulson said:

Ken said:

Sorting It All Out said:

Chris Green said:

Helen Feddema said:

John O'Quin said:

John O'Quin said:

Dave Holbon said:

Hans said:

Wolf said:

Phillip Fries said:

YisMan said:

Jake said:

Walker said:

Chris said:

Joel said:

Mike said:

Rick Weideman said:

Skip said:

mk said:

reinert pc tech» Blog Archive » Access 2007 Developer said:

Dating said:

Weddings said: