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!