In this article, I
will demonstrate a great feature in an Access 2007 template that integrates
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 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; 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. 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 import all of 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 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. While 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.
Importing Contacts
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 that you can add some contacts. Rather than discussing
this now, I will demonstrate how that Wizard runs in the Contacts.ACCDB
download later.
Adding a Contact
When the database
opens, the Contacts List form will display. Initially you will notice that
there is no information unless you’ve added your contacts from Outlook. Let’s
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 you edited. Let’s 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 synchronise.
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. Let’s look at what happens when I synchronise an Access contact
to Outlook. In this case, I needed to add Level 39 to Sean Robinson’s 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 feature is that both records are most
likely going to be sitting side by side. In addition, 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.
The conclusion that you and the
users of your database need to be aware of in this synchronising arrangement
are:
- 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 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,
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
The Commands
In this article, I
have featured the following commands that are available from VBA or Access
Macros.
RunCommand AddFromOutlook
RunCommand SaveAsOutlookContact
RunCode OpenMap([Address],[City],[State/Province],[Zip/Postal
Code],[Country/Region])
Conclusion
The Microsoft Access
team have spent a lot of time creating Access templates for Access 2007. While 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.
»
See All Articles by Columnist Garry Robinson
