Data Type problems

[KevW]

I have a table that i need to be able to store text that may be over 255 characters long. I have changed the fields datatype setting to memo as I thought this was able to show Up to 65,535 characters. But the data is still being trimmed to 255 characters. I have looked in Access help at data types and it states that the memo field length is 65,535 characters

"if manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database."

My question is?

• How do I know if it is manipulated by DAO
  
• If it is not manipulated by DAO how can I change it (if possible) as I really need to full data to be retained

I am using Access 2000

Thanks for any advise

 

[llkhoutx]

My question is?

* How do I know if it is manipulated by DAO
* If it is not manipulated by DAO how can I change it (if possible) as I really need to full data to be retained

1. DAO isn't created by Access, the user creates DAOP code using the Microsoft DAO 3.0 Object Library.
2. You don't say how that field is constructed or where it comes from. You are probably truncating the data when it's stored in the memo field. Look at that expression closely it should be a memo field or a concantenating expression on the right side of the expression.

 

[KevW]

The table is populated from an Excel 2000 spreadsheet that is imported into the database using VBA code. So all the field data is from this spreadsheet. Not so sure as to what you mean about checking the expression but any pointers further ideas would be helpful.

I checked Tools > References but there was know entry for the Microsoft DAO 3.0 Object Library

 

[Rod D]

I think your problem lies elsewhere

Hi Kev

Is the data Linked to the Excel spreadsheet? - If so, only 255 chars will be "transferred" across.

If your tables are populated via an export/import... if the data in the first 25 rows have less than 255 chars, then the field will be treated as text and will be limited to 255 chars by default.

I have have seen problems in the past when Excel cells are formatted to "Wrap Text" to interfere with wizard as well, so it may be worth changing the format - the reason being that the Cr/Lf in the cell is seen by the wizard as the end of the input string. I don't think this affects you but is worth noting.

I think the problem lies in the import/export, not DAO (but of course - I could be very wrong... )

Regards
Rod

 

[KevW]

Hi Rod

Thanks for your input, I have checked the file and some of the first 25 have over 255 characters, (there is one row that actually has 313 characters). The table is populated using VBA code to import the table. I have also checked the alignment of the text in the spreadsheet and that is set to general.

So maybe the problems is in the importing of the data.

Do you havwe any solutions to this probelm or possible fixs

Thanks

 

[The_Doc_Man]

It used to be that Excel NEVER allowed a single cell to contain more than 255 characters. This has implications for how Excel stores things. But it also has implications for any Import/Export wizards, which "know" that Excel cannot store longer strings. Even if they are now wrong...

Because Excel didn't used to allow long strings, I would never depend on any Excel import/export to provide more than 255 characters per cell.