Appreciate Opinions on LongText Fields (1 Viewer)

[Cotswold]

In the olden days using dBASE Tables I experienced errors and sometimes corruption using the somewhat strangely named MEMO type fields in Tables. After I removed all of the MEMO fields from the dBASE tables the problems went away. When I moved to Access I then tried the Memo field in the .MDB and within a year had similar errors. Again, that could have been flaky networks, or even users switching off the PC instead of closing down. But I didn't have the time or inclination to prove it. So I took the lazy doctors' solution of treating the symptoms and not the causes. I removed the Memo fields from the .MDB and no more issues. So on this very tiny statistical analysis I have avoided Memo fields in everything Access. I will add that the issues I had were in .MDB databases. Maybe the .ACCDB are an improvement in this regard?

I am developing an application in Access2019 and using a LongText field in a main table would be an easy solution for me. The maximum number of records in any single table I expect to be in the range of 1,000 to 5,000 so quite small really. In just one table maybe 40% of the records would have text of varying lengths in a LongText field. This would be from five or six lines, up to to maybe the contents of one and at the most three A4 pages. Whilst in the other 60%, the LongText field I'd expect to be empty. I could have a separate Table containing the LongText, but my preferred design is in in the main table.

My opinion at the moment is don't use them.......just in case it give me problems later. However, as it is possible that I'm the only Access user in the World who doesn't use them, and as I say, I'm in search of an easier solution. I am wondering what the broader experience is at Access-Programmers with the LongText and what your opinion is on the use of the LongText Fieldtype?

Do you avoid them, or do you use them all the time without any hesitation, or issues?

 

[CJ_London]

Personally never had a problem- I use them when I know 255 chars is not enough or I need to use richtext and cannot format a short text field as such in a query or form for some reason.

I dont use the history feature

long text fields are actually stored in a hidden table in much the same way attachment and ole fields are. All that is in the table itself is a pointer

 

[The_Doc_Man]

You asked a similar question here and got answers.

Memo or Long Text Field Question

Hello All, Although Memo Fields are probably fine now, I'd appreciate the opinions of the forum members. Back in the dark days of DOS I found that memo fields could cause corruption. It certainly was the case using Clipper87 with dBASE tables. When I switched to Access97 it was still the...

www.access-programmers.co.uk

Did you forget having asked?

The problem will always be that some things you can call only use Short Text fields internally and therefore cannot exceed 255 characters. This is unfortunate but is a limitation of how the interfaces are implemented. It is a fact of life that some things required special handling, and LongText is one of those things.

 

[MajP]

In some of the early versions of Access memo fields were a cause of corruption on split databases, but that was a long time ago. I have not seen or heard of issues recently. If uncomfortable then put them in their own table using a true one to one (pk to pk). Simple to implement, but you should be fine.

 

[isladogs]

Just to second the earlier comments. The issues you describe with memo / long text fields were fixed around version 2002 - 20 years ago.
There are also advantages to their use e.g rich text.
There are some disadvantages also e.g. truncation is possible in queries. Can't search on long text fields

 

[Cotswold]

You asked a similar question here and got answers.

Did you forget having asked?

No TDM, I hadn't forgotten. But opinions on the LongText were side-lined by truncating and exporting to Excel, with the exception of Minty. The question became lost in truncation so to speak and had no conclusion. So here I posted it again in hope, and it does appear with more success.

 

[Cotswold]

In some of the early versions of Access memo fields were a cause of corruption on split databases, but that was a long time ago. I have not seen or heard of issues recently. If uncomfortable then put them in their own table using a true one to one (pk to pk). Simple to implement, but you should be fine.

Thanks MajP, That was also Minty's suggestion last year . I think I'll use the separate table analysis.

 

[Pat Hartman]

@MajP Since Access already stores the long text field in a separate table, what would be the point of you creating your own separate table?

 

[isladogs]

long text fields are actually stored in a hidden table in much the same way attachment and ole fields are. All that is in the table itself is a pointer

