Access 2007 Limits

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

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.

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