For years I take a customers excel file and import it into access and then it is used with older classic asp coding. Everything has worked fine, but now for whatever reason when I import the excel file into MS Access there is one field that should be "MEMO" and it imports it as a MEMO field, but still cuts the text off at 255 characters. I tried saving the excel file to a text file and doing the same thing, but it makes no difference. I have probably spent close to 3 hours now on this. I searched google, but it seems that other people have this problem too, but not sure what the solution is. Anybody?
Msg#: 4474087 posted 9:00 am on Oct 11, 2012 (gmt 0)
Access/Excel can be a nightmare for cutting text off at 255 characters.
One thing to try is not to import the file but to link to the Excel file. Then, write a query to copy/append the fields from the linked file into the existing table. Do not do any modifying of the fields in this query or Access will cut the text to 255 chars again - just do a straight append query.
I remember having to create a query to delete the data in the table (do not delete the table or you will lose the 'memo' field status), another query to copy the data from the linked file into the table, and a macro to run them both. You also need the new 'linked' table to the Excel file!
Crazy, but that was the only way I got it to work. That was until Access gave me enough serious problems that I had to stop using it.
Msg#: 4474087 posted 2:13 pm on Oct 12, 2012 (gmt 0)
I just use the text file route now. I can't stop using it for what I do use it for, no need to upgrade to MS SQL for 1mb of data and the process will be just as long, if not longer from excel. It just aggravates me that it took hours to figure this "short cut" / shortfall of Access.