We help IT Professionals succeed at work.

Cannot insert record in access table, record is too large.

Hi Experts,

I'm trying to insert a record from Excel into an Access table and getting an error "Record is too large", see attached.

This table has many fields, any idea how can I figure out which column is causing it?

Thanks
Untitled3.png
Comment
Watch Question

President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

 All the fields in an Access table except for OLE and Memo fields must fit on a single page of 4096 bytes.   If they cannot, you get "record too large".


 So one way around this is to change text fields to memo or long text fields.


 The other is too properly normalize the data on the way in and dice it up into separate tables.


Jim.

Hi Jim,

So one way around this is to change text fields to memo or long text fields.
For that route, how do I figure out which column is best to convert (for this record and others as well in future...), like an estimate of average per field...?

Thanks,
Ben
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012


 Any Text/Short Text field (not sure which version you are working with) is fair game, but it should be ones that are typically filled.  When you use one of these, you are creating a variable length field that is stored with the main part of the record and all the other fields.  The field can be a max of 255 characters.


 When you use memo/Long text field, then you force the field to be stored on LVP's (Long Value Pages), which are pages separate from the rest of the record.   The only thing that remains on the main page is a 16 byte pointer to the start of the LVP chain.  A long text field can be 65k characters in length.

 

 So it still chews up some space, but it's so little that typically you'd run into the 255 column limit of a table before you would end up with "record too large".


and BTW, SQL server does not have this issue.   It's just JET/ACE that does.


Again, the other way to get at this is to refactor the tables.


For example, current table has something like:


tblVisits

VisitID

PatientID

VisitDate

IntakeNote

ExamNote

DischargeNote


Where instead, you could go to:


tblVisits

VisitID

PatientID

VisitDate


tblVistNotes

VisitNoteID

VisitID

NoteType

Note


  Now instead of three fields in one table, you would have three records in another table.  You are not going to get a "record too large" with this, where you might with just a tblVisits.  That's what I meant about properly normalizing before. 


  This is might not be quite fully normalized (you might have one table per note type), but it gives you the idea.


When you see things like:


IntakeNote

ExamNote

DischargeNote


  it can be an indication of what is really a repeating field  ("a note").    Another example of that is phone numbers:


Phone1

Phone2

Phone3


or 


HomePhone

WorkPhone

MobilePhone


    all are "a phone #" and really belong in their own table (or tables).   Many will put all in a single table though and that leads to "Record too large"


Jim.

Hi Jim,

Let me explain the story over here a bit...

The table was designed in Caspio in a none normalized state.

I was called in and given the task to create reports.

Since Caspio does not have the capability to select and print various records at once, I decided to import all data into Access and print it from there.

At the moment its not fashionable to normalized it as it would affect the entire app.

Now Caspio has it in either text(255) fields or Memo fields, its using SQL as back-end.

I had already tried exporting into diff formats like Excel or CVS, but ran into different issues...

I understood that the solution would be converting it to Memo field.

My question now is, if its there a way to determine which field should be converted, or I have just to guess and pick one...?

Thanks,
Ben
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Ben,


<< Since Caspio does not have the capability to select and print various records at once, I decided to import all data into Access and print it from there.>>


OK.


<< At the moment its not fashionable to normalized it as it would affect the entire app.>>


 Unless I'm not understanding, normalize it on the way into Access, not in Caspio.


<< My question now is, if its there a way to determine which field should be converted, or I have just to guess and pick one...?>>


 Which ever one(s) have the most data in them.


 Again, a short text field in Access is stored variable length, so the fields with the most data in them will chew up the most space.


Jim.



Top Expert 2014
Using a copy of your workbook, add columns to the worksheet, one for each text field.  
2. Add a formula for these new columns:
=Len(T2), where T2 is the address of text cell for that row.  

3. Fill down the formulas.

4. Sort the data by each of the new columns, one at a time.

5. Look for large numbers.  Those are likely the source of your insert problems.

I think there is still a 32K limit on the text for Excel cells.  Of course, that depends on your version of Excel.
Hi,

normalize it on the way into Access, not in Caspio.
Actually you right, that may also work, however this would get me confused if structure in Access would be different from the Caspio DB.

Which ever one(s) have the most data in them.

I'm trying to use the following in Access to determine size lenght of each field, but getting an error "invalid operation" on len(Fld.Value) line #13 below.

Public Sub LoopThroughFieldsInATable(strTableNa As String, dtCutOffDt As Date)
Dim db As DAO.Database, tdf As DAO.QueryDefs, fld As Field
Set db = CurrentDb()
'Set tdf = db.QueryDefs(strTableNa)
Debug.Print strTableNa & " has " & DCount("*", strTableNa) & " rows."

For Each fld In db.QueryDefs(strTableNa).Fields    'loop through all the fields of the tables
   If fld.Type = 10 Then
      'text
      'If DCount("*", strTableNa, "   -   " & fld.Name & " > 0 and Date_Signed > #" & dtCutOffDt & "#") = 0 Then
          'Debug.Print fld.Type & " " & fld.Name & " has zero-length strings"
      'End If
          Debug.Print Len(fld.Value) & " - " & fld.Name
      GoTo NextField
   End If
      
   If fld.Type = 20 Or fld.Type = 4 Or fld.Type = 1 Then
      'number
      If DCount("ID", strTableNa, "[" & fld.Name & "] is not null and DateEntered > #" & dtCutOffDt & "#") = 0 Then
          Debug.Print fld.Type & " " & fld.Name & " is null"
      End If
      GoTo NextField
   End If
   
   If fld.Type = 8 Then
      'Date
      If DCount("ID", strTableNa, fld.Name & " is not null and DateEntered > #" & dtCutOffDt & "#") = 0 Then
          Debug.Print fld.Type & " " & fld.Name
      End If
      GoTo NextField
   End If
   
   If fld.Type = 10 Then
    End If
   
      
   Debug.Print "UNK:  " & fld.Type & ": " & fld.Name
   
NextField:
Next fld

'cleanup:
Set tdf = Nothing
Set db = Nothing

End Sub

Open in new window


Any idea how to get that working.

Thanks,
Ben
@Akimark,

...one for each text field

There are many (embarrassed to say how many...), looking for a shortcut-:)

Thanks,
Ben
Top Expert 2014
Assuming the worksheet is an attached table, you can write some VBA code in Access to iterate the columns.  For each text column, invoke a query like this:

Select Top 10 Len(textcolumnname), "textcolumnname"
From attachedtablename
Order By Len(textcolumnname) Desc

Open in new window


And persist (print/save) the results.
For each text column, invoke a query like this...:
Similar idea is what I'm trying to accomplish with the code posted above (one below your first comment).

That would avoid having to work field by field.

Can you help me make that work?

Thanks,
Ben
Top Expert 2014
For Each fld In db.TableDefs(strTableNa).Fields    'loop through all the fields of the tables
   If fld.Type = 10 Then
      'text
      set rs = dbEngine(0)(0).OpenRecordset("Select Top 10 Len(" & fld.name & ") As fldLen, " & fld.name & " As fldname From " & strTableNa & _
         " Order By Len(" & fld.name & ") Desc")
      Do Until rs.EOF
         debug.print rs.fldLen, rs.fldname 
         rs.movenext
      Loop
   End If
Next

Open in new window

Okay this works,

How do I add field name to the output?

Thanks,
Ben
Top Expert 2014
It should be there to the right of the length. I named it fldname
Thank you!