Data types in linked tables - numbers wanted as text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm linking to an Excel spreadsheet (Office 2003). One of my data fields
contains mostly integers with the occasional text value. To give Access a
hint that it should treat it as a text field, I inserted 5 rows of dummy data
at the top and bottom of the spreadsheet with the text string "Dummy" in the
offending field. I put in appropriate dummy data into most of the other
fields. However, when I link the tables - even though the first and last
five rows have the text "Dummy" in - Access insists blindly that they're
number fields (so my dummy values are returned as #Num).

I've tried refreshing the linked tables, relinking them, deleting the link
and starting again... everything I can think of. Does anyone have any ideas
on what else can be done?
 
Have you tried changing the data type in the linked spreadsheet's design view?
Otherwise, you could create a query to the link or import the data instead of
linking to it. HTH.
 
Linked tables don't allow me to control the data type in the design view -
attributes are determined by Access and are read-only; likewise with running
queries: the text is already converted to #Num by the time the query accesses
it.

Sadly the table is a dump from a system with some duplicate field names;
Linking Tables means that duplicate names are formatted as [fieldname]1,
whereas if I import the data to Access I have to rename the fields manually
first because their name becomes [Field001] or something similarly unhelpful.
 
Sorry for the bad advice. Can the other system dump the data into a text
file instead of Excel? Then you could create an import specification that
sets field data types and lengths. If you do import the data, you can set up
a table with your desired field attributes and simply do a delete and append
to refresh the data. Both of these methods give you more control over the
import. HTH.
Linked tables don't allow me to control the data type in the design view -
attributes are determined by Access and are read-only; likewise with running
queries: the text is already converted to #Num by the time the query accesses
it.

Sadly the table is a dump from a system with some duplicate field names;
Linking Tables means that duplicate names are formatted as [fieldname]1,
whereas if I import the data to Access I have to rename the fields manually
first because their name becomes [Field001] or something similarly unhelpful.
Have you tried changing the data type in the linked spreadsheet's design view?
Otherwise, you could create a query to the link or import the data instead of
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Cool; thanks for that. I tried that and it worked; I also tried using VBA to
set the IMEX to 1. I have no idea what IMEX is, but that seemed to work too!

kingston via AccessMonster.com said:
Sorry for the bad advice. Can the other system dump the data into a text
file instead of Excel? Then you could create an import specification that
sets field data types and lengths. If you do import the data, you can set up
a table with your desired field attributes and simply do a delete and append
to refresh the data. Both of these methods give you more control over the
import. HTH.
Linked tables don't allow me to control the data type in the design view -
attributes are determined by Access and are read-only; likewise with running
queries: the text is already converted to #Num by the time the query accesses
it.

Sadly the table is a dump from a system with some duplicate field names;
Linking Tables means that duplicate names are formatted as [fieldname]1,
whereas if I import the data to Access I have to rename the fields manually
first because their name becomes [Field001] or something similarly unhelpful.
Have you tried changing the data type in the linked spreadsheet's design view?
Otherwise, you could create a query to the link or import the data instead of
Click to expand...
[quoted text clipped - 12 lines]
and starting again... everything I can think of. Does anyone have any ideas
on what else can be done?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top