ADO Update of Memo Fields 
Author Message
 ADO Update of Memo Fields

I've got a perplexing problem.

I have an Access 97 database with a Memo field that I can't update
using ADO v2.5.  All other data is stored perfectly.

What I'm doing is reading information out of a bunch of Word docs and
storing the information in the database.  I need to use the Memo field
for this particular part of the data because it will be holding entire
paragraphs some of which will be > 255 chars long.

Anyway, searching around the web, I found this link:
http://www.*-*-*.com/ ;EN-US;q194975
which talks about storing Binary Large OBjects in the memo field of a
database, but it hasn't helped.  Looking at the code at the page
above, it appears that the only part of it that applies to me (since
I'm not reading my Long Text data from a file) is the part about using
AppendChunk() so I updated my code to use that method (before, I was
just doing a straight assign, i.e., fld.Value = MyData).

When stepping through the code I can check the fld.Value after the
call to AppendChunk() and see that it is correct, but it gets reset to
Null the moment I call Update.  Any help here will be greatly
appreciated.

Here's the code:

Public Function AddQText(ScrnId As Long, QText As String) 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

    ' rsSpecScrn is a global ADODB.Recordset created
    ' and opened elsewhere by CreateDynRS() (see below)
    With rsSpecScrn

      ' Get to the record for the specified Scrn
      .Find "id=" & ScrnId, 0, adSearchForward, adBookmarkFirst

      ' Id is an Autonumber field, Name a Text field (set to length 50)
      MsgBox "See we're on the correct record: " & vbCr & _
        .Fields("Id") & " - " & .Fields("Name")

      ' 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 have data here: " & vbCr & _
         .Fields("QuestionText")
      .Update
      MsgBox "Now it's Null: " & vbCr & _
         .Fields("QuestionText")

      ' We always seem to return True here
      ADOAddQText = (.Status = adRecOK)
    End With
  End If
End Function

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



Mon, 23 Aug 2004 21:40:45 GMT  
 ADO Update of Memo Fields
Now, this is odd.  When I plunk this code into a little
test project at home with a small three field Access 97
database everything works like expected, that is, the
data persists after the update.

If anyone has any insights, I'd be interested.  Otherwise,
it's going to be a long hard road of debugging with the
code on Monday...


Quote:
> I've got a perplexing problem.

> I have an Access 97 database with a Memo field that I can't update
> using ADO v2.5.  All other data is stored perfectly.

> What I'm doing is reading information out of a bunch of Word docs and
> storing the information in the database.  I need to use the Memo field
> for this particular part of the data because it will be holding entire
> paragraphs some of which will be > 255 chars long.

> Anyway, searching around the web, I found this link:
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q194975
> which talks about storing Binary Large OBjects in the memo field of a
> database, but it hasn't helped.  Looking at the code at the page
> above, it appears that the only part of it that applies to me (since
> I'm not reading my Long Text data from a file) is the part about using
> AppendChunk() so I updated my code to use that method (before, I was
> just doing a straight assign, i.e., fld.Value = MyData).

> When stepping through the code I can check the fld.Value after the
> call to AppendChunk() and see that it is correct, but it gets reset to
> Null the moment I call Update.  Any help here will be greatly
> appreciated.

> Here's the code:

> Public Function AddQText(ScrnId As Long, QText As String) 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

>     ' rsSpecScrn is a global ADODB.Recordset created
>     ' and opened elsewhere by CreateDynRS() (see below)
>     With rsSpecScrn

>       ' Get to the record for the specified Scrn
>       .Find "id=" & ScrnId, 0, adSearchForward, adBookmarkFirst

>       ' Id is an Autonumber field, Name a Text field (set to length 50)
>       MsgBox "See we're on the correct record: " & vbCr & _
>         .Fields("Id") & " - " & .Fields("Name")

>       ' 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 have data here: " & vbCr & _
>          .Fields("QuestionText")
>       .Update
>       MsgBox "Now it's Null: " & vbCr & _
>          .Fields("QuestionText")

>       ' We always seem to return True here
>       ADOAddQText = (.Status = adRecOK)
>     End With
>   End If
> End Function

> 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



Tue, 24 Aug 2004 08:44:20 GMT  
 ADO Update of Memo Fields
Yet another wrinkle back here at work.  If I use a small test database
with only 5 records in the table containing the Memo field
(TEST~1 MDB       268,288  03-06-02 12:49p test_blank.mdb)
the Update doesn't write the text data to the Memo field.

However, if I use a copy of this database that has data in it already,
containing 1745 records in the table containing the Memo field
 (TEST MDB     1,058,816  03-11-02 10:31a test.mdb)
the Update works as expected, with the code below.

If anyone has any pointers to where the behavior of AppendChunk() is
more fully explicated, I'd appreciate it, since none of the resources
I've found so far on the Web mention that success of an AppendChunk is
dependent upon the size of the database being written to.

Thanks.


ejaculated:

Quote:
>Now, this is odd.  When I plunk this code into a little
>test project at home with a small three field Access 97
>database everything works like expected, that is, the
>data persists after the update.

>If anyone has any insights, I'd be interested.  Otherwise,
>it's going to be a long hard road of debugging with the
>code on Monday...



>> I've got a perplexing problem.

>> I have an Access 97 database with a Memo field that I can't update
>> using ADO v2.5.  All other data is stored perfectly.

>> What I'm doing is reading information out of a bunch of Word docs and
>> storing the information in the database.  I need to use the Memo field
>> for this particular part of the data because it will be holding entire
>> paragraphs some of which will be > 255 chars long.

>> Anyway, searching around the web, I found this link:
>> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q194975
>> which talks about storing Binary Large OBjects in the memo field of a
>> database, but it hasn't helped.  Looking at the code at the page
>> above, it appears that the only part of it that applies to me (since
>> I'm not reading my Long Text data from a file) is the part about using
>> AppendChunk() so I updated my code to use that method (before, I was
>> just doing a straight assign, i.e., fld.Value = MyData).

>> When stepping through the code I can check the fld.Value after the
>> call to AppendChunk() and see that it is correct, but it gets reset to
>> Null the moment I call Update.  Any help here will be greatly
>> appreciated.

>> Here's the code:

>> Public Function AddQText(ScrnId As Long, QText As String) 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

>>     ' rsSpecScrn is a global ADODB.Recordset created
>>     ' and opened elsewhere by CreateDynRS() (see below)
>>     With rsSpecScrn

>>       ' Get to the record for the specified Scrn
>>       .Find "id=" & ScrnId, 0, adSearchForward, adBookmarkFirst

>>       ' Id is an Autonumber field, Name a Text field (set to length 50)
>>       MsgBox "See we're on the correct record: " & vbCr & _
>>         .Fields("Id") & " - " & .Fields("Name")

>>       ' 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 have data here: " & vbCr & _
>>          .Fields("QuestionText")
>>       .Update
>>       MsgBox "Now it's Null: " & vbCr & _
>>          .Fields("QuestionText")

>>       ' We always seem to return True here
>>       ADOAddQText = (.Status = adRecOK)
>>     End With
>>   End If
>> End Function

>> 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



Fri, 27 Aug 2004 23:36:56 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ADO Update of Memo Fields

2. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

3. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

4. Memo Field Update Problem ADO JET

5. ADO Update (using VB) of Memo fields in Access 97 DB

6. Unable to update memo field in Access2002 with ADO

7. ADO/SQL - Updating large memo field

8. ADO, Access, Memo-Field update error at size above 64K

9. Updating data to a memo field using SQL UPDATE

10. Append memo field to a different memo field

11. Memo fields - problems reporting 2 memo fields

12. Problem updating memo field

 

 
Powered by phpBB® Forum Software