Access Record is too large error

Recently reverted to some old school .net winform coding, moving data into an access 2010 database using datatable and tableadapters. Funny thing, some of this still is still easier to use than entity framework or whatever flavor of data connectivity flavor of the week you might be using… but mostly, it’s not so great. Since this needed to use Access, I didn’t have much other choice.

So, one table I needed to move data into had a ton of fields… about 150 of them. I didn’t immediately recall the row data size limit, but it found me pretty quickly- apparently about 200 characters. So, without spliting the table into multiples or some other nonsense, how do you work around this?

The table is actually capable of storing 4000 bytes, but everything defaults to unicode.. and not the smaller utf-8 stuff, but the big boy utf-16. This is status quo nowdays, but is still quite a waste for many english-only applications.

I had the idea that maybe access could be tricked back into some kind of 8 bit character mode, but no such luck. However: I did find a flag you can set on text fields, called “unicode compression”. Turn this on, and apparently it uses some kind of internal compression (sounds like it emulates utf-8) to smoosh down characters that done require two bytes for storage. I turned this on for all the text fields in my db, and sure enough- now my datadata is able to store data with total width greater than 2000 chars. Maybe not the best possible solution, but works great when you just want to get the project out the door.

Info from MS: http://support.microsoft.com/kb/111304

Another item I ran across with this: if you go through and change all the columns in your table to use unicode compression, the table effectively deletes the old column and creates new ones. But internally, the column count continues to increase, and can get to be too many columns for a table definition (255 or 250 columns if I recall). The quick solution for this: after making all the changes to the table, go to the file menu and click “save as” and save it off with a different table name. This will re-gen the table with only the columns you’ve edited, and not alll the old deleted columns baggage. Change the names of the tables back, and you’re ready to roll.

Some more info on the column limit: http://support.microsoft.com/kb/128221