Jet engine max record length?

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
I am trying to hit an Access table with a database report writer. I create the table from 91 rows, each 126 characters long. I have them all concatenated in one record. I know the single table length is 4000, but I have broken this up into multiple tables, and then joined them in a query. When I try to access that composite record it says the record is too long for the Jet engine. Is there a method around this?

thanks
Fred Emmerich
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
Have never seen that error, nor do I know what you mean by "table length". It certainly is not the limit for table records. Your issue is probably that the query field is short text, which has a maximum of 255 characters. If you must put over 1100 characters in to one field, it will have to be memo/long text. Or are you saying the query works? If so, then what does "When I try to access that composite record..." mean?
 

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
Basically my input is text, 126 wide by 91 rows. I put it all into 1 record, but each row (before they go in a record) has about 15 items which become the fields. So I have not exceeded the number of fields. The error says the record is too long. At first it was all one table, then I broke it into 6 tables, each "record" in each table 1 part of the whole record joined on the same field which is a unique identifier. In both cases I got the same error. I am trying to hit it with a report writer that uses ODBC, which means the Jet engine. Another choice is Crystal Reports, I do not know if they use ODBC or hit the Access table directly. My query runs fine in Access, no ODBC there.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
No idea then, sorry. Except to say research the error number you got.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
It all sounds confusing and not at all sure where the idea of a limit of 4000 ever comes into play, or why multiple tables would be a possible solution. Seems like more information is needed because I'm also without ideas. I guess your "database report writer" is not MSAccess reports but might help to be more specific what you are using and how you are connected to Access. As a rule, if you have long text in a field it would be better to keep it in one field and not try to separate it into records and tables (!) - but of course that means you need to be able to bring it over to your report writer.

Offhand, transferring long text across different databases through ODBC does sound slightly complicated. Databases mostly have different ways of dealing with long text (varchar max, memo, text, blobs). Not sure how ODBC would work as a cross-platform compliant standard but assume its possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top