I am trying to convert data from Access 2.0 to SQL Server
2000. I have ran into a few hurdles and worked through
them all except 1. I cannot get the ODBC Data reader to
return the value of a large amount of data in the memo
field. If there is a small amount of data in the memo
field, then it works fine. But as soon as i hit a row
with a lot of data in that field, it bombs with a error
operation not valid with this type of object. I have used
the item property of the data reader and the GetString and
GetChars methods. All work fine until that row.

How do I read a large amout of data from an Access field
with a Memo data type?

Please help!

James Hansen

[quoted text, click to view]

¤ I am trying to convert data from Access 2.0 to SQL Server
¤ 2000. I have ran into a few hurdles and worked through
¤ them all except 1. I cannot get the ODBC Data reader to
¤ return the value of a large amount of data in the memo
¤ field. If there is a small amount of data in the memo
¤ field, then it works fine. But as soon as i hit a row
¤ with a lot of data in that field, it bombs with a error
¤ operation not valid with this type of object. I have used
¤ the item property of the data reader and the GetString and
¤ GetChars methods. All work fine until that row.
¤
¤ How do I read a large amout of data from an Access field
¤ with a Memo data type?
¤

Using the Jet OLEDB provider is preferable to ODBC. Here is an example:

Sub ReadMemoFromAccess()

Dim retVal As Long
Dim FieldLen As Int32

Dim MemoCol As Integer = 1 ' the column # of the Memo field in the query
Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("SELECT [record ID], MemoField FROM Table1 WHERE
[record id] = 1", AccessConnection)
AccessConnection.Open()
Dim dr As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
dr.Read()
FieldLen = dr.Item(MemoCol).Length
Dim MemoBuffer(FieldLen - 1) As Char
Dim startIndex As Integer = 0
retVal = dr.GetChars(1, startIndex, MemoBuffer, 0, MemoBuffer.Length)
Console.WriteLine(MemoBuffer)
dr.Close()
AccessConnection.Close()

End Sub


Paul ~~~ pclement@ameritech.net