• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Access with ADO


I'm trying to make a code generated message that will be saved to a field of type Memo in an Access database.

In the message I am trying to break the message into parts, like paragraphs.  I first tried using:

LFCR = Chr(13) + Chr(10)

And inserting LFCR where I wanted the new paragraph, however, this resulted in no data being saved to the database.

How can I get the breaks into the Memo field?


  • 6
  • 4
1 Solution
can u post the code?

syntelAuthor Commented:
Which part?
syntelAuthor Commented:
It's something like this:

Set adoRecordset = New Recordset

String = My Name & Date & Title & LFCR & "Some Info Here"

adoRecordset.Fields(15) = String


When the LFCR is present no data is saved to the field.  When LFCR is removed the data is saved but all the data is on 1 line.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Hi Syntel,

I have foll. suggestions -
String is a reserved word in VB.
use VBCrLf instead of LFCR
Try printing the same thing on a message box and check if it comes on the other line.

Hi Syntel,

Following is a quote from online help

"Use the AppendChunk and GetChunk methods and the FieldSize property to get or set a value in an OLE Object or Memo field of a Recordset object."

Applies To
Field Object
Appends data from a string expression to a Memo or Long Binary Field object in a Recordset.
recordset ! field.AppendChunk source
The AppendChunk method syntax has these parts.
Part      Description

recordset      An object variable that represents the Recordset object containing the Fields collection.
field      An object variable that represents the name of a Field object whose Type property is set to dbMemo (Memo), dbLongBinary (Long Binary), or the equivalent.
source      A Variant (String subtype) expression or variable containing the data you want to append to the Field object specified by field.
You can use the AppendChunk and GetChunk methods to access subsets of data in a Memo or Long Binary field.
You can also use these methods to conserve string space when you work with Memo and Long Binary fields. Certain operations (copying, for example) involve temporary strings. If string space is limited, you may need to work with chunks of a field instead of the entire field.
If there is no current record when you use AppendChunk, an error occurs.
·      The initial AppendChunk operation (after an Edit or AddNew call) will simply place the data in the field, overwriting any existing data. Subsequent AppendChunk calls within the same Edit or AddNew session will then add to the existing data.
·      In an ODBCDirect workspace, unless you first edit another field in the current record, using AppendChunk will fail (though no error occurs) while you are in Edit mode.
·      In an ODBCDirect workspace, after you use AppendChunk on a field, you cannot read or write that field in an assignment statement until you move off the current record and then return to it. You can do this by using the MoveNext and MovePrevious methods.
AppendChunk and GetChunk Methods Example

just see the example below:
AppendChunk and GetChunk Methods Example (VB)
This example uses the AppendChunk and GetChunk methods to fill an image field with data from another record.

Public Sub AppendChunkX()

   Dim cnn1 As ADODB.Connection
   Dim rstPubInfo As ADODB.Recordset
   Dim strCnn As String
   Dim strPubID As String
   Dim strPRInfo As String
   Dim lngOffset As Long
   Dim lngLogoSize As Long
   Dim varLogo As Variant
   Dim varChunk As Variant
   Const conChunkSize = 100

   ' Open a connection.
   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
   ' Open the pub_info table.
   Set rstPubInfo = New ADODB.Recordset
   rstPubInfo.CursorType = adOpenKeyset
   rstPubInfo.LockType = adLockOptimistic
   rstPubInfo.Open "pub_info", cnn1, , , adCmdTable
   ' Prompt for a logo to copy.
   strMsg = "Available logos are : " & vbCr & vbCr
   Do While Not rstPubInfo.EOF
      strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
         Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & _
         vbCr & vbCr
   strMsg = strMsg & "Enter the ID of a logo to copy:"
   strPubID = InputBox(strMsg)
   ' Copy the logo to a variable in chunks.
   rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
   lngLogoSize = rstPubInfo!logo.ActualSize
   Do While lngOffset < lngLogoSize
      varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
      varLogo = varLogo & varChunk
      lngOffset = lngOffset + conChunkSize
   ' Get data from the user.
   strPubID = Trim(InputBox("Enter a new pub ID" & _
                            " [must be > 9899 & < 9999]:"))
   strPRInfo = Trim(InputBox("Enter descriptive text:"))

   ' Add the new publisher to the publishers table to avoid
   ' getting an error due to foreign key constraint.
   cnn1.Execute "INSERT publishers(pub_id, pub_name) VALUES('" & _
                  strPubID & "','Your Test Publisher')"
   ' Add a new record, copying the logo in chunks.
   rstPubInfo!pub_id = strPubID
   rstPubInfo!pr_info = strPRInfo

   lngOffset = 0 ' Reset offset.
   Do While lngOffset < lngLogoSize
      varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
      rstPubInfo!logo.AppendChunk varChunk
      lngOffset = lngOffset + conChunkSize
    ' Show the newly added data.
   MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
      "Description: " & rstPubInfo!pr_info & vbCr & _
      "Logo size: " & rstPubInfo!logo.ActualSize

   ' Delete new records because this is a demonstration.
   cnn1.Execute "DELETE FROM pub_info " & _
      "WHERE pub_id = '" & strPubID & "'"

   cnn1.Execute "DELETE FROM publishers " & _
      "WHERE pub_id = '" & strPubID & "'"


End Sub

syntelAuthor Commented:
What's all this?  My problem is one of formatting only.
syntelAuthor Commented:
Also the word String was not meant to be taken literally! Pretend it's something else.
syntelAuthor Commented:
Adjusted points to 200
Do you try using vbcrlf?
syntelAuthor Commented:
Close enough.  This works when text is present both before and after but not before and not after.  I can work out the bumps thanks!

Hi Syntel,

I think you just missed my comments.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now