Problems with memo field on Access 2000

[dsoutherland]

I just converted a VB6 application/Access 97 database to Access 2000. Now code that used to retrieve data from memo fields in the tables only returns NULL. Any ideas? Interesting point is that when I run the code in debug and the data is retrieved from the table, using the immediate window I can display the data value the FIRST TIME. If I try to display the data value a second time, the data is returned NULL. Here is the code used to fill a data grid where rs is the recordset:

With grd

If (Not rs.EOF) Then
lintDBNumberOfFields = rs.Fields.Count
.Cols = lintDBNumberOfFields
ReDim ChunkyField(lintDBNumberOfFields)
llngWidth = 8 * .GridLineWidth
For lintX = 0 To lintDBNumberOfFields - 1
.Col = lintX
.Text = rs.Fields(lintX).Name
ChunkyField(lintX) = (rs.Fields(lintX).Type = adLongVarChar)
Select Case rs.Fields(lintX).Type
Case adChar, adVarChar, adVarWChar, adWChar, adLongVarChar, adLongVarWChar
.ColAlignment(lintX) = flexAlignLeftCenter
.WordWrap = True
If lintX <> 0 Then
If rs.Fields(lintX).DefinedSize > 10 Then
If rs.Fields(lintX).DefinedSize > 50 Then
If (lintX = lintDBNumberOfFields - 1) And (.Width - llngWidth > 5000) Then
.ColWidth(lintX) = .Width - llngWidth
Else
.ColWidth(lintX) = 5000
End If
Else
.ColWidth(lintX) = 80 * rs.Fields(lintX).DefinedSize
End If
Else
.ColWidth(lintX) = -1
End If
End If
Case Else
If lintX <> 0 Then
.ColWidth(lintX) = -1
End If
End Select
End if
Wend

 

[LostInCode]

This is correct (that a second attempt to retrieve a blob field produces a NULL).
This is due to the way long binary fields are retrieved, especially when over 256 bytes.

You should anyways be using the GetChunk method, or a Stream object to retrieve a blob field.
Also wise is to use a second recordset to retrieve just the long binary data, and then only if the user really needs to see the data (use a command button "Memo").
If a grid, it doesn't make much sense to display a memo field, usually intended for a lot of data, or even a picture.

If it contains text, then save the data to a variant(string) if you need to use it again.

If you move off of the record (or page) and back again, you should be able to get the data again.

 

[dsoutherland]

I seem to have a solution though I do not know why. I changed the recordset cursor type to be adStatic rather than adForwardOnly and the code now handles the memo field correctly. Can somebody explain this, please? I have read the doc about the cursor types and it does not explain.