@MajP Since Access already stores the long text field in a separate table, what would be the point of you creating your own separate table?

Do either of you have evidence to support this comment?

It is true that the memo field column history feature involves the use of a deep hidden system table (actually two of them).

However, unless that feature is turned on, I don't believe this to be true (though it may once have been so with the MDB format?)
Certainly no new hidden tables are created when a long text field is added.
So if it is true then it would need to be using an existing hidden system table. If so, which?

 

[Pat Hartman]

Here's one. These people are selling a recovery tool so I would expect them to understand the inner workings of Jet/ACE

4 Common MS Access Memo Field Problems Along With It’s Fixes

Know what MS Access Memo Field Problems can obstacle you to work freely with your Access database. And how to fix MS Access Memo Field errors and problems.

www.accessrepairnrecovery.com

However, I found slightly different descriptions in In Jet Database Programmer's Programming guide (jet Only, nothing on ACE) and SQL Server 2008 R2. Both talked about some data being stored in the table but excess data being stored in a data page rather than a table. The record length limit in Access is 2000 and in SQL server is 8000. Long text in excess of what would fit within those limits after the other columns are filled, is stored in a "data page" whatever that actually is. Also, the Jet book talked about the data in the data page as being "comingled" so if there is more than one memo field or more than one expansion of a single memo field, the data from more than one record could be stored in the same data page. This may be why memos in earlier versions of Jet were more fragile and prone to corruption.

Another thing I found during my search was a fair amount of mis-information regarding the size of a long text field. There were many references that said the field held 63,999 or 64,000 bytes rather than 64K which is 65,535 bytes. Also, they didn't clarify that this hard limit is actually what a text box will display. The data field itself can hold 1 gig and you can reference the excess of 64K only with VBA. If you have more than than 64K in the field and use a text box to edit it, several references said the data gets truncated.

 

[isladogs]

Yes I saw that URL as well before posting and discounted it.
Its an advert for Stellar and I have a very low opinion of their articles which often contain inaccuracies and outdated info

As I mentioned before, MS fixed issues with memo/long text fields a long time ago
AFAIAA the long text data is not stored in a separate table or 'container file' or 'data page' (whatever those may be)
At least not now with ACCDB format even if that were the case in the long distant past.

Access specifications correctly states the limits for long text fields:

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

 

[CJ_London]

For myself, Perhaps not a deep hidden table, but I've believed for a very long time not in the table. Can't find anything to back that up as old documentation just does not appear on the web anymore, perhaps in an older Access manual (which I no longer have)

Can you provide documentation to confirm not stored in a ........ 'container file' or 'data page'

After all, there is no table for indexes


here MS says

Long Text

NOTE: Long Text was introduced in Access 2013. It replaced Memo. Lengthy text or combinations of text and numbers.

Up to 63,999 characters. (If the Long Text field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Long Text field is limited by the size of the database.)

DataType property

Office VBA reference topic

docs.microsoft.com

So potentially 2GB - the 64k limit set by using DAO (which is the default for forms). However characters take 2 bytes so would be 1Gb of characters

 

[isladogs]

For myself, Perhaps not a deep hidden table, but I've believed for a very long time not in the table. Can't find anything to back that up as old documentation just does not appear on the web anymore, perhaps in an older Access manual (which I no longer have)

Can you provide documentation to confirm not stored in a ........ 'container file' or 'data page'

After all, there is no table for indexes

I've also searched extensively for evidence to support this claim & have found nothing to back it up
More difficult to confirm something isn't true I would think

I checked in the Access 2000 Developers Handbook & found no information at all.
Nor is there anything in the manual that accompanied Access 2.0.

As a quick test I created a simple database with just one table including a memo field

and added 3 records

then looked at it in a hex editor

That was the only location where I found the data. Notice the short text & long text fields are adjacent suggesting both are stored in the same place. Certainly not proof however I've found similar outcomes in the past when examining other databases.

In fact there was a MSysIndexes table in Access 1.0 & 2.0. This is from the first version of Northwind back in 1992

