MS Access Integration

[kschuster1]

Hey Tim - looking at using VBA-Web with MS Access. Can I use this in MS Access? If so, any guidance? I've been trying to use some of your sample code but can't figure out the proper Reference required thanks in advance Keith

[zgrose]

You'll want to set a reference to Microsoft Scripting Runtime (scrrun.dll) for the Dictionary class.

[LeoZandvliet]

For some reason I can't get a working project in Excel to run in Access (2016) due to reasons I don't understand anymore. I even copied the modules manually from Excel over to Access, and in Access I added the Microsoft Scripting Runtime reference. Excel doesn't have any strange other references, and so doesn't Access.

When creating a WebClient that sends a WebRequest it first stops at the function WebHelpers.RegisterConverter() on this line:

web_Converter("MediaType") = MediaType

I can solve the issues in this function, RegisterConverter(), by changing the syntax of all calls from this:

web_Converter("MediaType") = MediaType

To this:

web_Converter.Add "MediaType", MediaType

But when I do that, the project stops at the first call to

WebRequest.AddHeader "SOAPAction", "CustomHeader"

Which boils down (while stepping through the function calls) to WebHelpers.CreateKeyValue():

Public Function CreateKeyValue(Key As String, Value As Variant) As Dictionary
    Dim web_KeyValue As New Dictionary

    web_KeyValue("Key") = Key ' < Runtime error !
    web_KeyValue("Value") = Value
    Set CreateKeyValue = web_KeyValue
End Function

With:

Error 438, Object Doesn't Support this Property or Method 

Then, when changing the name of the Dictionary module, so it can't be used, I run into the error:

ERROR - WebHelpers.ParseByFormat: 11000, An error occurred during parsing
2517: Microsoft Access kan de procedure XMLHelpers.ParseXml niet vinden.
(English: Microsoft Office Access can't find the procedure)

What is the difference over Access 2016 and Excel 2013 on these points that causes these troubles?

[zgrose]

Some suggestions.... Make sure you have Option Explicit on all your classes and modules Make sure the database compiles. When I copy/pasted from Excel, the VBA as-is does not compile.

Other than that, this project seems to work in Access otherwise. (see image)

[LeoZandvliet]

Many thanks for your response!

Your example runs fine indeed, key is to don't use the 'dictionary' class module and reference Microsoft Scripting Runtime.

But the error 2517 remains in Access fore some reason when using:

WebHelpers.RegisterConverter "xml", "application/xml", "XMLHelpers.DoNothing", "XMLHelpers.ParseXML"

I tried 'WebHelpers.ParseXML" also to exclude problems in XMLHelpers mmodule, but that also throws 2517.

Can you get the following test to work within Access?

Module TestVBAWeb

Public Sub TestParseXML()
    Dim Parsed As Object

    ' The following line will normally be executed by WebHelpers.ParseByFormat:
    Set Parsed = Application.Run("XMLHelpers.ParseXML", "<?xml version=""1.0"" encoding=""UTF-8""?><test><node>1</node></test>") ' throws error 2517
    'Set Parsed = XMLHelpers.ParseXML("<?xml version=""1.0"" encoding=""UTF-8""?><test><node>1</node></test>") ' works as expected

    'Set Parsed = Application.Run("WebHelpers.ParseXML", "<?xml version=""1.0"" encoding=""UTF-8""?><test><node>1</node></test>")  ' throws error 2517
    'Set Parsed = WebHelpers.ParseXML("<?xml version=""1.0"" encoding=""UTF-8""?><test><node>1</node></test>") ' function is called and throws expected error

    Debug.Print Parsed.XML
    Debug.Print Parsed.Text
End Sub

Module XMLHelpers

Option Explicit

Public Function ParseXML(Value As String) As Object
    On Error GoTo xmlHelper_ErrorHandling
    Set ParseXML = CreateObject("MSXML2.DOMDocument")
    ParseXML.Async = False
    ParseXML.LoadXML Value

    Exit Function

xmlHelper_ErrorHandling:

    Dim xmlHelper_ErrorDescription As String
    xmlHelper_ErrorDescription = "An error occurred during parsing" & vbNewLine & _
        Err.Number & VBA.IIf(Err.Number < 0, " (" & VBA.LCase$(VBA.Hex$(Err.Number)) & ")", "") & ": " & Err.Description

    WebHelpers.LogError xmlHelper_ErrorDescription, "XMLHelpers.ParseXML", 12000
    ' Err.Raise 12000, "XMLHelpers.ParseXML", xmlHelper_ErrorDescription
End Function

Public Function ConvertToXml(Value As Variant) As String
    ConvertToXml = Trim(Replace(Value.XML, vbCrLf, ""))
End Function

Public Function DoNothing(Value As String) As String
    DoNothing = Value
End Function

Public Function TextOfNode(Node As MSXML2.IXMLDOMNode, XPath As String, DefaultText As String) As String
    If Not Node.SelectSingleNode(XPath) Is Nothing Then
        TextOfNode = Node.SelectSingleNode(XPath).Text
    Else
        TextOfNode = DefaultText
    End If
End Function

[LeoZandvliet]

I kinda pin pointed the problem myself. To get an existing project to work in Access you'll have to name helper functions uniquely, as Access can't resolve (non uniquely named) functions within modules through Application.Run.

It appears that Application.Run() implementation in Access differs from Excel. See docs:

• Application.Run in Access
• Application.Run in Excel

It's designed to call a unique function name in (another) database project. So if I rewrite my test code below to use the function 'XMLHelper_ParseXML' it works:

In module XMLHelpers, rename:

Public function ParseXML(Value as String) As Object

To:

Public function XMLHelpers_ParseXML(Value as String) As Object

And call it through Application.Run as: Set Parsed = Application.Run("XMLHelpers_ParseXML", "<?xml version=""1.0"" encoding=""UTF-8""?>1")

That just means all helper functions in modules would be called through Application.Run should be named uniquely to be used in Access :( That is, if you have multiple modules with the same declared function names.

In this case:

• WebHelpers.ParseXML
• XMLHelpers.ParseXML

Now I'm able to ParseXML with:

WebHelpers.RegisterConverter "xml", "application/xml", "XMLHelpers_DoNothing", "XMLHelpers_ParseXML"

[pflugs30]

@LeoZandvliet thank you for the insightful question and answer above regarding the difference between the Access and Excel Application.Run implementations. How irritating it is that they are not consistent!