Cannot store > 255 characters in Memo field 
Author Message
 Cannot store > 255 characters in Memo field

This has to be my month for bugs. I've discovered close to 20 in the
last month in various products.

This one, I have abolutely no workaround and the KB article that
acknowledges this as an ACCESS 97 bug, isn't helpful. I deem it
as a SHOWSTOPPER. I'd appreciate any suggestions. The Article Q170218,
also seems to deal mostly with this being an SQL server problem.
If I haven't made a mistake below this is basic Access 97 functionality
that doesn't work.

With the example code below when the rst!crInfo memo field
is proved to contain > 255 characters, the Update truncates the data
at 255 in the table. Doesn't matter whether it is Addnew or Edit.

So the bottom line at the top is that there is NO WAY to get memo
data > 255 characters into a table with a RecordSet. I'd sure
appreciate anyone with any workarounds. I don't even see any patches
to MSACCESS 97, but to me this is like a showstopper bug. The KB
acknowledgment of a bug, seems to imply a very specific set of
conditions, but the examples below would imply this is a very
widespread problem. I am for instance using the ODBC 3.0 driver
3.50.342800 for Access which means the driver has either nothing to
do with it or multiple instances of the driver have the same problem.

***********************
Some of the lines below are wrapped simply because of line length.
The table is crId = autonumber
             crName = text
             crInfo = memo

*******************
An Edit of an existing record:

                Set dbs = CurrentDb
                strSQL = "SELECT crID,crName,crInfo
                      FROM tblCenters" _
                    & " WHERE crName="""
&                                                        CStr(CenterName) & """"
                Set rst = dbs.OpenRecordset(strSQL,dbOpenDynaset)
                If rst.EOF Then
                    ' We did not find the record, so we can add a new          
CareerCenter for this category
                    rst.AddNew
                    ' We supply 2 of 3 values, the 1st is autonumbered
                    rst!crName = CareerCenterName
                    rst!crInfo = Me.Container!InfoMemoBox.Value
                 End If
' It can be verified here with debug.print when rst!crInfo has >   255
characters
                 rst.Update
                 rst.Close

Looking at the table shows that only the first 255 characters were
stored.

***********************
And likewise an update!

                Set dbs = CurrentDb
                ID = CLng(Forms!MainFolder!ListBox.Column(0))
                strSQL = "SELECT crID,crName,crInfo
FROM                                            tblCareerCenters" _
                    & " WHERE crID = " & CStr(ID)
                Set rst = dbs.OpenRecordset(strSQL,dbOpenDynaset)

                If rst.EOF Then
                    .... return because no record to update.
                Else
                    ' We found the record to update
                    ' Now we can proceed to update it
                    rst.Edit
                    rst!crName = CenterName
                    rst!crInfo = Me.Container!InfoMemoBox.Value
                 End If
' Once again it can be verified here when rst!crInfo contains more than
255 chars.
                 rst.Update
                 rst.Close
Looking at the table shows that only the first 255 characters were
stored.

Wray Smallwood, Hewlett Packard



Sat, 15 Jan 2000 03:00:00 GMT  
 Cannot store > 255 characters in Memo field

Quote:

> With the example code below when the rst!crInfo memo field
> is proved to contain > 255 characters, the Update truncates the data
> at 255 in the table. Doesn't matter whether it is Addnew or Edit.

> So the bottom line at the top is that there is NO WAY to get memo
> data > 255 characters into a table with a RecordSet. I'd sure
> appreciate anyone with any workarounds. I don't even see any patches
> to MSACCESS 97, but to me this is like a showstopper bug. The KB
> acknowledgment of a bug, seems to imply a very specific set of
> conditions, but the examples below would imply this is a very
> widespread problem.

You're saying this is happening with native Jet tables? I can't
duplicate it. I can store a 1000-byte string in a memo field via a
recordset insert without a problem.

What is this InfoMemoBox of yours, some OLE control? Is it possible that
the Value property returns a max of 255 characters?

nbc



Sat, 15 Jan 2000 03:00:00 GMT  
 Cannot store > 255 characters in Memo field

Quote:

> The InfoMemoBox was a control, but wasn't involved, because it could
> have been a variable with 1000 characters assigned.

Well, I don't know about that, since you were assigning it's Value
property to your memo field...

Quote:
> The lesson: Memo fields are incompatible with a query which
> retrieves data and stores them in columns in a list box (visible or
> not) unless you don't plan to use more than 255 characters.

Yes, this behavior has been around since at least Access 2.0. Same thing
for combo boxes.

As far as the End key goes, it will take you to end of a *line* if
you're displaying more than one line at a time. Ctrl-End will take you
to the end of the field itself.

nbc



Sun, 16 Jan 2000 03:00:00 GMT  
 Cannot store > 255 characters in Memo field

Quote:

> You're saying this is happening with native Jet tables? I can't
> duplicate it. I can store a 1000-byte string in a memo field via a
> recordset insert without a problem.

> What is this InfoMemoBox of yours, some OLE control? Is it possible that
> the Value property returns a max of 255 characters?

> nbc

The InfoMemoBox was a control, but wasn't involved, because it could
have been a variable with 1000 characters assigned.

However..

Thanks for your assurance that it worked and efforts in duplicating
it. It helped to steer me to where the real problem lay. It's a lesson
in "What You See Isn't Necessarily What You Get", or the "Eyes of the
Beholder Don't Always Behold Everything".

First there is some confusing things about long memo fields when looking
at them in an opened table. If you hit the End key with
your cursor in the field, it does not take you to the end of the field
but instead to some arbitrary calculated position a few hundred
characters down the string. Hitting the right arrow key advances you
one position into the next arbitrary buffer, which allows you to
hit the END key again to get further down  the string. This also
fools you for cut and paste, because when you think you have the
entire memo field selected, in fact you may not. This is what caused
me to assume the truncation was occurring in the first place. However
none of these buffers has anything to do with 255 characters because
they were odd lengths of characters like 384 for the first jump in
one instance.

What was causing the symptoms in the first place is that only
255 characters were being displayed in a text box. However the source
of this was one of the columns of a list box where the original
query lay. Therein the problem. Each column in list box is limited
to 255 characters. So I could only get that maximum to display.

The solution lay in finding another way to store the data for a
memo field retrieved on one form so that it could be displayed in
an opened form that was used to examine it's contents. Before I
closed out this letter I was looking at module variables, or passing
a variable to the opened form, or hidden controls on the originating
form to store the memo field or specific DLOOKUP's of that memo
field. The latter sounds like the best bet.

The lesson: Memo fields are incompatible with a query which
retrieves data and stores them in columns in a list box (visible or
not) unless you don't plan to use more than 255 characters.



Sun, 16 Jan 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Displaying MS Access fields over 255 characters (Memo fields)

2. Strings > 255 from Memo fields

3. Memo > 255

4. Memo > 255

5. Memo > 255

6. Ken Getz, can you adivse re: adhInsertAtBookmark from Handbook truncates memo fields at 255

7. Access memo field won't accept more than 255 chars

8. memo fields : truncating to 255 car

9. Memo field updates longer than 255 chars

10. variable>255 characters?

11. Populating Bookmarks with strings of > 255 characters

12. displaying > 255 Characters

 

 
Powered by phpBB® Forum Software