These days, of course, indexes are stored as field properties

 

[Pat Hartman]

AFAIAA the long text data is not stored in a separate table or 'container file' or 'data page' (whatever those may be)
At least not now with ACCDB format even if that were the case in the long distant past.

I hear you BUT, the limit to a table "record" is 2000 (actually was. It was increased to 4000 for Jet 4 but I don't have a source for ACE although I think it is the same) bytes. Here's a very old kb article that seems to explain the storage method and apparently what "data pages" are.

Microsoft Knowledge Base Article - Q198660

DOC: Additional Jet Database Record Size Information
The information in this article applies to:
Microsoft Visual Basic Professional Edition for Windows 4.0, 5.0, 6.0
Microsoft Visual Basic Enterprise Edition for Windows 4.0, 5.0, 6.0
Microsoft Access for Windows 95 7.0
Microsoft Access 97

Summary
Because Microsoft Jet allows for variable length fields, you may
occasionally receive DAO error 3047:

"Record Too Large"
This occurs if the physical storage requirements of a record exceeds the
amount of free space available in a data page (about 2000 bytes).

Appendix A of the Microsoft Jet Database Engine Programmer's Guide provides
a list of factors you must take into account when estimating the size of a
record in a Microsoft Jet database (MDB file). The book omits some
information regarding MEMO and OLE fields.
More Information
The Microsoft Jet Database Engine Programmer's Guide, Appendix A, documents
that Microsoft Jet stores MEMO and OLE fields on separate pages (Long Value
pages) from the rest of the record. And that for every non-NULL MEMO or OLE
field, there is a 12-byte overhead in the main record that points to the
location of the start of the Long Value page chain.

Appendix A, however, does not document that Microsoft Jet 3.5 and earlier
will attempt to store the MEMO or OLE value in the main record if the size
of the data is less than or equal to 32 bytes. This results in faster lookup
of these values and does not incur an additional 2K storage overhead, but
does retain the 12-byte overhead. If the record is very large, (approaching
2000 bytes for example, the maximum record size), the MEMO and OLE fields
will be stored in a Long Value page even if 32 bytes or less.

For Jet 4.0 (VB6.1, Office 2000), this limit is increased to 64 bytes (not
64 characters).

Also for Jet 4.0, the page size is increased from 2K to 4K and the maximum
record size has increased to approximately 4000 bytes.
First Published: Nov 10 1998 4:58AM

 

[gemma-the-husky]

Are short text fields stored as full length strings. Ie if a short text is defined as 100 chars, is space for 100 chars reserved in advance. Maybe? In fact, I presume so. Now I doubt for a moment that a long text field is reserved, and I assume there must be a pointer to an area of memory. In which case is a long text is most likely defined by multiple page size blocks of data, linked together dynamically. If so, extending a long text would be achieved by referencing more page blocks at the end of the last block, (linked list sort of structure) until you run out of disk space. Something like that. That might account for why some processes can't be done with memo fields. That would probably also explain why the 4000 byte limit does not include memo fields.

[edit - looks like that was on the wrong track!]

 

[CJ_London]

per the link provided in post #12

Text	NOTE: Text was replaced by Short text in Access 2013.(Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.	Up to 255 characters or the length set by the FieldSize property, whichever is less. Microsoft Access does not reserve space for unused portions of a text field.
Short Text	NOTE: Short Text was introduced in Access 2013. It replaced Text.(Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.	Up to 255 characters or the length set by the FieldSize property, whichever is less. Microsoft Access does not reserve space for unused portions of a text field.

my interpretation of
Microsoft Access does not reserve space for unused portions of a text field.

is that it only takes the space required - so 'ABC' in a field with size set to 200 is stored in a space required for 3 chars, not 200.

Which begs the question. if the value is changed to 'ABCDE', does access move the entire record to another location? Implication is it does.

 

[isladogs]

Apologies, I forgot to post a response to this thread with my findings.
On further investigation, shorter records in memo/long test fields are stored directly in the table itself
However, when the record length exceeds the limit for the page size, the long text data appears to be stored in one or more of the Lv binary object fields for that table in MSysObjects. I’ve not tested to find out precisely when that happens

So coming back to my question back in post #9, we both appear to be right, at least in part.
Possibly because the page size was increased many years ago, corruption in long text fields is much more unlikely these days.

 

[Pat Hartman]

Text fields in Jet/ACE are al variable length. There is 1 byte to define the length (hint max for 1 byte = 255) + the actual number of characters required. So, in the ABC example, the actual space used is 1 + 3 = 4 characters.

Colin, one of the documents I read regarding the long text field mentioned 12 bytes for length (not sure why that is so long) + up to 32 bytes of the text if the text is less than 33 bytes. Otherwise the long text s stored in a separate data page.

 

[The_Doc_Man]

I want to address three questions at once.

First, @CJ_London - "Which begs the question. if the value is changed to 'ABCDE', does access move the entire record to another location? Implication is it does." Of course it does, because it can't erase the previous value yet. If you do a CurrentDB.Execute of an UPDATE query with the dbFailOnError option, you get an automatic rollback if it fails. Up to the point that the update finally either succeeds or fails, the old and new records have to co-exist to support the potential for a successful commit or a rollback after failure. When the UPDATE is complete, the old records are de-threaded and the new records are threaded into the table. If the UPDATE fails, it is the new records that just don't get threaded into the table. And that is why UPDATE queries cause so much bloat. The (new OR old) de-threaded records are now marked for reclamation but the old ones can't be immediately reclaimed because all users who were using the tables and did NOT have the dbSeeChanges option for their recordsets will still see the non-updated records (briefly.) Complicated? You had better believe it is complex.

@isladogs discusses changes in page size. That was related to two factors. First, the common disk "cluster factor" was changed long ago to 8 blocks = 4096 bytes because of larger disks needing bigger allocation units for FAT32 structures. That was necessary because bigger disks would reach a point where the allocation unit was bigger than 32 bits - which doesn't work for FAT32. The advent of NTFS changed that game because (IIRC) the so-called "extent pointers" (a.k.a. retrieval pointers) that point to file fragments are now themselves variable length and so size isn't as much of an issue. The second factor is that a long time ago, physical memory management was limited in how many bits could feed the memory address registers and it was a big deal to reach page sizes of 4 KB, which allowed you to have 12-bit offset pointers (within page). One can probably successfully argue that the change in physical memory addressing range was what enabled the use of larger disk allocation units.

@Pat Hartman discusses a long-text field with 12 bytes for minimum length. The reason it is that long is because that is the size of a formal scalar variable data description with a complex structure. The basic item is 4 x byte, 2 x long (=12 bytes) where the first 4 bytes have a data type, some status and other data, and then a LONG address and LONG size count (for the size of a LONG TEXT item). Don't hold me to the order of appearance of the address and size parts. The breakpoints on Text string sizes is simple, in that you have 1-byte string length indicator (Short Text=0-255 bytes), 2-byte string length indicator (Long Text=0-65535 bytes), or 4-byte string length indicator (Long Text=0-4 GB, except they restrict that case to 30 bits.) Which length indicator tells you which string size was used in the formal data descriptor. The formal descriptor can be shorter for SHORT TEXT strings since they cannot exceed 255 bytes. I cannot find my notes so I'm working from memory on that, but basically every variable passed into a function or subroutine has one of these item descriptors.

 

[CJ_London]

"Which begs the question. if the value is changed to 'ABCDE', does access move the entire record to another location? Implication is it does." Of course it does, because it can't erase the previous value yet.

good point

with regards short text first byte to indicate size of field - I always though it was 2 bytes since Access uses unicode which requires 2 bytes per char so a 3 char value would require 8 bytes of storage and a 255 char value would require 512 bytes - which is what Pat is saying when she say 4 chars. My bad for not being clearer with my answer