ADO Update (using VB) of Memo fields in Access 97 DB 
Author Message
 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
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
  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
        ' 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 & _
      MsgBox "Now the data is gone:" & vbCr & _

      ' We always seem to return True here - no error messages
      ADOAddQText = (.Status = adRecOK)
    End With
  End If

End Function

Sat, 28 Aug 2004 05:16:07 GMT  
 ADO Update (using VB) of Memo fields in Access 97 DB

I forgot to mention the structure of my database.  It's an Access 97
database.  The table in question looks like this:

Id - Autonumber
Name - Text (50)
QuestionText - Memo

Sat, 28 Aug 2004 05:18:32 GMT  
 [ 2 post ] 

 Relevant Pages 

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

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

3. Accessing empty/null Memo Fields in a Access 97

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

5. Connecting to Access 97 Database using ADO with OLE DB Driver

6. Problem writing to Access 97 DB with ADO 1.5 and 2.1 (using ODBC)

7. Connecting to Access 97 Database using ADO with OLE DB Driver

8. Converting Access 2.0 DB to Access 97 DB in VB code

9. Updating data to a memo field using SQL UPDATE

10. Using VB 3.0 to Update DBase III Memo Field

11. Using ADO Command object and Access PROCEDURE to insert Memo/Binary fields

12. Corrupt memo fields in Access 97?


Powered by phpBB® Forum Software