Access with ADO

can u post the code?

alok

Which part?

It's something like this:

Set adoRecordset = New Recordset
adoRecordset.Open

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

adoRecordset.Fields(15) = String

adoRecordset.Update


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.

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.

hth
alok.

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.
Syntax
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.
      ..
Remarks
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.
Notes
·      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.
      ..
Example
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
      rstPubInfo.MoveNext
   Loop
   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
   Loop
   
   ' 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.AddNew
   rstPubInfo!pub_id = strPubID
   rstPubInfo!pr_info = strPRInfo

   lngOffset = 0 ' Reset offset.
   Do While lngOffset < lngLogoSize
      varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
         conChunkSize)
      rstPubInfo!logo.AppendChunk varChunk
      lngOffset = lngOffset + conChunkSize
   Loop
   rstPubInfo.Update
   
    ' 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.
   rstPubInfo.Requery
   cnn1.Execute "DELETE FROM pub_info " & _
      "WHERE pub_id = '" & strPubID & "'"

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

   rstPubInfo.Close
   cnn1.Close  

End Sub



hth
alok.

What's all this?  My problem is one of formatting only.

Also the word String was not meant to be taken literally! Pretend it's something else.

Adjusted points to 200

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.

alok.