MVP’s Allen Browne’s review and resource page on Access 2007

Allen Browne has put together a nice resource page on Access 2007. I’m not sure I completely agree with the statement about too buggy for serious use–guess that depends on your definition of serious use. As with any major release there are some bugs that slipped through (the team will focus on fixing the critical issues in SP 1) but I feel pretty good about the “What’s fixed” list. Most of those issues have been a pain in the side of Access developers for a very long time. Personally, I find it painful to use Access 2003 after spending so much time using the new functionality. 

 “The most significant release in 12 years, Access 2007 introduces major new functionality in both the engine and the interface. Seasoned developers will discover new things are possible, and casual users will find more things within their grasp.

With so much new stuff, it is no surprise that the new version has many bugs. It will be a great product, but the initial release is too buggy for serious use.”

Here is my favorite section:

Issue Solution
Security dialogs

Every time you opened your database, Access 2003 tried to convince you that was a bad thing. Access 2007 solves this by introducing Trusted Locations (Office Button | Access Options | Trust Center | Trust Center Settings.)

Mouse Wheel

Developers often coded to disable the mouse wheel so it would not scroll records in Form view. Access 2007 scrolls in Datasheet and Continuous view, but not form view. Use the new Mouse Wheel event if you want the old behavior.

Email in Hyperlinks Previous versions prefixed “http://” to all hyperlink fields. Access 2007 recognises email addresses, and prepends “mailto:”
Percent format If you type 5 into a field formatted as “Percent”, Access 2007 understands it as 5%. Previous versions interpreted it as 500%. (This workaround is no longer required.)
Default Value (number fields) You no longer have to remove that annoying zero Access used to assign to the Default Value property of every numeric field you created.
Reserved field names Access 2007 recognises the most common field names that cause problems (e.g. Name, Date, Month, and Year), and warns you (in table design.) It responds to only a very few reserved names, so you still need this utility or list.
Picture + Caption Display both a Picture and a Caption on command buttons and tab controls (but not toggle buttons.)
Autofill (datasheets) Previous versions attempted to guess the number you wanted when entering data in a datasheet. This annoying behavior has been removed.
Imports Several issues solved, such as the ability to choose columns and specify data types when importing from Excel.
Internal margins Scroll bars now work for controls with internal margins, and size-to-fit correctly. Controls now have padding as well as margins.
Filters Several filter bugs fixed.

Updated post with whats fixed information 1/20/2007.

