The need for Access and Outlook to talk to each other is inevitable. There are plenty examples on the web if all you are doing is working with your own e-mail account, but when you need to work with Public Folders, the examples get increasingly difficult to find. Hopefully, this post will make it that much less difficult.
In the past, all I had to do was link the Public Folder and everything was fine. This became a problem with MS Office 2007 when they changed the way the Outlook Exchange server stored the user’s profile in the connection string: If a user other than one who initially linked it tried to use it, it would sporadically throw an error. It was that problem that led me to make this post:
I should note that I had to get snippets of code from 3 different sites and still had to tweak it to get it to work. Nothing is ever easy!
So, the code works, problem solved, right? Not exactly. My company recently upgraded to MS Office 2013, Win 10, SQL Server 2014 AND MS Exchange Server (no idea which version) – and nothing worked like it used to. My code would still link the table, but it would take up to 5 minutes to access it once it was linked. I think I have a thread on that problem as well, but I am too lazy to look for it.
Acting on some advice I received, I tried a few things but one thing that worked very well (fast) was a saved import. Once it was saved, simply choose it from the menu or with VBA and in the blink of an eye, I had a local copy of the Public Folder to play with.
Life is good right? Again, not exactly. The Path for the saved import contains the user’s email and if it is not the one stored in the Saved Import’s XML, it throws runtime error 3011; “Access Cannot Find blah, blah, blah”
Easy, just change the XML data accordingly and everything will be fine. And it IS easy, once you find out how…it is the finding bit that can be difficult.
I have found two ways, there are probably more but the one I am sharing is the best in my opinion. The first involved altering the Path of XML property of the Import Specification which you can find by typing
in the VBE immediate window (I use the ordinal position but the name of the saved import works too).
By using the built-in InStr() function or even Regular Expressions(RegEx) to isolate the Path, you can then use Replace() to alter the XML and it will work. I have tried both InStr() and RegEx and I can share that code too if it is wanted. The reason I kept looking is that there HAD to be a more specific way to manipulate XML.
I have never really liked InStr unless I was dealing with large text files and once I found RegEx, I rarely have a need for it. RegEx is a very powerful tool, but it can be difficult to get your head around - for me anyway.
Then I stumbled across this thread in Stack Over-flow that was exactly what I was looking for and decided I would share it. It is much cleaner and easier to use; no arcane RegEx escape codes and no position math with InStr()
Option Compare Database
On Error GoTo err_handler
' MSXML2 requires reference to "Microsoft XML, v6.0"
' earlier versions are probably compatible, remember to use the appropriate
' DOMDocument object version.
' code borrowed from Jason Hardman's contribution at this link:
Dim XMLData As MSXML2.DOMDocument60
Dim ImportSpec As ImportExportSpecification
Dim XMLNode As IXMLDOMNode
Dim strNewPath As String
' Get XML object to manage the spec data
Set XMLData = New MSXML2.DOMDocument60
' existing Import Specification (should be set up manually with relevant name)
Set ImportSpec = CurrentProject.ImportExportSpecifications(0)
strNewPath = "firstname.lastname@example.org|Public Folders\SubFolder\SubFolder\etc"
' change it's path to the one specified
.setAttribute "Path", strNewPath
ImportSpec.XML = XMLData.XML
' run the updated import
Set ImportSpec = Nothing
Set XMLData = Nothing
Select Case Err.Number
MsgBox "Replacement path is invalid", vbCritical 'Profile name is wrong or MS Exchange server is offline
MsgBox Err.Number & " - " & Err.Description & vbCrLf _
& "Please take note of the error code and contact your System Administrator", vbCritical
Hopefully others will find this as useful as I did. Let me know what you thing and I am ALWAYS open to comments, suggestions and criticism.