Problems linking two number fields

  • Thread starter Thread starter mogens
  • Start date Start date
M

mogens

When using Get External Data > Link Tables, and then choosing an
MS-Excel workbook, I have no possibilities to define the data type of
the individual fields. It seems as if a number field will automatically
be defined in Access as number of format "General Number"

My problem now is, that when I try to link a field called orders_id from
this linked table to a field called orders_id (Int, 11) in another table
(linked from MySQL), the join does not seem to work. I guess this is
because the first field is not interpreted as integer, so that Access
cannot join the fields? However there are no warning signs when
establishing the joins about type mismatch or the like, so somehow
Access seems to accept the join, but does not use it.

Can anything be done to force the "Get External Data" to see orders_id
as Integer, or is there another workaround?
 
A couple thoughts...

"General Number" is a format, not a data type.

Using "Link Tables" doesn't constrain the underlying data/table you are
linking to, just provides a way to see it.

Try right-clicking on the linked table. Select "Design...". You'll be
warned that it is a Linked table and that your changes will not be saved.
That's ok.

When the table design window opens, take a look at the field you are trying
to join on. What data type has Access used in interpreting that field?
 
Jeff said:
A couple thoughts...

"General Number" is a format, not a data type.

Using "Link Tables" doesn't constrain the underlying data/table you are
linking to, just provides a way to see it.

Try right-clicking on the linked table. Select "Design...". You'll be
warned that it is a Linked table and that your changes will not be saved.
That's ok.

When the table design window opens, take a look at the field you are trying
to join on. What data type has Access used in interpreting that field?
Click to expand...
I actually got the information as you describe, but looked too far below
I can see now ;-) The Data Type is interpreted as "Number".

Meanwhile I have tried to save the Excel file as a tab-delimited text
file, as I can here control the data type of each field when creating
the link to the Access table. A little clumpsy way, but the only way I
could think of now that the same possibility does not exist when linking
to an Excel file. However, having defined orders_id as Integer didn't
solve the problem, so maybe something completely different is wrong.
 
Back
Top