Getting record too large error in MS Access
I'm getting a Record too large error when I try to append one of my tables to a new table. I'm using MS Access 2K. I suspect one of my users entered too much data into one of my fields.
I (1) need help finding the record causing the error... and (2) suggestions how to prevent it from happening again..
I know about the "2000" character limitation of Access records, but I can't find the offending record... it seems like someone must have written code???
I don't really understand Unicode Compression . it seems like it can affect this problem? Can I mitigate the problem by changing a Unicode setting??? I don't need support for any language other than English.
I know I can delete unused fields, change to memo type, and normalize, but I right now I just need help finding the offending record.
Thanks in advance.
I (1) need help finding the record causing the error... and (2) suggestions how to prevent it from happening again..
I know about the "2000" character limitation of Access records, but I can't find the offending record... it seems like someone must have written code???
I don't really understand Unicode Compression . it seems like it can affect this problem? Can I mitigate the problem by changing a Unicode setting??? I don't need support for any language other than English.
I know I can delete unused fields, change to memo type, and normalize, but I right now I just need help finding the offending record.
Thanks in advance.
ASKER
I was hoping for something not programmatic.. I have about 200 fields and 100000 records..
I know that's a lot of fields.. most art archival.. I'm afraid to delete them because I know I'll have other problems
I know that's a lot of fields.. most art archival.. I'm afraid to delete them because I know I'll have other problems
Then remove the last 100.
If fail, the offending field is in the current 100, add back to 100 removed field. then remove 50 of the 100 you use first - and so on with 25, 13, 6, etc. fields.
If success, the offending field is in the removed 100, then add back 50 of these, and so on with 25, 13, 6, etc.
If fail, the offending field is in the current 100, add back to 100 removed field. then remove 50 of the 100 you use first - and so on with 25, 13, 6, etc. fields.
If success, the offending field is in the removed 100, then add back 50 of these, and so on with 25, 13, 6, etc.
ASKER
Thanks Gustav..Like you I'm sure Ive used that approach many times.. mostly to find corrupt records.. but I'm hoping to find code that can be run after update in the offending form that prevents this problem.
This only happens once every few years.. I have on form which is prone to the problem... it uses eight fields.. changing compression won't help???
This only happens once every few years.. I have on form which is prone to the problem... it uses eight fields.. changing compression won't help???
Have you checked the field definitions? It is sloppy to allow them to default to 255 or even 50 when the max allowed will be 20. You can change them a couple at a time and resave the table. Access will complain when you get to the field that is too long.
There is something else at play in your append query if all the data exists in tableA and you can't add it to tableB. Please go into more detail about exactly how you are doing this. You must be taking data from multiple sources for this to be happening.
There is something else at play in your append query if all the data exists in tableA and you can't add it to tableB. Please go into more detail about exactly how you are doing this. You must be taking data from multiple sources for this to be happening.
Well, if you have corrupt records or database, fix that.
I'm not sure what compression, you have in mind. Just use the appropriate data types and (for short text) field sizes.
I'm not sure what compression, you have in mind. Just use the appropriate data types and (for short text) field sizes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Like many of you who develop applications which other people use, we have little control over what data clients enter into forms.
As a result, clients can enter too much stuff in an Access record which can lead to problems such as what I experienced.
"Record Too Large" can bring an operation to a halt when it occurs...especially in a multiuser environment.
I found a solution which I will share here in case other encounter this type of problem.. I really don't know where else to post this code but would like to help anyone in a similar situation.
Since its not easy or even possible in many cases to tell how many characters are contained in a specific record, when I encountered this problem I wrote code to find the record with the most characters. Its not professional code as you will see posted by many of the expert here... but it did work and solve my problem... it cycles through the table...capturing the length of all the text files in the fields collection... in my case the record causing the problem was 1561 characters long.. I reduced the size by changing a description from 100 characters to 40 characters and solved my problem.
I also created a command button that runs this code form my staff. If they encounter the error while no-one is around to perform database surgery they can find the record and fix it themselves by pressing a button.
Pat and Gustav...thanks for the help!
Set oDB = CurrentDb
Set oRS = oDB.OpenRecordset("Select * from 0080")
Do While Not oRS.EOF
For Each fld In oRS.Fields
Debug.Print fld.Type & " " & fld.Name
If fld.Type = 10 Then
If Not IsNull(fld.Value) Then
strTemp = fld.Value
RecordSize = RecordSize + Len(strTemp)
End If
End If
If RecordSize > RecordMaxSize Then RecordMaxSize = RecordSize: strProblemPart = oRS!PartID
Next fld
Debug.Print oRS!PartID & " " & RecordSize
RecordSize = 0
oRS.MoveNext
Loop
MsgBox "Max Size was : " & RecordMaxSize & vbCrLf & strProblemPart
As a result, clients can enter too much stuff in an Access record which can lead to problems such as what I experienced.
"Record Too Large" can bring an operation to a halt when it occurs...especially in a multiuser environment.
I found a solution which I will share here in case other encounter this type of problem.. I really don't know where else to post this code but would like to help anyone in a similar situation.
Since its not easy or even possible in many cases to tell how many characters are contained in a specific record, when I encountered this problem I wrote code to find the record with the most characters. Its not professional code as you will see posted by many of the expert here... but it did work and solve my problem... it cycles through the table...capturing the length of all the text files in the fields collection... in my case the record causing the problem was 1561 characters long.. I reduced the size by changing a description from 100 characters to 40 characters and solved my problem.
I also created a command button that runs this code form my staff. If they encounter the error while no-one is around to perform database surgery they can find the record and fix it themselves by pressing a button.
Pat and Gustav...thanks for the help!
Set oDB = CurrentDb
Set oRS = oDB.OpenRecordset("Select * from 0080")
Do While Not oRS.EOF
For Each fld In oRS.Fields
Debug.Print fld.Type & " " & fld.Name
If fld.Type = 10 Then
If Not IsNull(fld.Value) Then
strTemp = fld.Value
RecordSize = RecordSize + Len(strTemp)
End If
End If
If RecordSize > RecordMaxSize Then RecordMaxSize = RecordSize: strProblemPart = oRS!PartID
Next fld
Debug.Print oRS!PartID & " " & RecordSize
RecordSize = 0
oRS.MoveNext
Loop
MsgBox "Max Size was : " & RecordMaxSize & vbCrLf & strProblemPart
Remove one field by the time until success.
The offending field will be the last removed.