Record Too Large for every update query I run after MS updates

[gilhammer24]

Let start off by saying I am a novice at best. Completely self taught through Google. Running Windows 7 64bit and Access 2010. That being said here is my problem. I have several databases that I append new data to monthly as it becomes available. I have a series of queries (50+) programed to update, calculate, and sort the data to various tables as needed. It seems that since I have installed a barrage of updates during the last month (the only thing I can think of thats changed), nearly every update query I run returns the error "Record Too Large". This occurs on queries that may only be updating a maximum of 4 characters on the low end and up to 200 characters on the high end. Not large amounts of data at all. I have had success with one database by simply running one query, closing out the database, reopenning the database and running the next. Very time consuming and frustrating. Suggestions?????

[Dal Jeanis]

Wow. Never heard of this one before, but apparently Access has a limit of 2000 characters total for any one record, not counting memo fields.

google "Access record too large"
http://support.microsoft.com/kb/111304
http://answers.microsoft.com/en-us/o...d-7663462d4782

So, first, you need to analyze your tables and find out which ones are designed such that the records are likely to hold long amounts of text in text-type fields. If you have this situation, you'll either have to move some of them to "tall thin records" - for example by creating a "textnotes" table and storing the key to the textnote on the regular table, and the note itself on the textnotes table - or change some of them to memo type fields, which are not subject to the limitation. (Presumably because they are already handled somewhat like the textnotes idea above).

Second, what kind of updates, yours or Microsoft's?

[gilhammer24]

Wow. Never heard of this one before, but apparently Access has a limit of 2000 characters total for any one record, not counting memo fields.

google "Access record too large"
http://support.microsoft.com/kb/111304
http://answers.microsoft.com/en-us/o...d-7663462d4782

So, first, you need to analyze your tables and find out which ones are designed such that the records are likely to hold long amounts of text in text-type fields. If you have this situation, you'll either have to move some of them to "tall thin records" - for example by creating a "textnotes" table and storing the key to the textnote on the regular table, and the note itself on the textnotes table - or change some of them to memo type fields, which are not subject to the limitation. (Presumably because they are already handled somewhat like the textnotes idea above).

Second, what kind of updates, yours or Microsoft's?

Microsoft Updates. I just ran the repair function for office with no luck. What has me so frustrated is the data that I am updating is very short, no more than 200 characters, so I have no clue why I keep getting this error over and over, and it is happening in all of my databases. I will try converting some of the text fields to memo to see if that works. As is, the databases have worked of over a year with the same format and data types with no issues. These are critical to my job as they were cutting my file scrubbing and sorting time down from days to just hours.

[Dal Jeanis]

Is the entire record no more than 200 characters, or jus the longest text field? It is the SUM of all the field lengths that makes the total record too big.

[gilhammer24]

Ahhh....It has been growing every month for the last year. I will convert to memo where applicable and that should get me through this month. Sounds like I will need to separate the data out to multiple tables and make a few adjustments to my form queries as needed. Making a separate table for each year should keep everything in working order while still making it easy enough to query.

Thanks.....sometimes it just takes a slap across the face. I think I was so complacent with the easy of operation I had create that I did not want to do any maintenance. Looking for the easiest way out. Sounds like it is probably just poor planning on my part and coincidence that this happened after the updates. Still don't understand why sometimes it will work and others it won't????

[ssanfu]

...Making a separate table for each year....

This makes me think you should also look at the structure. A table for each year is not a normalized structure.
Care to post a pic of your relationship window?

I'm just saying.....

[Dal Jeanis]

Yeah, that "separate table" comment, and the fact that your *records* are getting bigger across time, are two red flags that you're making a short wide table instead of a tall thin one. You'd be better off reviewing the design and getting it properly normalized. It will make your reporting much easier in the long run.

[gilhammer24]

My primary table, which is the raw data I receive, is 17 fields total and nearly ~25k rows at current. Basically I am bringing two secondary data sets that are 8 & 12 fields and using queries, all relationships are built into the queries as needed, to update data as needed. Then individual data groups are pulled out into tables. These tables are the base for each form built with a series of queries ran from its base table that will allow for various calculations to be ran from the data. I have a "Home" form that gives the user command buttons to select which form, i.e. data group, they want to analyze. The user has various combo boxes to select various ways to sort the data, typically in YOY comparisons with percent change and related charts for visualizing the data, e.g. Select Customer Group - Select Year One - Select Year Two.

As I was working through my issues yesterday I also found that when trying to delete records that were no longer needed I also received the error "Record Too Large". At one point I built a query to null out an entire field and received "Record Too Large". I then tried to delete that field in design view and received "Record Too Large". In the end I simply exported my table, deleted the existing one, then imported it back in and everything worked like it should. I did the same on my other database and it ran smooth as silk too??????? Data corruption???

Thanks for all you help. As I said I'm very much a novice and am utilizing Access because I was tasked with producing large data analysis in short periods of time basically keeping me up all night for two days at the first of every month. Access allows me to complete it all in about 4 hrs.

[Dal Jeanis]

Yes, it sounds like data corruption of some sort. Access decided some particular record(s) or field(s) was much bigger than it really was, and confused itself. Sounds like you worked it out. If so, please mark thread as "solved". Top of page, under thread tools.