Link to Excel Spreadsheet - Incorrect Data Type

[Guest]

With Access 2002, after I successfully link to an Excel Spreadsheet, and I
look at the Table design of my linked Excel spreadsheet, some of my columns
list incorrect Data Type. For instance, the 1st column of linked table shows
"Number" as my Data Type, however, the data in my Excel spreadsheet has a
mixture of numbers and numbers with text within it, therefore, I would expect
this to be a "Text" Data Type. This brings up a problem when I view the data
in my linked table, because I get a bunch of "#Num!" in the fields where my
values are not numbers. For instance, one row of my data is "7584018", while
another is "75B4018", where the 3rd character is the letter "B".

Why am I seeing this type of behavior, and what do I need to do to get this
to be a "Text" Data Type? I even went into my spreadsheet and set the entire
column to be "Text", thinking this would fix my problem when I linked to it,
but it did not.

 

[Guest]

Usually, the reason you see this is that Access does not look at all the data
in a column, it looks at the first data and "guesses". So, if the data in
the first row is all numbers than it assumes it is numeric.

What I do is create a temp "Import" table that has the same field names as
the column names of the spreadsheet. If you column name has spaces (or other
non allowed characters) your Access field name will need to be in brackets --
ie [First Name]. Set the field properties of the Import table to the data
type you desire.

Then import your Excel data into the temp Import table first. you can then
manipulate it as necessary with Access and then add (append) it to a main
table.

Finally, you can automate this process with some VBA code. Use
DoCmd.TransferSpreadsheet or DoCmd.TransferText.

Does this help?

 

[Jamie Collins]

Usually, the reason you see this is that Access does not look at all the data
in a column, it looks at the first data and "guesses".

Usually, you see replies to this question saying, 'MS Access looks at
this and that to determine the data type.' In fact, MS Access has no
control over the process. MS Access is simply presented with a column
of data whose type has already been determined and nulls in place of
any data that didn't fit the chosen data type. There is nothing you
can do on the MS Access side to influence the Excel data type, it is a
done deal.

You must look deeper into the issues i.e. the values and format of the
Excel column plus the Excel registry settings for the relevant version
of MS Jet. For details, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--