none
export data from Access to BCM via VMA RRS feed

  • Question

  • Hello, <o:p></o:p>

    I am trying to make an interface, using VBA, between an Access "CRM" application and BCM. In Access I have tables that store information related to Accounts, Opportunities and Contacts and I would like to synchronize this information between the 2 applications using this interface. <o:p></o:p>

    This far I was able to successfully do this for "Accounts" and now I am struggling with "Opportunities".<o:p></o:p>

    The reason for my struggle is that all my Access Opportunities are linked to Accounts, so the first step in storing information from Access to BCM is to find the Account object and get its EntryID. However, after the first pass through the code, the search always turns out empty. I am sure of that all my searches must results in finding exactly one object, so it is not a problem of returning an empty result. The code I am referring to is bellow.

    After further research, I found that the problem is that I cannot use the Find method more than once in every opened session of the outlook.application Thus using a very ugly code I was able to fix this by closing the session at the end of every loop, then wait for about 10seconds (the time it takes my computer to close the session) and then, at the begining of the loop re-instantiate the sessions.

    Can you please help me understand why this happens and is there a more elegant and less time consuming way to fix this?

    Thank you!

    The code: (rs is the recorsed that I use to get the information from the Access database)

        Set olApp = CreateObject("Outlook.Application")
        Set objNS = olApp.GetNamespace("MAPI")
        Set olFolders = objNS.Session.Folders
        Set bcmRootFolder = olFolders("Business Contact Manager")
        Set bcmOppFolder = bcmRootFolder.Folders("Business Records").Folders("Opportunities")
        Set bcmAccountsFldr = bcmRootFolder.Folders("Business Records").Folders("Accounts")

    Do While Not rs.EOF

    'instrunction where the problems appear....        
        Set Acct = bcmAccountsFldr.Items.Find("[FileAs]='" & StrConv(rs.Fields("Customer"), 3) & "'")

        If TypeName(Acct) = "Nothing" Then
            ParentID = Acct.EntryID
    'CREATE OPP
            Set newOpportunity = bcmOppFolder.Items.Add("IPM.Task.BCM.Opportunity")
    'ADD Subject
            newOpportunity.Subject = StrConv(rs.Fields("Customer"), 3)
    'ADD ParentID
            If (newOpportunity.UserProperties("Parent Entity EntryID") Is Nothing) Then
                Set userProp = newOpportunity.UserProperties.Add("Parent Entity EntryID", olText, False, False)
                userProp.Value = ParentID
            End If
    '..... ADD the rest of the information

    'SAVE OPP
        newOpportunity.Save
       
        Else
            MsgBox ("Contact was not fount")
        End If
       
        rs.MoveNext
       
        Set Acct = Nothing
        Set newOpportunity = Nothing
       
    Loop
       
        Set bcmOppFolder = Nothing
        Set bcmAccountsFldr = Nothing
        Set bcmRootFolder = Nothing
        Set olFolders = Nothing
        Set objNS = Nothing
        Set olApp = Nothing

    Thursday, November 1, 2012 10:16 AM

Answers

  • I'm not sure what is going on - your code looks fine.  All I can suggest is using a dedicated variable for the Items collection, and maybe try using the Items.Restrict or Folder.GetTable methods.

    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    • Marked as answer by Doru22 Friday, November 2, 2012 8:54 AM
    Thursday, November 1, 2012 7:49 PM
    Moderator

All replies

  • There is no usage limitation to using the Find method.  As long as your search criteria is valid, you can perform a search as often as you like.

    Can you try searching on another property to verify that you can get any results? Triple check your final search string as well to make sure that there are no characters like quotes (which you need to double) that could be affecting the results.

    Items.Find Method (Outlook):
    http://msdn.microsoft.com/en-us/library/ff869662.aspx


    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Thursday, November 1, 2012 3:01 PM
    Moderator
  • Hi, thanks for your answer.

    I tried more then 5 properties with the same result. The problem is not in the search string.

    I'm sure of this because if for example lets say the recordset has 3 differet accounts, if I start with the first entry it will work with the first, fail the next 2; if I start with the second, it will work with the second, fail the last... and so on...

    also, if I close the outlook session an reopen it at the begining of the loop it works. But this is very time consuming, and sometimes, depending on the other proceses on the computer it may also fail.

    Thursday, November 1, 2012 7:09 PM
  • I'm not sure what is going on - your code looks fine.  All I can suggest is using a dedicated variable for the Items collection, and maybe try using the Items.Restrict or Folder.GetTable methods.

    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    • Marked as answer by Doru22 Friday, November 2, 2012 8:54 AM
    Thursday, November 1, 2012 7:49 PM
    Moderator
  • Hi Eric,

    Your suggestion was perfect.

    I just defined a new variable for the Items collection and that solved the problem.

    Thanks a lot !!! More then a day of agonizing work was solved with a simple tweak...

    Friday, November 2, 2012 8:54 AM