Linking Excel Table to Access Database

[Guest]

When linking an excel table to Access, certain data are displayed as "#Num!".
How do i overcome the mentioned problem, as it does not affect all data of
the exact same nature. Certain values are displayed correctly, whilst others
are not.
I will appreciate some advise towards resolving this particular problem.

Kind regards.
Maurice

 

[John Vinson]

When linking an excel table to Access, certain data are displayed as "#Num!".
How do i overcome the mentioned problem, as it does not affect all data of
the exact same nature. Certain values are displayed correctly, whilst others
are not.
I will appreciate some advise towards resolving this particular problem.

Kind regards.
Maurice

Since Access has strong datatyping (every field must have a defined
datatype) while Excel does not (each cell is independent, a column may
contain any combination of datatypes), Access must *guess* the
appropriate datatypes when importing or linking to a spreadsheet. It
does so by looking at the first few (I don't know the numeric value of
"few" here <g>) rows. If they are all numeric, Access treats that
column as if it were of Number type.

If further down the sheet you have non-numeric data you'll get this
error. A common example is Zip or PostCodes - if you have five-digit
numbers in the first several rows, and further down something like
83660-6354 or N3Y 8B1, you'll get an error.

One getaround is to put a dummy row at the top of the spreadsheet with
an unambiguous text value ('X for example) in each such column.

John W. Vinson[MVP]

 

[aaron.kempf]

make your beancounters export into CSV and use BULK INSERT.

this of course; assumes that you know SQL Server.
if you don't then get out of the db industry.

-Aaron

 

[Ron2006]

I have had better results if I imported the excel spreadsheet into
access instead of linking it.

There can still be problems but fewer. If problems still come up then
the I have used the approach that John has suggested. Once imported
then just run a quick query to delete that particular row.