Comments (7)

  1. What about replication with MDBs created in A2K to A2K3 format? Reports are that DAO synchronization fails, and that the only workaround is using JRO instead. MS has been deprecating ADO and its ugly step-children for work with Jet data for quite some time (and JRO is definitly one of ADO’s more disappointing step-children), so now all of us who stuck with DAO (on the theory that proved correct, that it was a better interface to Jet data than the newer interfaces) are going to have to rewrite our replication code to work with A2K7 or convince our clients not to upgrade.

    Did anyone test replication in A2K7? It would seem that they did not, or they would have discovered such a serious failure of A2K7.

    David W. Fenton

    David Fenton Associates

  2. As mentioned in an earlier post, I left the Access team several months ago. I’m not familiar with this area and the person who owns it is OOF until the 29th. I will get back to you when I find out more information.

  3. grovelli says:

    Phew, that was a close one; I’m glad I used JRO for my replication code in Access 2003. 🙂

  4. Janet Chisholm says:

    Hi David,

    Clint asked that I take a look at the issue that you reported and I wanted to share with you what I know at this time.

    Replication is definitely important to some of our customers but we know from customer experience data, hits on replication topics, posts in newsgroups, and general noise on the issue that it is used by a small percentage of users. Some times last minute fixes to other bugs break working scenarios. That is one reason why we slow down bug  

    fixes as we approach RTM. We don’t like that this happens and are always working to release a product that is of great value to our customers.  

    With this particular issue, we are aware of the behavior and the support team is working closely with the product group to determine possible solutions to the problem.   When additional information becomes available, I will ask Clint to update the blog so that you are aware.  In the meantime, I would suggest that customers who are experiencing a problem or needing assistance with DAO synchronization of mdb’s in 2007, contact product support.  



  5. I appreciate the response, and I do understand about priorities in getting a piece of software out the door — I am, after all, an Access developer.

    But the complete neglect of Jet replication is a tragedy. It was such a useful feature and is more and more important today as users start wanting to put applications in the field on disconnected laptops and then synch data back with the mother ship. Sure, it can be done with SQL Server replication, but at the overhead of installing and supporting that on the laptops, or delving into the intricacies and limitations of heterogeneous replication.

    I would say that the reason why nobody users replication is because Microsoft has failed to promote it. Microsoft should have improved Replication Manager. MS should have made the synchronizers runnable as a system service. MS should have cleaned out the marketing lies in the replication white papers and FAQs much sooner than they did.

    I read all the Access groups every day as well as checking in on a couple of times a week in an attempt to provide the kind of community response for users with replication questions that just isn’t there from MS. I try to make sure every post about replication gets answered or at least a response (if not a definitive answer). Michael Kaplan used to do this far better than I ever can, but he’s moved on to other things.

    Why didn’t Microsoft have somebody doing this a long time ago? Obviusly, it’s too late now, since A2K7 shows that Jet replication is just not a priority for Microsoft any more (it doesn’t exist any more except as a legacy of support for the old Jet engine). But that’s one of your self-fulfilling prophecies coming true. MS never made much of an effort, and, surprise, surprise, nobody could figure out how to make it work!

    I’ve set up a wiki:

    in an effort to provide a place for those of us using replication to document what we’ve found all in one place. But so far, I’ve gotten no contributions from anyone else. But why couldn’t MS have done something like this long ago? Or even 5 years ago? The improvements to replication in Jet 4 were really phenomenal and the release of Access 2000 would have been a brilliant time to promote replication functionality.

    But, as it is, we’re left with MS employees occasionally dipping into the replication newsgroup with obviously very shallow knowledge of replication, and meager efforts to keep the FAQs and white papers up to date.

    It should be obvious why nobody is using replication, because it’s never been given the version 3 treatment by a Microsoft development team. Actually, I’d say Jet 4 was the version 3 treatment for the database engine part of it, but the UI and documentation side of things never got the finishing touches that usually come from MS’s version 3 releases.

    These days I get more requests for laptop replication than I ever did back in the day before VPNs and Windows Terminal Server everywhere, when I was doing multi-site replication projects because there was no other cost-effective way to share the same data between multiple locations. Nowadays, those multi-site users are using Terminal Server-hosted Access apps, so replication is no longer needed there, but more and more small businesses (I’m talking about micro-businesses, with fewer than 20 employees) need laptop-to-LAN replication, which is very easy to implement because it can be done with direct replication (as long as they don’t need to synch while in the field). It’s very easy to set this up and to program it, but there aren’t really any How-to’s out there that explain this. I explain it over and over again in newsgroup posts, but why isn’t something that is straightforwardly addressed in the Knowledge Base in a way that is easily locatable by those seeking the information?

    Maybe the hapless users interested in replication aren’t trying hard enough?

    Sorry to vent. I just find it very frustrating that I’m now going to have a couple of years of fighting against the WE MUST UPGRADE TO A2K7 juggernaught for a reason that should have been caught and fixed in beta testing.

    David W. Fenton

    David Fenton Associates

  6. David,

    I love your passion for the product. It is great to see folks like yourself work so diligently to help users get their solution working correctly. It is people like yourself that make Access such a productive development env.

    Some times product development doesn’t work out the way we plan–that is part of making big bets. Personally, I’m really excited about the team owning the Jet codebase and the improvements that are starting to come in that area of the product. This release the team spent a bunch of time getting the code integrated into Office and setup–expect to see more innovation in the engine level in the future.

    Moving forward we hope to meet many of these type of "micro-business" scenarios with Office Live and the new offline capabilities. For $20 a month you can subscribe to a service that provides lists on the Internet and using A2K7 get basic replication.

    Did you notice the bug in Beta 2 TR? Our beta engineers indicated the replication bug didn’t repro in the beta build. The workaround to the bug is to open a replica inside of the Access instance before using DAO.

    It would be very cool if there is a WE MUST UPGRADE TO A2K7 juggernaught. The team will work to get a solution so you don’t have to fight the fight for a couple years.

  7. It would be great if *real* replication were introduced into the new Jet engine, but I doubt that’s going to happen.

    As it is, Sharepoint lists are not a substitute for replication of a relational database. Indeed, I can’t conceive of any scenario when they are appropriate for anything but trivial applications lacking RI, the kind that I haven’t been involved with for many, many years.

    As to Office 2K7, well, no, I haven’t tested any version of it. I can’t because I have stayed with Windows 2000, because I wanted to avoid the myriad problems with WinXP. I probably should have installed Win2K3 Server on my primary workstation, since it’s my favorite version of Windows, but I never got around to it.

    I’m in the "if it ain’t broke, don’t fix it" school of thought, which makes me much more conservative than most Access developers. I didn’t even develop in A2K until after the monolithic save problems were addressed, and after Jet 4 SP6. I still have one Access 2 app out there, in regular use, and three quite large A97 apps. They run just fine.

    Every time a new version of Office is released, my clients feel the pressure to upgrade. We always buy the latest versions of Office on their new computers, then remove Access and install A97 for these older apps. While this is technically a violation of the EULA, we do it because the benefit from upgrading does not justify the cost, while they still are buying their Access licenses, just not using the latest versions. I feel like we are not cheating anyone out of revenue (we are buying new licenses) so that it’s fair, even if not technically kosher.

    I hope the solution to the DAO replication problem comes out before more people abandon replication entirely. I can’t think of a better way to kill off such a feature as to simply totally break it in a new release. I know that wasn’t intentional and that it’s not completely broken, but it sure could cause problems for the reputation of native replication. I see it as yet one more casualty of the stupid decision to promote ADO-everywhere (including Jet data, where it never made any sense) that was embarked upon with the release of Access 2000. MS support has finally figured it out and now promotes DAO, but most of the replication documentation is still stuck with examples using JRO, and that’s likely why the problem wasn’t fully tested and accounted for, and the regression in the final released overlooked.

    I know software development is hard, and Access is an enormous project and that these things happen. I’m glad it’s intended to be addressed. But I wish it hadn’t happened in the first place.

    David W. Fenton

    David Fenton Associates