ntext problem with text box control

[Keith G Hicks]

I have an MS Access app (it's an mdb, not an adp) that is tied into a SQL
2000 backend. One of the forms is in bound mode to a single row in one of
the major tables (there are some 15000 rows but I do a select on it to just
get one row at a time).

There is an ntext column in the table for ongoing client notes. Most rows in
the table have limited amount of data in the notes column (maybe a few pages
if you were to copy it into a word doc). But one of the clients has
substantial amount of information. It's nearly 40 pages now if copied into a
word doc.

I'm just using a standard MS Access text box control on the form for the
notes. The row that has some 40 pages now seems to have reached some kind
of limit. THere is a button on the form that through code adds the date and
user name to the beginnign of the note data so that they can start typing
whatever notes right under that. (not a good design but it's not mine, I
inherited this). The button works fine. They can click it over and over and
over and it faithfully adds the new note header info to the note column. But
now they can't type anything under it. If they move to a different row in
the table and add notes to that row it works fine. It's just this one that
has some 40 pages of data in it. NTEXT is supposed to allow up to
1,073,741,823 characters. 40 pages doesn't even come close to that and if it
did then they would get an error when clilckign the button that adds the
date/user info in code.

I'm thinking that there's some sort of limitation with MS Access here
because I tried doing this in Delphi with ADO and was able to get 350 pages
of information into the same table column via a Delphi memo control on a
form. Can anyone give me an idea as to what migth be happenign here and a
possible solution?

Just a quick note too, sometimes when trying to add information in the
Access version I get a warning "<application name is here> won't be able to
undo this action or subsequent actions. To make more resources available so
that Access can record further desing actions, close and repoen this window.
Do you want to continue anyway?" (this does not come up in Delphi)

Thanks,

Keith

 

[John Nurick]

Hi Keith,

You're right, it's a limitation (I guess of the Access textbox control),
implicit in the descriptoin of the Memo field in Help under "Access
Specifications". To get beyond 65535 characters you need to write code
(that's why the header is working even though the user can't type
anything).

So one approach would be to normalise the data to separate out each
day's notes; another to write code to pass the long string to a more
capable text editor. But first I'd check out some of the rich text
controls to see if they can be bound to larger fields. (See e.g.
www.lebans.com and www.fmsinc.com).

 

[Keith G Hicks]

Thanks John for confirming that. I'm glad to hear that it's that simple. I
would love to change the program and normalize the data better. I've
suggested that to this client before. There are other areas of the program
that were handled in a similar way by the original programmer. In fact he
even has them putting entire addresses into a single control rather than
separate address, city, state, zip fields. He had to write a series of
complicated functions to parse the addresses when he needed to pull out
states or zip codes. It's worked like that for 10 years but it's kind of
ugly and of course relys on the users to enter the data exactly in the right
format. Ugh.

Hi Keith,

You're right, it's a limitation (I guess of the Access textbox control),
implicit in the descriptoin of the Memo field in Help under "Access
Specifications". To get beyond 65535 characters you need to write code
(that's why the header is working even though the user can't type
anything).

So one approach would be to normalise the data to separate out each
day's notes; another to write code to pass the long string to a more
capable text editor. But first I'd check out some of the rich text
controls to see if they can be bound to larger fields. (See e.g.
www.lebans.com and www.fmsinc.com).