Silent Fail - Microsoft Access Error 3163: The Field Is Too Small - What It Means and How to Fix It


	Back to Field Is Too Small Comments List

Silent Fail

Donald Blackwell

28 hours ago

As a side note, if it seems that Access if failing without an error when using a Long Text datatype - i.e. Access will truncate the field if doing an import, it may be that Access has actually reached the maximum amount of space even a Long Text field can hold OR, it isn't displaying all of the text in the field.

I believe the maximum # of characters allowed in a Long Text field in the database is around 2.7 billion characters (the # of characters is equal to the largest # a Long Integer can hold), however the largest amount of characters an Access Text Box can display is 65,535 characters. And for another fun fact, the largest amount of characters a cell in Excel can hold is 32,767.

Richard and I recently had a conversation via email about his "Psychic" abilities, lol. I literally just ran into this issue today while working on an import operation with a CSV file that was failing both using Access text import and Classic VB File IO reading so I thought I'd share in case anyone else had run into this type of truncation.

Richard Rost @Reply

27 hours ago

Yep, that's a good catch, Donald. A Long Text field can store far more than an Access text box can display or accept through the normal form UI. The text box limit is 65,535 characters, so VBA or an import process can be important when you're dealing with really large blocks of text.

The database limit itself is much higher, although I wouldn't rely on the Long Integer maximum as the documented Long Text limit. Either way, it's well beyond what a standard Access text box can handle.

And I relish my psychic abilities, man.

Let me guess: you came here from the future, and you're trying to sell me a subscription to the Youth Guard Coast Auxiliary.

	Donald Blackwell OP @Reply 27 hours ago
And yeah, in most use cases, I'll never get anywhere near that number. In the import operation VBA started truncating the incoming File IO line input #FF, S string at 215045 characters which is also well more than I wanted or needed, however the source repository created the CSV with LF instead of CRLF line breaks so Classic VB IO just kept going.

Richard Rost @Reply

25 hours ago

I did actually get past that number once. About 10 years ago, I built a database to store handbook text and archive books and other material. I loaded a whole bunch of stuff into it, then went back a week or two later and thought, "Why is half of this missing?"

I had to learn that one the hard way in the pre-AI days. I actually had to use the Googles. By the time I found the limit, all that work I'd done was basically for naught.

Next Unseen