Linking Excel table to Access

[Guest]

When linking an excel table to Access, some of the data is being displayed as
: "#Num!". The problem however does not affect all data of the exact same
nature, but only certain some fields.
I will appreciate some assistance in resolving the mentioned problem.

Kind regards
Maurice

 

[Ron2006]

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]


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.

Ron

 

[Guest]

Thank you for really making an effort to provide me with both an explanation,
as well as a proposed solution. It is much appreciated, and I shall certainly
give it a try.

Maurice