Jet engine max record length?

[Mr930]

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

 

[Micron]

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]

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]

No idea then, sorry. Except to say research the error number you got.

 

[xenou]

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.