Andy Colonna has released an open source spell check that works for the runtime. It also includes suggestion logic. Those of you building runtime apps might find it useful.

Steve Bailey from Sagekey asked me if I would be willing to mention their product for building Access runtimes. Here is Steve’s description of their product with a special discount for our readers:

SageKey Software has a track record of removing the time and frustration out of building reliable installs that work on all supported platforms and maintain co-existence with other versions of Access.

Having been in the business since Access 97, SageKey provides installation tools for all versions of the Microsoft Access runtime including Access 2007. During the month of September 2008 we are offering a 10% discount for orders when you mention you heard about us on the Access blog.

Edited: 9/4/2008. Fixed links.

I know it is off topic but there are a few blogs posts of MSDN I found interesting.

IE 8 Performance - good post about the work the IE team has been doing around performance. I love browser performance work.

Windows 7 – Approach to System Performance – post by Steven talking about the different elements of performance.

Boot Performance – post by Michael Fortin about what the fundamentals team is doing for Windows 7.

One thing about being on the Access team that I enjoy is seeing some of the great work being done in the Access community. Among the forefront of this community is FMS, who is the leading developer of Access add-ins. FMS recently announced a preview version of Total Access Analyzer 2007 which is a tool that helps you create better Access databases. There are too many new features to list here, but here is a small subset that might be interesting to note:

• Support for the .ACCDB file format added in Access 2007
• Analysis of new macro actions including embedded macros
• 24 new reports!
• Reports can now be viewed in Report View which provides an interactive experience for reports
• Reports can be exported to PDF

Here's a link to the preview:

http://www.fmsinc.com/Products/analyzer/confprvw.asp

Enjoy!

Aleksandar Jakšić, who is one of the testers on our team, has written an article that shows you how you can use the Access Database Engine from native applications written in C/C++.

The article shows you how to to the Access Database Engine using C or C++, but has a great discussion of the data stack used by Access and some of the different components of the engine. He also breaks down the different methods for accessing data using native technologies such as DAO, ADO, OLE DB, and ODBC. The article provides great insight about the engine and the mechanisms that are used to connect to it, regardless of whether you are writing native code.

The article is available on MSDN:

Developing Access 2007 Solutions with Native C or C++
http://msdn.microsoft.com/en-us/library/cc811599.aspx

Crosoft releases Access Object Navigator

This tool is not only for Microsoft Access 2007, but also supports versions 2000, 2002 and 2003. Once installed, Navigator provides a method to develop software applications without creating additional switchboards. They have a free trial if you want to check it out.

Long Island Power Authority uses a custom Access application to crunch data

The program HDR developed for the Neptune project proved to be effective in meeting permit requirements for TSS monitoring of a large and complex project with potentially serious environmental impacts in an environmentally sensitive area. The power lines officially became operational in July 2007, carrying enough electricity to meet the demands of approximately 600,000 homes.

Access to SQL Tutorial

Inspired by our good friend Garry Robinson, Randall from Yale created a tutorial on how to leverage the power of SQL server stored procedures through a MS Access database.

10+ reasons to use Access (and a few reasons not to)

Microsoft Access may not be the king of databases, but it does offer a number of advantages over solutions like SQL Server and Oracle in certain situations. Access guru Susan Harkins counts the ways.

Today’s guest writer is Josh Meisels, our intern this summer. He is heading back to school next week but wanted to share information about one of his summer projects. 

Access 2007 made a huge push to create useful, ready-to-use database templates that users could download from Office Online and get started with Access. The number of downloads exceeded our expectations (the Access 2007 contacts database is downloaded about 60,000 times a month). We thought the templates were pretty good; however, in January we linked each template to a feedback survey. From the results we derived a Net Promoter Score that indicated we had plenty of room to improve (see Clint’s post on Provide Feedback).

When I began my internship on the Microsoft Access team 3 months ago, my first assignment was to redesign one of the poorest performing, and yet the most downloaded template, the Contact Management Database. Many people provided email addresses as part of our survey, so I contacted them and called 10 who ran the gamut from students to professors, receptionists and business professionals. Each had a different, personal story to tell about his or her experience with Access and with the contacts template. The best part was how surprised people were when they learned that Microsoft actually cared what they had to say!

From feedback, I compiled a list of template limitations, including

• Inability to group or categorize contacts
• Too hard to search for people
• Issues importing addresses from Outlook
• Availability of help on adding fields

The last one came up repeatedly; and for an Access newbie, just learning how to add a field can be a challenging task.

To bring help into the application, we created a custom “Getting Started” screen that pops up when you first open the template.

It provides links to online help and two custom videos about using and modifying the contacts template. The videos have received rave reviews, not just because they help users learn to work with the Contacts Template, but also because they teach widely applicable Access skills that, unlike traditional help, appeal to visual learners.

In addition to listening to customers, we also watched them. We conducted a usability study with people who were familiar with other Microsoft offerings like Excel, but who had never used Access, and we asked them to perform tasks with the template. For two days we watched and learned how to make the template even easier to use and more visually appealing. Changes that resulted from the study were the addition of a text-based summary of our videos, and clearer video labels. Over the next few weeks we made incremental changes to the template so what we released would be as clear and usable as possible.

