G
Grip
Hi,
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/access/HP010950951033.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/access/HP051885461033.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??
Cheers,
Colin
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/access/HP010950951033.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/access/HP051885461033.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??
Cheers,
Colin