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!