Two weeks ago we released an updated version of the Contacts Template. Our satisfaction scores shot up 64%! The team is pretty excited. People especially like the new video help. Here’s what folks are saying about the videos:

• I learned the basic steps in Access. Now I can play a little with the program so I can learn much more about it.
• I have been struggling with how to add to the information but watching the video solved my problems
• It makes getting started easier
• Awesome! Short and concise yet detailed enough to give insight as to the functionality capabilities. I think I can do this!
• Anytime I can visually see how to----it simplifies the learning process. Thanks
• The hard to find but essential techniques was demonstrated in very good detail. (means, no more hair pulling and stress on my part) Thanks!!
• Thanks!! I learn better from SEEING something done, and this was most definitely helpful!!

Don’t take other people’s word for it. Check out the template here and the videos.

Using the Contacts Database	Modify the Contacts database

More important than the positive words about the videos and the NPS increase for the template is the overall impact or research has had on the product. The last question we asked in our feedback form was whether users would recommend Access to a friend. This isn’t rating the template anymore, but rather the overall experience with Access after using the template. The NPS score we derived from this improved 35% after releasing the new template!

Here are the three things we took away from our experience redesigning the contacts template:

• Listen: Listen to a broad range of customers and ask for feedback.
• Iterate: When you think you have a great design, make it even better. Then make it better again.
• Educate: Remember the Chinese proverb, “give a man a fish and he’ll eat for a day; teach a man to fish and he’ll eat for a lifetime?” It’s true. Help is a feature and should be accessible and comprehensible no matter the user’s skill level. Sometimes making a feature more opaque isn’t the best way to go. Instead, try to teach users how to make the most of your application and be transparent about how things work rather than adding unnecessary layers of abstraction.

If you are looking for ways to apply this to your own applications, consider the addition of a feedback form, an NPS score calculation and connect with people who struggle with your product. Also, before you release a feature, do ad-hoc usability testing. Ask a friend or co-worker to perform a few tasks; and watch where they click, what confuses them and what makes them happy. Finally, make help a part of your application by adding videos – the Office Labs Community Clips recorder is a great tool for this.

Access 2007 in many ways is a different product for end users. In previous releases it was very difficult to be successful creating a new application without using help, taking a class, reading a book, or asking the community—information workers that wanted to use the product had to really dive into the product. Sure we had some templates but there weren’t discoverable and the team hadn’t made big investments in that area. Internally, we referred the Access 2003 boot screen as the gray screen of death.

The new Getting Started screen is introducing the product to a much broader range of information workers that don’t have previous experience in Access.

We watched carefully the comments and ratings through Office Online. Everything indicated that people were getting lots of benefit and the template hit the mark and were successful at bring a new class of users. However, the team felt we needed to understand with more clarity how successful the templates were as well as gather more information about our users. We have fairly good communication channels with enterprises through our sales force and professional developers through the MVPs and the blog. The user we didn’t feel was accurately represented in our feedback loop was new users and information workers who were primarily focused on their day job where Access is a tool for helping them make decisions and track stuff.

About 8 months ago we updated our templates to include a Provide Feedback option to the main application form.

This link takes people to a short 10 question survey that utilizes the Ultimate Question methodology to determine customer loyalty and gathers some additional information about our users. The results of the research indicated there were lots of room to improve the template experience for new users.

One of the questions ask people if they would talk to a person on the team about their experience. Since the release of the survey the team has called all types of users to talk about Access on a regular basis. We typically write up our results and share them with the entire team. This process has enabled us to incorporate feedback from many of our users that previously haven’t had a voice in product design decisions.

The team has been able to leverage this information in a number of ways. Early calls indicated many people were having trouble printing labels because the Address field didn’t show up. Turns out the label wizard filters our memo fields and the address field type was a memo (consistency with SharePoint). We were able to quickly release a update to all the templates with a fix. The great thing about the Office Online integration for templates is we can release a fix without having waiting for an SP.

We recently took it one step further. Josh Meisels, our summer intern, used the information to contact a number of people who were using the contacts template. Through the summer we were able to make a number of changes that have been extremely well received. Josh will tell you more about that tomorrow.

Overall, the template survey enabled us to make a connection with an important customer that wasn’t represented in our feedback chain.

The article Create a tabbed form, and the companion sudden demo Watch this: Create a tabbed form,  are now live on Office Online.

The famous Allen Browne has published two new articles on Office Online.

Validation rules
Want to ensure that users enter valid data? Validation rules might be the answer. This article describes when validation rules are appropriate, and provides examples of commonly-used validation rules.

Relationships between tables
Once you learn how to design tables and create relationships between them, you can take advantage of the most powerful data storage and retrieval features of Microsoft Office Access. Read this article to see an example of relationships in action.

BTW – Firefox users are going to need to use IE to read these articles because of some funky mhtml file format we are using. Enjoy.

Mike Alexander an Microsoft MVP in Dallas is putting on an Access Power User Workshop October 20-21, 2008. The cost is $600 but Mike will offer a 30% discount for anyone that mentions they found it through the Access blog. Some of the topics that will be covered during the two-day workshop:

