"The preferred way to connect to SQL Server is MDB file format or ACCDB file format. This enables you to use the full flexibility of local tables and local queries, while leveraging the full power of SQL Server. In addition, MDB and ACCDB files link to multiple SQL Servers and a wide variety of other data sources. "

Obviously, Microsoft continues to see Access primarily as user productivity tool, used by a business analyst who is writing his queries in Access, easily combining data sources from all directions and getting valuable business results.

Such rosy picture might be good for a TV commercial, but has nothing in common with the reality.

Real analysts don't use Access ever. They don't even think about it. Maybe one in several thousand, but all others work in Excel. Have been doing it for decades. Microsoft should have had enough time to notice.

Analysts, further, don't write sophisticated queries even from one datasource, not to mention "multiple SQL Servers and a wide variety of other data sources" - because (1) they can't (2) they don't know the layout of the data (3) they could easily load the server with a query that would last for one week, (4-103) 100 other reasons that would be obvious to anybody who spent 1 hour in any company with a computer. Half of these reasons also explain why not only analysts, but all others also don't write queries from "wide variety of other data sources".

Access, however is very effectively used by professional programmers who are able to create data-driven forms and reports in fraction of time that would be required for doing that in Visual Studio (especially "powered" by Crystal Reports that have become the classic example of instability and bad design).

These professional programmers do see lots of benefits in ADP compared to MDB, but the ability to design database objects right from Access is not in the first hundred of them. Maybe in the second.

It's sad and quite remarkable to see the level to which Microsoft has become disconnected from the needs and preferences of the very audience that 20 years ago made it #1 - the developers; it increasingly looks like it's now taking the information from its own magazine ads - and what's most amazing, it actually believes them.

(Perhaps due to cut & paste issues, the formatting below is a bit "off" and missing paragraph breaks no matter how I try to fix it...seems this editor needs some debugging!)

As Vadim mentioned in his comment, Microsoft seems sadly disconnected on how ADPs are used in real-world scenarios. While local caching of tables can certainly be a huge benefit in the right scenarios, it's not at the top of my "needs" list and could be implemented even in an ADP in a slightly roundabout fashion (either in-memory caching or programmatic linking to an MDB or similar).

The benefits of having an ADP that behaves just as though the SQL Server side and the GUI side were all one is enormous. The fact that I don't have to worry about forgetting to link a newly created object, and spend time writing code to handle any required linking, unlinking, etc., can't be overstated.

But let's say for a moment that I wanted to work with a local database and use linking. How does ACCDB provide me any useful tools in that regard...or any other?!? Let's see:

• Complex data (multi-valued data types)
• Generally not a good design, but it's a "nice to have", I suppose. I'm not sure how useful it would be in the long run, though, as you then have to extract the data again to do whatever it is you want to do with it. More often than not, I would think that leaving separate fields as separate fields rather than grouping them together into one would be a better approach.
• Attachment Date type
• Typo much? (Should be "data".)
• The Attachment data type is an interesting idea, but I suspect it will have limited use. A Memo or Object field could already be used with a little work to achieve something similar and for large databases, the 2 Gig file size limit for an entire database may be prohibitive once you start adding "attachments" all over the place. As has always been the case, you may end up preferring to implement some kind of external file management scheme rather than "attaching" files internally.
• Append Only Memo fields
• This is useful how? And why aren't you implementing this sort of thing in the business- or front-end? Back-end data should always be fully editable by a developer in my concept of what a database is, with the possible exception of keys and timestamps and such.
• Compressed image storage for any Picture property
• This means what?
• Assuming this means internal compression of bitmaps, etc., this is nice, but unlikely to be a major issue in most databases.
• E-mail database as attachment
• Wow! Really?!? Cuz I couldn't exit the database and e-mail it before now...honest. MS built it into a menu item...that must have been SUCH a challenge. Sure, it's convenient in something like Excel and Word, but in an Access database? And let's not even get into the Outlook security issues around this.
• <various things> to Office SharePoint Server 2007
• I've never worked in an office that actually USED SharePoint Server, so naturally, the supposed benefits here make me yawn.
• Linked Tables to files in ACCDB format
• And the point is? I can't imagine this ever being significantly useful.
• Encrypt with database password
• This is new in what way?!? There are some slight differences in approach here, and the encryption is stronger, but in terms of overall concept, there's really nothing new here at all.

So all in all, ACCDB provides WHAT benefits, exactly? And just who are these people who prefer ACCDB/MDB format for accessing SQL Server data? Not me, that's for sure!

In my role as an analyst, I find Access to be indispensible. I typically write several ad-hoc queries for business intelligence per week, and could not do it without Access as my data store.

There is no possible way to do any real, enterprise analytics in Excel. Can someone show me how am I supposed to take the average of over 10 million records in Excel? Better yet, tell me how to do that 7 times for 7 different fields, to get me the data I'm really looking for? Oh, while you're at it, and lets also make that an automated process, so it can take place at 2am, since I don't have time in my workday to perform that kind of calculation, without dipping into VBA code.

If you are using Excel for analytics, that is only because you are lucky enough to have such a small amount of data for manipulation that it fits in the 64K cells alloted to a single excel worksheet. That is not the case in Enterprise level reporting and analytics.

I felt the comments here were too limited to personal experience in their scope. For the benefit of others, I simply wanted to offer a different perspective. MS gets a bad wrap in a lot of instances, and I think this is one.

First, I totally agree with the flexibility of MDB files. Its the first time I see Microsoft regard the MDB as preferable to ADP. The use of local access table is a major tool when developing an un-bound application.

I have an application originally written in Access 2.0, now converted to Access97
that supports both access back end or sql server (2000) back end with virtually no change to queries
and VBA code.

The major issue that was not covered in this article is the fact that ACE (the new Jet) doesn't support native 64 bit. The implications are unclear because on one hand the Jet is a so to speak a depricated technology. On the other hand Access seems to continue to be part of the current and future Office suites.

.Net isn't really an alternative these days having only a partial and problematic support for integrating with Office / COM.