Linked Table

  • Thread starter Thread starter Cathy
  • Start date Start date
C

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
 
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.
 
How can I correct this or force it to a text format?
Click to expand...

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.
 
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
Click to expand...

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!
 
Thanks for your help. I tried mine several times and
finally got it to work. I'll try your method next time.
 
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.
 
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.
Click to expand...

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!
 
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!
 
Back
Top