• Advanced Query Tips and Techniques
• Advanced UserForm Controls and Technique
• Enhancing your Application GUI
• Working with Recordsets
• Doing some Cool Things with Windows APIs
• Creating Dashboards Using Access Reports
• Packaging and Distributing Techniques
• Protecting your Access Applications
• Creating Help Systems for your Access Applications
• Creating an Installer Package for your Access Applications
• Automating the creation of Excel Reports
• Automating the Creation of PowerPoint Slides
• Automating Outlook to send Emails and Manage incoming Mail

There is a flattering blog post Microsoft Gets Blogging by Thomas Hawk. Some interesting quotes include:

I'm not sure when exactly or if it's ever been an official sort of thing, but it seems like Microsoft of all of the companies that I've seen out there interacting with bloggers consistently gets it right.

And I read random stories and blog posts like the one above where it just seems like some Microsoft employee is hanging out reading some blog at random and takes the initiative to interact very personally with a blogger. Sometimes big blogs, sometimes small blogs, but interaction in personal ways.

In addition to Microsoft employees interacting directly with bloggers, Microsoft of course has many employee bloggers out there talking about Microsoft and what they do there. This is smart.

I think what Microsoft is doing by engaging bloggers is a good thing. From a PR perspective blogs are difficult animals and in some ways they are like herding cats, but the influence of blogs are here to stay.

We started the Access Team blog October 5th 2005. Lots have changed—before the blog I would get asked at least once a week if Access was dead—that question doesn’t surface much any more. Today the team blog is always in the top 5-8 blogs hosted on MSDN and Technet.

Blogging for us isn’t a marketing effort or part of a broader communication strategy for the team. There are few guidelines beyond “blog smart” and “don’t talk about unreleased vnext product information.” Posting on this blog isn’t part of our review goals and usually gets prioritized against urgent day job issues. A few of us on the team really enjoy the community interaction (most of the time :-) ) and the benefits of feedback. We have even hired some of our best people through relationships developed through blogging. Mostly, we love the product and enjoy sharing the helpful information that comes our way.

There are challenges associated with blogging, especially when some people have a different vision about product decisions. Typically the conversation stays open and respectful.

I’m still waiting for the day when one of my comments lands me on the home page of CNN or quoted by the Wall Street Journal—I just hope it isn’t about something stupid I wrote. Another challenge is the broad range of readership. I know from the email we get and comments that the readership of this blog spans people new to the product to the most seasoned Access expert. Overall, the dialog has been good and made us a better team. I expect the community has got quite a bit of value out of it as well.

Well—I got to get back to looking at 14 bugs and seeing if I can build a cool app with new bits. Wish me luck and have a great weekend.

We love Excel (as-a-matter-a-fact, both teams report up to the same VP) but there are times where it isn’t the best tool for a database :-). Steven Thomas recently did an interview with Cancer Lifeline about how they moved from Excel to Access. Here is the article intro:

Sometimes a business solution is created by using Excel when Access is more appropriate. Or, perhaps Excel was originally a good idea, but the data has outgrown an Excel solution and it is time to use a database program. Fortunately, you can move your business data from Excel to Access.

Cancer Lifeline, a non-profit organization based in Seattle, originally designed its grant-tracking system by using Excel. After running into trouble with data redundancy, they decided it was time to give Access a try.

Dr. Kerry Fowler provides volunteer IT support for Cancer Lifeline. He agreed to an interview in which we discussed Cancer Lifeline's IT needs, his role and technical background, how they used Excel, and what they have done with Access.

View article…

Renaud has released a cool replacement for message box. Here is how he describes the code sample:

This project provides a custom and enhanced message box replacement for the default MsgBoxfound in Access. A Test database for Access 2007 is available at the bottom of this post.

Here is a screen shot to pique your interest:

Rob Cooper recently made a post, Adding Attachments from a Folder, which shows how to add a single attachment file per row.  But what if you wanted to add more than one file?  One (highly contrived) scenario would be to add all of the files in a folder to one row, and do so recursively if requested, similar to Rob's example.

What follows is some VBA code to do this, which borrows a bit from Rob's post and a bit from one of my earlier posts.  However, I should point out this (again, highly contrived scenario) is meant only as an example and not something I would recommend doing, because if you are adding more than just a few files you can (ok, WILL) bloat your database very quickly, and in doing so can negatively impact performance and potentially hit the two gigabyte file size limit very quickly (see Access 2007 Specifications for details on database file size and object limitations).

First, to use the following sample code you will need to do some setup.  Create a new table, add the following fields, and save it as Table1:

Table1

Next, open the VBE window (ALT+F11) and insert a new module (Insert -> Module), and paste in the following code:

Here is a short routine to help you test the above code.  You will need to change the "<YourUserName>" to your login name, or just change the whole path in the strRootFolder string constant to the folder you want to store in the table.  Note that I've set the blnIncludeSubfolders parameter to False to keep you from inadvertently bloating your database, but you can set it to True if you want to include all of the subfolders, too.