472,980 Members | 1,604 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,980 software developers and data experts.

255 Character Limit for Linked Excel data


I have gone throught the group and Microsoft's online help and have
seen many suggestions but I am still seeking clarity:

1. I have an excel spreadsheet. Column A contains text that may be
greater than 255 characters.
2. I have an access database. I link (not import) to the contents of
the excel spreadsheet. In the design view in access, Column A has the
data type "memo".
3. The data in column A is truncated at 255 characters in the Access
linked table and in any reports.

On Microsoft's site, I have read:

-- http://office.microsoft.com/en-us/ac...33.aspx#TrSh--
A common import problem - truncated data

If you find that Excel cells containing more than 255 characters are
being truncated during import, see "Data Type" under Step 4: Things you
should know before importing certain data types and elements, in the
"Getting started with the import operation" section of this topic. This
will explain that the Import Spreadsheet Wizard previews only the first
25 rows of Excel data in predicting the data type to assign to the
field in the new table.

If you have a cell that contains 440 characters in the 36th row of
data, for example, Access will not see the data in that row, and,
instead of creating a field with the Memo data type, the wizard will
create a field with the Text data type. How could you avoid this
particular problem? By cutting and inserting the 36th row of data above
the 25th row in the Excel worksheet. It's also important to ensure that
the data in the columns of your worksheet have a consistent data type
from row to row.
So I made sure I did this -- but I still have the truncation issue.
This article gives me the hope that there is a solution although it may
be limited to 'imported' data, not linked data. So, I looked further
on Microsoft's site:

-- http://office.microsoft.com/en-us/ac...885461033.aspx --
Some field values appear truncated in a linked table

During linking, if Access encounters values in Excel that are longer
than 255 characters, they are stored in a memo field that displays only
the first 255 characters. The workaround for this limitation is to
import the worksheet or named range, instead of linking to it.

This article, however appears to dash my hopes of a solution. It
clearly says that regardless of it being a memo field or not -- Access
still will only display the first 255 characters.

So here is my question:
1. Is there a way to link to an excel spreadsheet and have the Access
database display more than 255 characters in a cell

OK, wait I might have just figured something out.
- When I look at the table view in Access for the linked data in Column
A, it still only shows 255 Characters
- I opened my report in design view and got the properties for the Text
Box used to display the Column A data. I changed the 'Format' from "@"
to blank. Then saved the report.
- When I open the report in Preview all 255+ characters appear.

To double check here is what I did ...
- I closed everything down. I went into my Excel spreadsheet and added
more text to one of the cells (even more than 255 characters).
- I opened up Access. Data in Table is still truncated but the report
preview shows all of the data, including the new text I added.

So, I tried one more thing.
- I opened Access. Data in table is truncated, so I added the word
"truncated" to the 255 character string.
- I checked the report, the 255 characters, plus the word truncated
appear in the report. I close Access
- I opened the linked spreadsheet in Excel -- the cell contains only
the 255 characters and the word truncated.
- I reopen access. The data doesn't display the word truncated, but
the report does.

So, I think I answered my own question. Yes there is a way to display
more than 255 characters from a linked Excel spreadsheet, but not in
the data table -- but in a report and only if the properties for the
field have the "@" removed from 'format'. So long as I don't monkey
with the data in the Access Table which will overwrite the Excel
spreadsheet, I should have no problems ...

Does this seem logical at all??


Nov 23 '06 #1
0 14314

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: see_mun_lee | last post by:
I use asp to develop a web page to read an excel file containing Chinese Character then display it in the web page. Unfortunately, I cant display it!!! it will display (?????????). <META...
by: TBlair | last post by:
I am about to start working on a project where I would be required to dynamically create linked servers during the execution of an application. One of the requirements is for the Link Servers to be...
by: Chris Powell | last post by:
I am using Excel/Access 2000 and have two large Excel files (25,000 rows each) that I wish to create linked tables in Access rather than importing into Access. The two source Excel files change...
by: me here | last post by:
I have a VBA subroutine that links an MS Excel spreadsheet and copies the data into a local table. This process is controlled by a form that allows users to select the spreadsheet from the file...
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
by: Janelle.Dunlap | last post by:
My database is linked to external data from a single Excel spreadsheet. I currently have it so that the entire spreadsheet exports into one table, but really for the purpose of my database it will...
by: christianlott1 | last post by:
I've linked an excel worksheet as an access table. The values appear but it won't allow me to change any of the values. To test I've provided a fresh blank workbook and same problem. I've done...
by: ccookson | last post by:
I have a form that prompts the user to select an excel file to be linked into the database. However, Access automatically assigns the data types from text to numbers. How can I change the data...
by: franc sutherland | last post by:
Hi everyone, I am using Access 2003. I have a database with a table in it which is linked to an excel spreadsheet. When I install the database on someone else's system, the pathname to the...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.