Hands on: Memos are made of this

There has been a huge amount of interest in adding support for extra data types to database engines in recent times.

In past columns I have covered the arrival of the multi-valued data type in Access and in September I briefly mentioned the spatial data types due to arrive with SQL Server 2008. Support for XML as a data type appeared in SQL Server 2005.

Access has no direct support for XML but it’s still possible to make use of XML within an Access database if you so wish. If that sounds weird, remember that XML is simply text and as such is amenable to being stored in text and memo fields, probably mainly the latter because XML is rather a verbose language, so it won’t take much data before you run out of space in a text field. Since XML is just text, you are free to write your own functions that manipulate the stored text as XML.

This is exactly what Brian Jones has done in a database about which he emailed me recently. If you’ve bought the DVD edition of PCW, you’ll find DBCNov07.mdb on the cover disc.
It is also on the web.

There now follows an obligatory warning: I haven’t checked out this code and application in depth. As with all code from a third party, you might want to treat it with some caution initially; perhaps using it on an isolated machine until you are sure it is OK. I don’t want this to sound as if I don’t trust Brian, I do. But we all need to be careful.

Brian was kind enough to provide the database so others can benefit from inspecting his XML handling routines and seeing them in action, and I’ve included it for that reason. As anyone who writes code will know, it’s a lot easier to develop your own code if you have a working example to study and compare, so thanks to Brian for this.

As an example of the sort of function you can write, here is one of Brian’s XML handling functions:

Function ParseXML(strXML As String, strTag As String) As String
Dim str As String
Dim lngPos1 As Long
Dim lngPos2 As Long
Dim lngLenStrXML As Long
Dim TagStart As String
Dim TagEnd As String
lngLenStrXML = Len(strXML)
TagStart = “<” & strTag & “>”
TagEnd = “</” & strTag & “>”
Do
lngPos1 = InStr(lngPos2+ 1, strXML, TagStart) + Len(TagStart)
If lngPos1 = Len(TagStart) Then Exit Do
lngPos2 = InStr(lngPos1, strXML, TagEnd)
If lngPos1 > Len(TagStart) Then str = str & Mid(strXML, lngPos1, lngPos2 - lngPos1) & vbCrLf
Loop Until lngPos1 = Len(TagStart)
If Len(str) Then str = Left(str, Len(str) - 2)
ParseXML = str
End Function

Memo to self…
As mentioned above, the data type that’s most likely to be used by anyone wanting to use XML within Access is the memo. Indeed, Brian has done so in his sample database. It was curious, then, that I received a follow-up email from John Clark about the apparently erratic behaviour of this data type.

In the July Q&A pages, I answered John’s question about concatenating text into an Access memo field and in doing so turned up some oddities in its behaviour, writing: “Unfortunately Access doesn’t display the memo field contents at all elegantly, or even at all. If you click around the table the text will sometimes appear. A bug rather than a feature, I fear.”

John undertook some further investigation and these are his findings: “The workable limit on the memo field seems to be far less that the supposed 65,000 characters, which is typically about 11,700 words or 63,000 characters with spaces.

Trying to paste more than about 7,000 words failed with a message: ‘The text is too long to be edited’.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!