Linked Table

[Cathy]

I have an Excel spreadsheet that I would like to link to
ACCESS, but there is one field that the linked table
insists is a number format. I need the field to be
defined as a text format... but ACCESS won't let me change
it. I have tried to change the format to text in Excel,
but the link still forces it to a number format.

How can I correct this or force it to a text format?

Thanks!
Cathy

 

[Bob]

Sorry I'm no help. I have the same problem in reverse-
Access insists on importing some number fields as text.
I'm looking for a way to get around the linking wizard
and getting the link done right in the first place. Good
luck.

 

[John Vinson]

How can I correct this or force it to a text format?

About the only way I know is to put a dummy row at the top of the page
(or range) that you're linking, with "This Column Is Text Dummy!" or
some other undoubted text field in that column.

 

[John Vinson]

Sorry I'm no help. I have the same problem in reverse-
Access insists on importing some number fields as text.
I'm looking for a way to get around the linking wizard
and getting the link done right in the first place. Good
luck.
change

I've been frustrated every time I try to link to Excel, one way or the
other - date fields are especially bad. What I'll usually do is create
a table with the desired field types and import from Excel into the
table. Since Excel doesn't have "strong data types", Access must
guess, and often guesses wrong!

 

[Bob]

Thanks for your help. I tried mine several times and
finally got it to work. I'll try your method next time.

 

[Bob]

WEll, I did get the link to recognize the field data
types correctly (number)using the dummy method you
described . Now it doesn't recognize the real data itself
as numbers! What is the matter with Access? What garbage!
Why can't we override this manually! I want my money
back! I'm about to lose a contract because of this! What
Garbage! I should sue Microsoft. This is unbelievable.

 

[John Vinson]

WEll, I did get the link to recognize the field data
types correctly (number)using the dummy method you
described . Now it doesn't recognize the real data itself
as numbers! What is the matter with Access? What garbage!
Why can't we override this manually! I want my money
back! I'm about to lose a contract because of this! What
Garbage! I should sue Microsoft. This is unbelievable.

The problem is primarily with Excel - the fact that cells in an Excel
spreadsheet don't have a datatype.

Could you explain what isn't being recognized? There is usually *some*
way to get the data in, if only by importing the spreadsheet to a
table of all Text fields and then using an append query to migrate the
data to a table with the desired datatypes.

I agree though, that it can be a real pain in the neck!

 

[Bob]

Thanks again. The data I'm trying to link is numbers, in
Excel worksheet formatted numbers. I tried exporting the
sheet data from Excel to .csv, and importing to Access,
and it gave me a dialog that allowed me to set field data
types. After several tries, Access did recognize numbers
as such. This, however, was not satisfactory; I need
Access to update from the original spreadsheet. I decided
the problem in linking from the Excel spreadsheet was
that the spreadsheet I was trying to link from was
formula cells. I was able to link to another sheet, in
the same Excel workbook, that served as my original data
entry sheet; it contained no formulas. The data was
linked to Access successfully, i.e., Access recognized
numbers as numbers. So my immediate problem is solved- or
at least avoided. I may not be able to use this
workaround in the future. There appears to be a design
flaw (or bug) in Access or Excel. The other day, for no
reason, Excel started refusing to accept formulas in
cells- it insisted on treating them as text, even when I
used the function wizard. It stopped doing this several
restarts later, again for no reason (I posted about this
in the Excel newsgroup). So the whole thing could be
Excel- except that another program (Surfer by Golden
Software, a GIS mapping program) reads Excel formulas as
numbers just fine. Probably a bug in Excel or Access.
I've now got a new weird problem with Access queries,
which I should post about in the appropriate group.
Again, thanks for your help. Still trying to meet that
deadline!