Data Type Issue in Linked Excel Table

[MacNut2004]

Hello,

I have a table in acess that links to an excel spreadsheet. I have a field
"Comments" that is a memo field, and all the text in that field shows up when
i open up the linked spreadsheet in excel. Yet when i view that same linked
data in access, the field is cut off even though it's specified as a memo.

Looking at the data format of the "Comments" field in the linked table in
access, i see that the data type for that field is @. I take this out, and i
then see all the text in that field! The problem is, that it will NOT save
that way...the @ symbol is in there again when i reopen the table in design.

How can i get all the text in the field to show up in the linked table in
access??

Thank you!
MN

 

[Beetle]

See the following kb article;

http://support.microsoft.com/kb/839785

 

[MacNut2004]

Ahh, thank you for that article. That makes sense.

However, this creates a lot more work for me, if I have to import the tables
to capture all data. reason is because every month, i have about 15 of these
tables (one per person) when they submit the data to me in excel....which is
why i link them in access instead of import.

Then in my access tool, i have a union query to bring together all those
tables.

Is there another way rather than importing?

 

[Beetle]

Is there another way rather than importing?

Not that i know of unfortunately, but maybe someone else
will jump in with a solution.

 

[Ken Snell]

Importing is the answer. See my website for various ways to automate the
process, which would make it easier.

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

--

Ken Snell
http://www.accessmvp.com/KDSnell/

 

[MacNut2004]

Hi Ken!

Thanks very much for that link, that will help a lot. I do have a question
though. I put that code in Access, changing it appropriately, but when i try
to RUN it in my access tool, nothing happens.

What could I be doing wrong? I am using the "Import Data from Specific
Worksheets in All EXCEL Files in a single Folder via TransferSpreadsheet
(VBA)" method to put one sheet from each file in excel into a single Access
table. I put the code into a function and module in Access, and then run the
function...nothing happens.

Thanks,
MN

 

[MacNut2004]

Nevermind! I didnt' have the "\" at the end of my path. I put that in there
and now it works great!!

Thanks so much for your help!
MN

 

[Ken Snell]

You're welcome.