Outlook Access linking

[fjwong]

From an earlier post:

When I link a contact list in Outlook to an Access
database, some of the Contact entries don't show up in the
Access database. As far as I can tell there is no
difference in entries that show up vs those that don't.

Now:

Curious as to why even the middle name does not appear on
Access; and that the field names of First and Last do not
correspond with the Access convention of FirstName LastName.

Also, there is no way (or is there)to insert an ID (key
field) to the Access table to make the linked table more
meaningful (relational) in Access.

Any advice?

Thanks.

Frank Wong

 

[John Nurick]

Curious as to why even the middle name does not appear on
Access; and that the field names of First and Last do not
correspond with the Access convention of FirstName LastName.

The "fields" you see in the usual Outlook forms are only indirectly
related to the actual data stored wherever that particular installation
of Outlook stores it. For instance, for Contacts there are

1) the "fields" in the usual Contacts form, which are a subset of
2) the much wider set of "contact fields" available in the Outlook
Define Views dialog, which correspond more or less one-to-one to
3) the *properties* exposed by the Contact object in the Outlook
object model, which are *not* the same as
4) the actual data fields that are stored.

And then there are
(5) the fields that appear when you try and export from or link to the
Contacts folder.

As I understand it, just what appears in (5) is determined to some
extent by the current view of the folder, so you might try altering
the view and re-linking to Access. Otherwise, you can always get
pretty much what you want by using the Outlook object model via
Automation.

Also, there is no way (or is there)to insert an ID (key
field) to the Access table to make the linked table more
meaningful (relational) in Access.

Outlook's Contacts store doesn't have an accessible primary key. Each
Contact item does have a unique identifier string associated with it,
but as far as I can make out (a) it's not exposed to be linked to,
although you can get at it via VBA, and (b) the strings may be more than
255 characters, so cannot be indexed in Access, which means that even if
it was exposed it would be a pain to use.



Here are some useful links:

Accessing Microsoft Exchange and Outlook Data Using Visual Basic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnout98/html/olexcoutlk.asp

Sample Procedure to Link a Microsoft Outlook Folder
http://support.microsoft.com/?id=209946

How to programmatically export Outlook items to Access
http://support.microsoft.com/?id=290792
http://support.microsoft.com/?id=253794

OL2000: How to Programmatically Import Outlook Items from Microsoft
Access
http://support.microsoft.com/?id=208232

Using Automation in Microsoft Office Access 2003 to Work with Microsoft
Office Outlook 2003
http://msdn.microsoft.com/library/d...ry/en-us/dno2k3ta/html/odc_ac_olauto.asp?_r=1

Finally, the best single place I know for information about outlook is
http://www.slipstick.com

 

[Guest]

You are great, John! Thank you.

The "fields" you see in the usual Outlook forms are only indirectly
related to the actual data stored wherever that particular installation
of Outlook stores it. For instance, for Contacts there are

1) the "fields" in the usual Contacts form, which are a subset of
2) the much wider set of "contact fields" available in the Outlook
Define Views dialog, which correspond more or less one-to-one to
3) the *properties* exposed by the Contact object in the Outlook
object model, which are *not* the same as
4) the actual data fields that are stored.

And then there are
(5) the fields that appear when you try and export from or link to the
Contacts folder.

As I understand it, just what appears in (5) is determined to some
extent by the current view of the folder, so you might try altering
the view and re-linking to Access. Otherwise, you can always get
pretty much what you want by using the Outlook object model via
Automation.



Outlook's Contacts store doesn't have an accessible primary key. Each
Contact item does have a unique identifier string associated with it,
but as far as I can make out (a) it's not exposed to be linked to,
although you can get at it via VBA, and (b) the strings may be more than
255 characters, so cannot be indexed in Access, which means that even if
it was exposed it would be a pain to use.



Here are some useful links:

Accessing Microsoft Exchange and Outlook Data Using Visual Basic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnout98/html/olexcoutlk.asp

Sample Procedure to Link a Microsoft Outlook Folder
http://support.microsoft.com/?id=209946

How to programmatically export Outlook items to Access
http://support.microsoft.com/?id=290792
http://support.microsoft.com/?id=253794

OL2000: How to Programmatically Import Outlook Items from Microsoft
Access
http://support.microsoft.com/?id=208232

Using Automation in Microsoft Office Access 2003 to Work with Microsoft
Office Outlook 2003
http://msdn.microsoft.com/library/d...ry/en-us/dno2k3ta/html/odc_ac_olauto.asp?_r=1

Finally, the best single place I know for information about outlook is
http://www.slipstick.com

 

[Guest]

John:

I read all the articles you cited; very helpful in understanding the
background, and found Frank Rice of MSDN's article, the most recent, most
pertinent, especially his guide to "create an Outlook item in Access".

But alas, the Outlook item so created does not link (or does it?), and
therefore, requires maintenance in both Outlook and Access; and I don't
believe a primary key can be created and useful meaningfully.

One would wonder why didn't the authors of Access and Outlook come together
and create a procedure to link all fields in Outlook to Access, and use a
common primary key.

By the way, with your knowledge, or the knowledge of an expert, how much
time is needed to accomplish what I had desired?

And would that useful feature, common sense to me, be available in Office
2006?

Thank you again.

 

[John Nurick]

On Thu, 10 Feb 2005 10:15:05 -0800, "Frank Wong" <Frank

I read all the articles you cited; very helpful in understanding the
background, and found Frank Rice of MSDN's article, the most recent, most
pertinent, especially his guide to "create an Outlook item in Access".

But alas, the Outlook item so created does not link (or does it?), and
therefore, requires maintenance in both Outlook and Access; and I don't
believe a primary key can be created and useful meaningfully.

Certainly it wouldn't be easy.

One would wonder why didn't the authors of Access and Outlook come together
and create a procedure to link all fields in Outlook to Access, and use a
common primary key.

People have been wondering this ever since Outlook first appeared, which
I think was about 1996.

By the way, with your knowledge, or the knowledge of an expert, how much
time is needed to accomplish what I had desired?

I don't know. If I'm right in my belief that it's possible to use a
custom view or form to get Outlook to expose "linkable" fields other
than those it exposes by default, then an Outlook expert should be able
to do it in an hour or two. If I'm wrong it could take weeks to produce

And would that useful feature, common sense to me, be available in Office
2006?

Even if I knew what was in a future version I wouldn't be allowed to
tell you. I'm not expecting it but would be delighted if it did happen.

 

[Immanuel Sibero]

One would wonder why didn't the authors of Access and Outlook come together
and create a procedure to link all fields in Outlook to Access, and use a
common primary key.

Sort of "the right hand doesnt know what the left hand is doing"?

I'm a complete outsider to MS, but I see evidence of MS's attempt to merge
datastores of their various product offerings. What you're seeing has been
happening for a long time and across many MS product lines. Access, Outlook,
Outlook Express, Exchange, Windows Domain Database, Active Directory, all
have datastore as a back end but very little to none interoperability among
them.

Maybe one day the datastore will all be SQL Server. Wouldnt that be nice?


Immanuel Sibero