By Garry Robinson for Access 2007
In this article I will demonstrate a great feature in an Access 2007 template that does integrate your database with your Outlook contacts. If you are forever copying contact information from Access to Outlook and then synchronising Outlook with your phone, you will mostly like the way this Access feature has been implemented. I will also show how your contact address information can link directly with Microsoft Maps.
The Access Template
One of the success stories of Access 2007 has been the popularity of the Access templates as they have been downloaded many thousands of times. Now whilst the downloads and the solutions that they provide are important, I would like to draw your attention to a pretty neat feature in the Contacts download. Integration with Outlook. So to get started, Open Access 2007 as shown in Figure 1 and download the Contacts.accdb database to your computer. Note that you will also need Outlook 2007 for the tricks shown in this article to work.
Figure 1 - Access 2007 templates
When the download is completed, Access will open a Help window that provides you with information on renaming fields and other introductory Access actions and another which offers you two videos and a hyperlink to offer you to import all your contacts from Outlook (see Figure 2).
Figure 2 - Getting Started Video
Video 1- Using the Contact Management database template. This will provide you with a brief overview of what the the database does. It is quite short and it shows you how to easily add values to a list, an option that wasn't available in earlier versions of Access
Video 2- Modifying the Contact Management database template. This video shows you how to add a Birthday field to the contacts table. Whilst all competent Access developers will think they know everything there is to know about this topic, this video shows how the Access team is trying to make database modifications more acceptable to ordinary folks. In this video this is achieved by adding the text field directly into the table in datasheet view and then that field is added to the form in layout mode.
Overall these two videos don't tell a lot about the template, you have to go and explore it yourself to find out more.
On the getting started page is an import contacts hyperlink. I suggest that you click that before closing the Getting Started form. This will fire up the Adding contacts from Outlook Wizard so you can add some contacts. Rather than discussing this now, later I will demonstrate that how that Wizard runs in the Contacts.ACCDB download.
Adding a Contact
When the database opens, the Contacts List form will display. Initially you will notice that there is no information unless you added your contacts from Outlook. Lets press the New Contact hyperlink and add a new contact as shown in Figure 3. As you can see, the interface is very neat and clean and if it did what you wanted, you could hand it over to other people straight away.
Figure 3 - Add a new contact
Once you Save and Close, you are returned to the contacts list (see Figure 4) which refreshes and focuses on the last record that your edited . Lets now click on the Open hyperlink and see what happens when we save a Contact into Outlook.
Figure 4 - Last record that you edited
If you look at Figure 5, you will see the standard Outlook 2007 contacts interface. Saving the item into Outlook will mean that you now will have the information ready to go in Outlook and on your phone when you next syncronise.
Figure 5 – Saving a new record into Outlook
Modifying the Contact In Access and Saving To Outlook
Saving information into a new Outlook contact and moving a new Outlook contact in earlier versions of Office was something that could be achieved if you searched the internet hard enough for a VBA code sample. What was really difficult was synchronising those changes. Lets look at what happens when I syncronise an Access contact to Outlook. In this case I needed to add Level 39 to Sean Robinsons address.
This time the Outlook contact form shows up the same as before but when you press the Outlook Save and Close button, the Duplicate Contact Detected Outlook wizard pops up (see figure 6). If you press Update this will amend the Outlook record. If you look carefully at Figure 6 you will notice that the Job Title (which I modified in Outlook to Marketing Manager) is highlighted. This wizard is telling you that you made a change to that entry so you may well want to consider updating this information in the database. As you can imagine if you are a database professional, getting Contact information correct is really tricky and this Access to Outlook wizard lets us tap into the very sophisticated Duplicate Contact Detected Outlook wizard. This is something that is very tricky to write on your own.
Figure 6 – Saving a an existing record into Outlook
Importing From Outlook
If you look back at Figure 3, you will see that the Contact List form has a hyperlink to Add From Outlook. When you click on this it opens the Outlook Search Names to Add wizard. From here you can add multiple Contacts to the database as I have done in Figure 7. Click the OK button and one or more entries are added to the Access database as shown in Figure 8. Access will then select the first record in those new entries. So how neat is that, selective Additions to the database from Outlook using the Outlook Wizard.
Figure 7 - Add Multiple Contacts in Outlook
Figure 8 - One or more entries added to Access from Outlook
End Of The Good News Story - Modifying in Outlook and Updating in the Database
If you make a change to an entry in Outlook and then want to add that change to Access, this is where the good news ends. When you add the entry to Access, it adds a new entry as shown in Figure 9. Its one redeeming features is that both records are most likely going to be sitting side by side. Also what helps a little is that in the Contact Details form, it will give a warning to highlight that the entry is a Possible Duplicate.
So the conclusion is that you and the users of your database need to aware of in this synchronising arrangement is
NEW Outlooks contacts to Access Contacts is OK
NEW and Modified Access Contacts to Outlook contacts is always OK
A Modified Outlook contact that exists in Access is NOT going to update properly.
Figure 9 - How an existing Outlook record looks when added to Access
Finding A Location On A Bing Map (or a Google Map)
As an aside, one of the intriguing options on the Contact Details form shown in Figure 10 is the Click to Map option. When you choose this, the database will jump to Internet Explorer and open Microsoft Maps and put your address in the search option (see Figure 11). On some occasions this will work correctly and in some cases it won't. I suspect it will be a lot better in the USA than it is down here in Australia.
Now the amusing thing is when I copy and paste the Microsoft Maps search string "Level 39+2+Park+St,+Sydney,+NSW,+2000,+Australia" into Google, it shows the location of our Office correctly in a map in the normal Google Search Window (see Figure 11). Overall this map button is a pretty handy way of getting to a Internet based maps interface from your contacts database and could be a useful addition to your database.
As a bonus you will find that the Map button also exists in the report as shown in Figure 12. Yes you Access diehards, there is interactivity in Access 2007 reports.
Figure 10 - Click to map in Access 2007
Figure 11 - The Access map search string in Google
Figure 12 – The Map export button in a Access 2007 report
In this article I have featured the following commands that are available from VBA or Access Macros
RunCode OpenMap([Address],[City],[State/Province],[Zip/Postal Code],[Country/Region])
The Microsoft Access team have spent a lot of time creating Access templates for Access 2007. Whilst you may never use any of them yourself (because your database is so much better), there are some pretty interesting features in the database and a few things to learn in both the database and the accompanying videos.
Download the template from Microsoft for this article.
If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.
You will find the full size screen shots in Toolbox
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. Garry has been involved in 100s of Access databases Access. Contact details in Australia Web http://www.gr-fx.com/