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 = "https://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

• Anonymous
  February 25, 2010
  What's the code of the ODataReadFeed function?

• Anonymous
  February 25, 2010
  @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.

• Anonymous
  May 22, 2017
  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).

  • Anonymous
    May 23, 2017
    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 IfRather 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.
    • Anonymous
      May 25, 2017
      The $metadata is just what I needed. It even has the Keys.Thank you!I'll let you know how it works-out.

• Anonymous
  November 14, 2017
  Hi I was just wondering, If you have a really long URL how do you get results back from the odata service with VBA? My query consits of over 50 filter clauses but because of the URL limitations I cannot request more records than this. Is there some other object I can leverage to make a large query possible in vba?

  • Anonymous
    November 14, 2017
    Esai, there's usually a limit on how long URLs can be, which is probably what you're running into. A way to work around this is to make a POST $batch submission, so the URL goes as part of the body. To do that though, you first need to make sure the server will support $batch submissions, and then you'll need to do some extra work on the VBA side to form the request and parse out the results from the batch response. I don't have anything on hand showing this for VBA, because $batch is really mostly meant for update scenarios, although it does help in this case as well.

• Anonymous
  November 19, 2018
  Marcelo, Thank you for this code. I was able to get all the way through until AppendFeedToTablers.Fields(strColumnName).Value = objEntry.item(strColumnName) where it failed on 4th pass. It created the table with 3 fields and no data. We have a few legacy Access databases that are still being used. Therefore, I am researching the potential of using Access database with ODATA feed from SAP Business byDesign as a stepping stone until they are replaced. I am actually an AS400 programmer so I am having a fun time following the code here. If we can do this with VBA or .Net then I can get a coder to actually do it. I wish to get 1 datasource from SAP into Access via ODATA. I just wish to prove feasibility. Is there anything in Power BI? A couple of colleagues have recommended it. Thanks