Results 1 to 4 of 4
  1. #1
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Linking to an MS Outlook Contacts Folder

    In our Student Administration database we would like to link to the Outlook contacts of one (and perhaps other) of our users.

    I’ve seen in Outlook how a Contacts folder can be shared so that one user can see another’s contacts folder in addition to their own. When attempting in Access to use the External Data, More, Outlook Folder wizard, I first had to set up/configure Outlook on my computer. However, it seems the Wizard only looks on my PC for these and so I am unsure how to link to another user’s Outlook contacts. It did create a linked table to my outlook contacts. Perhaps it requires that you already have shared access to the other person's contacts in Outlook...?

    I found one seemingly relevant thread in the Access forum entitled “Importation contacts Oulook depuis Access 2010”. The problem here seemed to be that a macro to automatically add a contact from Outlook stopped working when the database was split. The suggested possible solution was “…. a temp table in the front end to hold the imported contact record then use an APPEND SELECT action to transfer into the linked backend table.” I did not see anything else in your Import/Export forum or on other Access or Outlook forums that seemed helpful.

    So, my questions are:
    1. How can my Access database link to another person’s Outlook contacts?
    2. How would I integrate or sync that linked table with another Contacts table in the Access database?

      Note: while the ability to sync both ways between Access and Outlook is desirable, it may only be necessary to be able to post Outlook updates to Access.

    As always, thanks for any guidance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    1. The only thing I can find that offers a clue:

    If you want to share the Contacts folder between profiles, you must use profiles that point to the same Microsoft Exchange Server mailbox. If the profiles do not point to the same Exchange Server mailbox, the profiles will not share the Contacts folder and updates cannot be automatic.

    And this http://www.msoutlook.info/question/614
    which I think describes actions in Outlook. Whether or not those actions can be programmatically invoked from Access is unknown.

    2. Not sure what you mean by 'sync' - can do a query that joins tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128
    2. Not sure what you mean by 'sync' - can do a query that joins tables.[/QUOTE]

    June:
    Thanks as always for your response. I'm not sure how to do a query here as there is not a parent/child relationship. The contacts in the Access table (Emergency Contacts) will be different from those in the table linked to Outlook (tblOutlookContacts).

    In the linked Outlook table, design view shows a key field called "Normalized Subject" which contains a concatenation of First and Last name with a space in between and shows the Access key field icon. Thinking I could join the the Access table via that field, I created a field in the Access table with the same name but again there is no common data (the Access table has an Autonumber field as key).

    Here are some SQL variations for all of which I received "Invalid Argument" when I attempted to select Datasheet view:

    1. Equi Join
    SELECT [Emergency Contacts].[Last Name], [Emergency Contacts].[First Name], [Emergency Contacts].[Normalized Subject], tblOutlookContacts.First, tblOutlookContacts.Last, tblOutlookContacts.[Normalized Subject]
    FROM tblOutlookContacts INNER JOIN [Emergency Contacts] ON tblOutlookContacts.[Normalized Subject] = [Emergency Contacts].[Normalized Subject];

    2. Left Join
    SELECT [Emergency Contacts].[Last Name], [Emergency Contacts].[First Name], [Emergency Contacts].[Normalized Subject], tblOutlookContacts.First, tblOutlookContacts.Last, tblOutlookContacts.[Normalized Subject]
    FROM tblOutlookContacts LEFT JOIN [Emergency Contacts] ON tblOutlookContacts.[Normalized Subject] = [Emergency Contacts].[Normalized Subject];

    3. Right Join

    4. No Join
    (I deleted the join line between the two tables).

    5. Find Unmatched query via Wizard

    The linked Outlook table seems to be in real time and actually updates in both directions, though we probably only need to see the updates made in Outlook on the linked Access table, not vica versa. One is not able to change the Outlook table's design in Access because it is linked.


    Alternates I tried
    1. I've read about a UNION query in the Missing Manual book but apparently records in this type of query cannot be edited. I did make an attempt but I get the error message "syntax error (missing operator) in query expression 'Emergency Contacts.Last Name'". Here is the SQL:

    SELECT Emergency Contacts.Last Name, Emergency Contacts.First Name
    FROM Emergency Contacts
    UNION
    SELECT tblOutlookContacts.First, tblOutlookContacts.Last
    FROM tblOutlookContacts

    2. I tried an append query to the Access Contacts from the linked Outlook contacts but got error "Invalid Argument" error message. Here is SQL:

    INSERT INTO [Emergency Contacts] ( [First Name], [Last Name], [Job Title], [Company] )
    SELECT tblOutlookContacts.[First], tblOutlookContacts.[Last], tblOutlookContacts.[Title], tblOutlookContacts.[Company]
    FROM tblOutlookContacts;

    However, I created a make table query from the linked Outlook table and then an append query from the made table to the Access Contacts table. This works.



    As alternate approaches, which I haven't tried yet
    1. Create something, perhaps VBA is needed, to check the Access table to see if there is a match for records in the linked Outlook table and then to append a new record if no match or to check and update fields if there is a match (of course the problem there is determining which is correct). I suppose this could be run whenever the database is opened or the Access Contacts table is opened. While I've been reading up on VBA coding in my 2 books, I've yet to try writing something.

    2. Keep the two tables separate and display them as datasheets in the same form with a button to open another form for detail edits.


    Close
    Sorry for the excess verbiage and hope this is comprehensible. Any thoughts you have would be appreciated.

    ps: Can you point me to instructions for saving drafts of posts or replies?

    Thanks
    Last edited by June7; 10-28-2014 at 04:01 PM.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Names do make poor unique identifiers. But apparently names are all you have as common data to serve as identifiers - unless you want to use some field of the Outlook contact record for an identifier. Unfortunately there is no 'user defined' field available as far as I can tell.

    Find Unmatched queries could reveal records in one table not in the other. Determining if the discrepancy is due to a variation of name spelling would required case-by-case review.

    If you want data to line up properly, field names for the UNION must be in the same order on each SELECT line. First line sets the field names for the query. Don't forget semi-colon at the end of last line. Field name with a space must be enclosed with [] - this is true for any query and in VBA. Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

    Don't see anything wrong with the INSERT SELECT.

    The forum has an Auto-Save feature - appears to be about every 60 seconds. If link gets interrupted can usually return to the thread and recover text entered into the editor. If it has been saved, look for "Restore Auto-Saved Content" button at lower left of editor.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-23-2014, 06:19 PM
  2. issue with linking outlook folder to access
    By live2ride in forum Import/Export Data
    Replies: 7
    Last Post: 12-20-2012, 12:24 PM
  3. Linking accdb to Outlook Contacts folder
    By Craig in forum Import/Export Data
    Replies: 1
    Last Post: 02-14-2011, 05:51 PM
  4. linking contacts
    By jkorinek in forum Access
    Replies: 1
    Last Post: 06-11-2010, 03:55 PM
  5. Outlook contacts
    By noidea in forum Access
    Replies: 0
    Last Post: 07-31-2009, 07:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums