Linking Excel and Access

[jhudachek]

I am linking an excel sheet as an access table but acces
is reading a field that should be text as a number and
givng me garbage.

The problem may be related to the fact that the sheet
that is linked to access is built of many links to
various other sheets in the workbook so the actual cell
entry is something like:

+sheet1!h24, but shows as "Smith, John" for example.

this pulls the data from other sheets so that I can link
to just what i need and get it in a formay i need for
access.

Some columns pull the text correctly but the first 2 are
showing as #num! in access, I have checked the formatting
in excel, of course Access does not let you change linked
tables formats.

any help is appreciated.

 

[John Vinson]

I am linking an excel sheet as an access table but acces
is reading a field that should be text as a number and
givng me garbage.

This is a very common problem. Excel does not have strong datatypes:
Access must guess, based on the first few rows in the spreadsheet,
what data type is needed. If you have numeric data in the first few
rows Access will guess that all the rows have numbers - often wrongly.

What I'll usually do in this case is create a table in Access with the
appropriate datatypes, and run an Append query from the linked
spreadsheet. Even this doesn't always work; you may need to create a
dummy first row in your spreadsheet with "This Is A Text Field" in the
first cell of the mixed number/text field.