I intended to follow on from part one a bit sooner but it’s been really busy at the office!
In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working.
While it did the job I highlighted the following limitations.
- It only fetches the last twenty updates – if there have been twenty five updates since you last did it then you’re out of luck.
- Conversely, if there have only been a few updates it’ll still fetch the last twenty giving you duplicate items.
- There is also the problem of needing to run this manually.
- It doesn’t automatically update the SharePoint list. We have to repeat the list creation process every time.
- Finally, it doesn’t deal with protected Twitter accounts at all!
This post will deal with the items 1, 2 and 5 and is actually pretty easy.
We’re going to use a bit of VBA to make Access a little smarter. I’m not a programmer/developer so if the code looks silly or inefficient….there is a good reason for it!
Also you’ll notice a distinct lack of error handling. This is just an exercise to see if it would work so code is kept to a minimum!
The Twitter API method I used in the previous post had a parameter where you can specify a tweet ID and the API will return all tweets since then.
So the only difficulty now is actually figuring out what tweet ID to use.
If only we had some sort of list we could query to do this. 🙂
Again this is really easy as we’re using Access which is built for the job!
Here’s the bit of SQL I crafted
"SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"
I’m using the VAL function to convert the tweet ID to a number as I had some weird sorting issues.
I wrapped this is in a VBA function called “Get_Last_Tweet_ID”
Function Get_Last_Tweet_ID() As String
Dim db As Database
Dim strSql As String
Dim rstData As DAO.Recordset
Dim strID As String
Set db = CurrentDb()
strSql = "SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"
Set rstData = db.OpenRecordset(strSql)
If rstData.EOF = False Then
strID = rstData("TwitID")
strID = ""
Set rstData = Nothing
Get_Last_Tweet_ID = strID
So this function will simply return a tweet ID we can plug into the API call and it’ll only return the most recent tweets. If the function returns an empty string we just don’t bother to run the API call.
The next question you’re probably asking is HOW do you run the API call?
Remember the wizard we ran in the last post to import the XML? All that wizard does is use a VBA function called ImportXML
The parameters are the same options asked for by the wizard.
A data source and whether you want to import the data, the structure or both.
That’s the first part – but since I want to be able to take protected accounts into consideration we can’t just & strScreenName & "&since_id=" & strLastID, "False", strUser, strPassword
Get_Latest_Tweets = myXML.responseText
Note: Depending on what you have on your machine you may need to use “Microsoft.XMLHTTP” instead.
This function takes a couple of parameters, the ScreenName of the Twitter account to retrieve, a username and password for authentication and a Tweet ID.
We check to see if we passed an ID. If not then don’t use the “since_id” parameter and just fetch the last 20 tweets (the default). We could add an extra parameter to fetch a specific number of tweets.
We then send the response and the function returns a string containing some XML.
Now that we have our XML from Twitter what now?
Since the ImportXML wizard requires a file to work with we need to save the XML to disk.
Sub SaveTmpXML(strXML As String)
‘ Load the XML
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"
This is a really simple procedure. We pass the XML to it, create an XMLDOM object, load the XML string and save it disk. I used a global variable for the filename so I can specify a temp path in an options table which I’ll cover later.
Finally we can import the XML into our table,
Public Function Fetch_Tweets() As String
Dim strID As String
Dim strXML As String
If Get_Settings = False Then
Debug.Print "Couldn’t get settings"
strID = Get_Last_Tweet_ID
strXML = Get_Latest_Tweets(strScreenName, strUserName, strPassword, strID)
Application.ImportXML strTempFile, acAppendData
This a wrapper function that calls all the code I’ve outlined above.
We find the last Tweet ID, fetch the tweets from Twitter, save them to an XML file and finally import them into our table.
You’ll notice the Get_Settings and Tidy_User_Table procedure calls, I’ll also cover those in a later post.
You could hook this wrapper function up to a button on a form. Once clicked it would update the table in the database with the latest tweets.
I think that’s all three issues solved. We now only fetch exactly the tweets we need and can work easily with protected tweets (Twitter recently announced a change that will break this but I’ll cover that in an upcoming post)
I’ll get part three together a bit sooner!