I am converting an existing (working) application based on the excel example provided by Wild Apricot to work with Access instead of Excel. All I need from the API is to download a list of members. I've found that the Excel code does not work with Access. I've found many workarounds on various VBA boards, but I'm stymied at the final step of actually downloading the XML for the membership roster.

These three lines of code from the LoadContacts sub are the problem. The dim statement does not compile in Access. The error is that the 'user defined type is not defined'. If I change that to be DOMDoccument60 it compiles, but the following statement fails silently. The call to LoadXML does return an object, but the following call to SelectSingleNode fails silently. Nohting is returned to ContactsResultUrl. It remains an empty string.

Dim contactsRequestDoc As DOMDocument
Set contactsRequestDoc = LoadXml(url)
ContactsResultUrl = contactsRequestDoc.SelectSingleNode("/ApiResponse/ResultUrl")