Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linking to .xls / memo field truncating text data

Status
Not open for further replies.

TheTuna

Programmer
Dec 13, 2002
544
US
I'm linking to a spreadsheet (one sheet within) and one of my fields is greater than 255 characters, so it's going into a memo field. For some reason, the memo field is chopping off the text. Does Access STILL not have this problem resolved? Does anyone know of a way around the 255 character limit?

Thanks!

[fish] No Dolphins were harmed in the posting of this message... Dolphin Friendly Tuna!

Ever feel like you're banging your head against a tree? I did, so I cut down the tree.
 
Anyone?

[fish] No Dolphins were harmed in the posting of this message... Dolphin Friendly Tuna!

Ever feel like you're banging your head against a tree? I did, so I cut down the tree.
 
Sorry Tuna

Access 2K, although can store up to about 64 kb in a memo field, only the first 255 characters is referenced as a "regular" field. Access stores the memo outside of the "table".

The only work-around is to use hyperlinks (which is also not stored "in" the table) and bookmarks.

Richard
 
Thanks

[fish] No Dolphins were harmed in the posting of this message... Dolphin Friendly Tuna!

Ever feel like you're banging your head against a tree? I did, so I cut down the tree.
 
I had a similar problem importing an Excel spreadsheet into Access 2000. The spreadsheet had many cells containing over 250 characters and Access refused to recognize the data from Excel as anything but text (250 limitation).

My solution:

1. Import the Excel file into a new Access table.
2. Change the field types in the new Access table to "Memo" as appropriate.
3. Delete all the records from the new Access table.
4. Re-import the Excel data into the existing empty Access table which I modified the structure on to include "memo" data types.

It seemed to work. All my Excel data came across. Just a little cleanup to do changing verticl bars to hard returns.

-Bob
 
Thanks, but this was solved a while back... have a great day!

[fish] No Dolphins were harmed in the posting of this message... Dolphin Friendly Tuna!

Ever feel like you're banging your head against a tree? I did, so I cut down the tree.
 
this was solved a while back
Thanks for sharing Tuna. Can you please explain the members how you solved your issue ?
 
Sure! Instead of linking to the spreadsheet, I import the data... end of problem.

The procedures very nearly match what rogern suggests in his post.

The problem lies in how linking via access works (or doesn't work, depending on your way of thinking) as willir pointed out...


[fish] No Dolphins were harmed in the posting of this message... Dolphin Friendly Tuna!

Ever feel like you're banging your head against a tree? I did, so I cut down the tree.
 
Thats interesting,

I had a similar problem, trying to import MySQL data (using ODBC) where one field had 'comments' > 255 bytes into SAS or Excel or Access, and the field that had > 255 bytes was truncated to 255 characters.

I solved the problem by exporting the mysql data as a well formed xml document then importing into Excel using vba.

cheers,
dan
 
I hadn't thought about this in a while, but there was a little more to it... the file being imported is actually now a CSV file, not a spreadsheet. The users were originally saving the file as a spreadsheet, but I had them save it as a csv file. Totally forgot about that. My bad!

[fish] No Dolphins were harmed in the posting of this message... Dolphin Friendly Tuna!

Ever feel like you're banging your head against a tree? I did, so I cut down the tree.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top