Consuming OData with Office VBA – Part IV (Access)


Check out parts one, two and three if you haven’t – good stuff there!


In today’s post, we’re going to import data into Microsoft Access. I’m going to focus on having the building blocks in place, although in real-life use, you would either make this more general or more specific – more on this later.


If we’re going to import data into a table, the first thing to do is to be able to figure out what columns we should create. Once we’ve downloaded some data, we can look at what the names are in for the values in our records, represented as dictionaries in a collection. The following code makes sure we gather all the names from all the records, in case some of them are missing. That won’t be the case for today’s example, but some other OData sources might do that.


‘ Creates a table with the named text fields.
Sub CreateSimpleTable(ByVal strTableName As String, ByVal objNames As Collection)
    Dim table As TableDef
    Dim strColumnName As Variant
    Dim objField As Field
   
    ‘ Create a table and set up its fields.
    Set table = CurrentDb.CreateTableDef(strTableName)
    For Each strColumnName In objNames
        ‘ Create simple text fields for now.
        Set objField = table.CreateField(strColumnName, dbText, 255)
        objField.AllowZeroLength = True
        table.Fields.Append objField
    Next
   
    CurrentDb.TableDefs.Append table
End Sub


Once we have our table, it’s a simple matter to just add all of our dictionaries into it.


‘ Appends the entries in a collection to the named table.
Sub AppendFeedToTable(ByVal objFeed As Collection, ByVal strTableName As String)
    Dim rs As Recordset
    Dim strColumnName As Variant
    Dim objEntry As Scripting.Dictionary
   
    ‘ Open the table and add new records.
    Set rs = CurrentDb.OpenRecordset(strTableName)
    For Each objEntry In objFeed
        rs.AddNew
        For Each strColumnName In objEntry.Keys
            rs.Fields(strColumnName).Value = objEntry.item(strColumnName)
        Next
        rs.Update
    Next
    rs.Close
End Sub 


Finally, we’re going to put all of the pieces together into a routine we can call to recreate the table and add data to it. We’ll make use of a helper CollectionContains function to check whether the table exists in case we want to start with a fresh table every time.


‘ Checks whether an object collection contains an item.
Function CollectionContains(ByVal objCollection As Variant, ByVal item As Variant) As Boolean
    On Error GoTo CollectionContains_Error
    Dim objResult
    Set objResult = objCollection(item)
    CollectionContains = True
    Exit Function
CollectionContains_Error:
    If Err.Number = 3265 Then
        CollectionContains = False
    Else
        Err.Raise Err.Numberr, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If
End Function

‘ Given a URL to an OData feed, imports the data into a new table.
Sub ODataImportToTable(ByVal strUrl As String, ByVal strTableName As String, ByVal bolDropExisting As Boolean)
    Dim objDocument As MSXML2.DOMDocument60
    Dim objFeed As Collection
   
    ‘ Read the data into our collection of entry dictionaries.
    Set objDocument = ODataReadUrl(strUrl)
    Set objFeed = ODataReadFeed(objDocument.documentElement)
    Set objDocument = Nothing

    ‘ Drop the table if asked to.
    If bolDropExisting Then
        If CollectionContains(CurrentDb.TableDefs, strTableName) Then
            CurrentDb.TableDefs.Delete strTableName
        End If
    End If
   
    ‘ Create the table and add records to it.
    Dim objColumnNames As Collection
    Set objColumnNames = GetDistinctKeys(objFeed)
    CreateSimpleTable strTableName, objColumnNames
    AppendFeedToTable objFeed, strTableName
End Sub


Now to put together a sample, this is how we can use the code we just wrote to import the dataset from the OGDI site for New American Foundation’s Funding, student demographics, and achievement data at state level data. If you create an empty Access database and put here all the code we’ve written so far, you should be able to see the table in your database and double-click it to browse the data (you may need to refresh the list of tables if the EdmFundDemo table doesn’t show up immediately).


Sub Test()
    Dim strUrl As String
    strUrl = “http://ogdi.cloudapp.net/v1/naf/EduFundDemoAchievePerState/”
    ODataImportToTable strUrl, “EduFundDemo”, True
End Sub


If you wanted to make this a real general-pupose import library, you would probably want to add data types and key information. On the other hand, if you were more precise, you could mess with the schema beforehand and only append certain columns. Scripting makes all these changes easy – that’s what’s great about being able to have an environment available with Microsoft Office to tweak things to your specific needs.


Enjoy!

Comments (5)

  1. grovelli says:

    What’s the code of the ODataReadFeed function?

  2. @grovelli,

    It’s in post #2 at http://blogs.msdn.com/marcelolr/archive/2010/02/17/consuming-odata-with-office-vba-part-ii.aspx, which in turn builds on http://blogs.msdn.com/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx. I can put everything together if anyone is interested, but it’s just the little snippets you see in this series.

  3. George says:

    This has been extremely valuable, years after you wrote it. Future users will appreciate knowing that there is an additional “Part V – Missing Function” at https://blogs.msdn.microsoft.com/marcelolr/2010/09/15/missing-function-for-odata-and-office/

    The reason I am writing you, is that despite having learned a lot from your post about collections, dictionaries, and XML parsing, I cannot figure-out how to follow your suggestion, “you would probably want to add data types and key information.”

    The following pieces missing from my understanding:
    1) How do you elegantly parse the m: type=? (I can use InStr() to pick it out)
    2) How do you store the type information in the collection?
    (Only field name and value are stored in your example)
    3) How do you retrieve the type info once you’ve stored in the collection?

    I understand that you have long-ago moved-on to other projects, but this one still has value to those of us coming to OData late in the game. Your help will be greatly appreciated.

    And yes, I believe everyone would be interested in having everything together in a single place, even though apparently no one replied affirmatively. (Not everyone reaches out).

    1. George, I’m happy this was of use. I’ll try posting things at some point or another.

      1. How to parse m:type?
      In ODataReadContent, as you read properties, you can look at the attribute using the getQualifiedItem method of objChild (which represents the XML element of the property).

      Dim objType As MSXML2.IXMLDOMAttribute
      Set objType = objChild.Attributes.getQualifiedItem(“type”, ODataMetadataNamespace)
      If Not objType Is Nothing Then
      Debug.Print objType.Value
      End If

      Rather than Debug.Print, you would do something with that value to record the type. The value might be something like “Edm.Int32” – the reference for values is available at https://msdn.microsoft.com/en-us/library/dd541295.aspx. I believe that a missing type implies that it’s a string.

      2. How do you store it in the collection? / 3. How do you get it from the collection?

      There are some changes that will have to happen to do this, because you can only have one value for each element in the dictionary.

      There are a few things you could do:

      – Instead of storing a value in the dictionary, store some data structure (like another dictionary) with the property value and the property type specified separately.

      – Have a function similar to the one that reads values, but that reads type names rather than values. Or have an argument to indicate whether you care about values of type names. Then you can call the function once to extract types from a sample element, and use that.

      A more involved approach would look at the metadata document, typically available at a URL like http://www.nerddinner.com/Services/OData.svc/$metadata. The format is different but it’s a similar idea to download, parse and examine the document, and you can get detailed structure information from that.

      1. George says:

        The $metadata is just what I needed. It even has the Keys.
        Thank you!
        I’ll let you know how it works-out.

Skip to main content