
ADO Update (using VB) of Memo fields in Access 97 DB
I'm about at my wit's end.
I'm parsing a bunch of Word documents to pull out various data from
them and sticking them into an Access 97 database. Everything works
perfectly except that I cannot update Memo fields.
At first, only key elements of the info in the Word document (such as
Screen Name and associated variables) were pulled out and put in the
database, but now I need to also pull out all the question text in
paragraphs adjacent to a Screen. The question text is stored in a
Memo field since it is quite often > 255 characters long.
The following code works if I'm using a database that already has data
in it (1,745 records in the table I'm updating, 1,058,000 bytes), but
fails to post the data when run against a clean database (that only
has between 1 and 5 records in the table, 267,000 bytes). I'm
beginning to think the problem is with how I'm connecting to the
database because:
a) I've read numerous messages on both the web and Usenet stating that
people are successfully storing data in Memo fields in Access
databases
and
b) If I open Access and cut and paste data from the Word doc into the
Access database the data is stored and persists.
I'd be very appreciative of any suggestions anyone might have. My
code is below.
I connect to the database like this:
Public Function ConnectDB(strDBName As String) As Boolean
' Setup connection to the database
Set conn = New ADODB.Connection
With conn
.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & strDBName
.CursorLocation = adUseServer
.Open
End With
' setup my global recordset object
Set rsSpecScrn = CreateDynRS("tblSpecScrn", conn)
ConnectDB = True
End Function
The record set is setup like this:
Private Function CreateDynRS(tbl As String, conn As ADODB.Connection)
As ADODB.Recordset
' Creates an ADO recordset that can be updated
Dim rs As New ADODB.Recordset
With rs
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.Open tbl, conn
End With
Set CreateDynRS = rs
End Function
And this is the code that is adding in the question text, and is
currently failing:
Public Function AddQText(ScrnID As Long, QText As Variant) As Boolean
' Find the Scrn by the specified ID and assign the Question Text to it
' If we have any data in the string
If Len(Trim(QText)) > 0 Then
With rsSpecScrn
' Move to the correct record
.Find "id=" & ScrnID, 0, adSearchForward, adBookmarkFirst
' Write memo data to the database
If IsNull(.Fields("QuestionText")) Then
.Fields("QuestionText").AppendChunk QText
Else
' The first call to AppendChunk overwrites the previous data,
' so it's necessary to do the concatenation on my own
.Fields("QuestionText").AppendChunk & _
.Fields("QuestionText") & Chr(13) & QText
End If
MsgBox "We've got the data in our buffer: " & vbCr & _
.Fields("QuestionText")
.Update
MsgBox "Now the data is gone:" & vbCr & _
.Fields("QuestionText")
' We always seem to return True here - no error messages
ADOAddQText = (.Status = adRecOK)
End With
End If
End Function