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?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.