Recordset Truncation

[sxschech]

I have a txt file that I imported and need to parse out so that it can be usable. I built a query, but needs further processing to generate the table, so have tried using a recordset, it works, except that if the data are longer than 255 it truncates. I looked through various posts and tried suggestions as being sure the underlying table is long text (memo in older versions). Another suggestion was to save the data in a table and use that as the recordset rather than a query, that way it is not using any functions that I used in the query such as left, mid, iif etc. This did not work either. I also tried file system object reading the text file directly line by line. If I debug.print, I see the full string of text, but the table does not get the full string, despite the data type of the field set to long text.

sample string
[tt]
1. This is the first line and is 295 chars wide
A. This is the second line
B. This is the third line
*C. This is the fourth line
D. This is the fifth line
2. This is the sixth line
[/tt]

table result should be something like this:

qno   qtext                                       response    rtext                correct         <==field name
number long text                                  short text   long text            yes/no         <==Data type
1    This is the first line and is 295 chars wide    A    This is the second line    0
1    This is the first line and is 295 chars wide    B    This is the third line     0
1    This is the first line and is 295 chars wide    C    This is the fourth line   -1
1    This is the first line and is 295 chars wide    D    This is the fifth line     0

code snippet

rs.AddNew
rs!qno= rsList!Questionno
rs!qText = rsList!Text1
rs!response = rsList!type
rs!rtext = rsList!Text1
rs!correct = rsList!correct
rs.Update
rsList.MoveNext

 

[missinglinq]

Perhaps Allen Browne article will help:

http://allenbrowne.com/ser-63.html

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[sxschech]

Thanks, missinglinq. That was one of the sites I looked at. Seems like it is more applicable to queries and concatenation. I have the actual data and am trying to split out the data rather than combine it. Maybe I'll try a fake concatenation to apply the suggestions there and see if that works and see what happens if I split out first 254 chars and then the rest and reconcatenate them down the road. I'm on another task at the moment so may not get back to trying this out until next week.

 

[sxschech]

Got it working. Added a new long text field and deleted the original long text field.

How I got there... Tested out splitting and concat by adding a new long text field at the end of the table. Then had the code put the first 254 chars in the first text field and the rest in the new field, ran a query to concat them and it showed the fully combined text data. Then tried an update query, if I put the data in the original text field, it truncated, while in the new field it took in all the data. Based on that, I deleted the original field and moved the new field to the position in the table where it should reside, then ran the original code without splitting or concatenating and it worked fine inserting the entire text string. Not sure why the original field would not accept all the data even though was formatted as long text. With this replaced field, I found I could now use the query directly rather than saving it to a separate table first.