Establish relationship beteen SQL query results and Access table

[zyzolus]

Hi!
I import an Excel datasheet (read-only so no modification in the *.xls
file is possible) using SQL query (and skip first rows):

SELECT *
FROM [Excel 8.0;HDR=Yes;database=C:\directory\datasheet.xls;].
[Sheet1$A3:C1000];

I would like to link the result of the query with an Access table
using a relationship, but an error message is displayed: "Data type
mismatch" (but setting a primary key and its data type in the linked
table is not possible).

What is the correct way to perform this operation?
Must I save the result of the query to a table (each time data in
Excel datasheet is updated), where I would be able to set field data
types and primary key?
Or is it possible to make a direct relationship between the result of
SQL query and Access table in some way (I guess running SQL query is
faster than saving data to a table)?

Thank you for your advice.

Zol.

Access 2003
Excel 2003

 

[Guest]

Haven't tried this recently: I thought I could join a table
to a linked spreadsheet. Try again with sample data to
see if it is a problem with your data.

Yes you can use a query to convert text fields to numeric
fields so that you can join on mismatched fields. Use
val([fld_txt]) or cstr([fld_num])
because val will convert invalid text to 0 zero. (cdbl would
error on invalid text, which would probably spoil the whole
query).

If you are using dynamic SQL, you can actually put the
type conversion function directly into the join, but you
can't display that in the query design grid, so if you are
using a saved query, make the conversion separately before
you do the join.

(david)

 

[zyzolus]

Thank you for the reply and your hints.

zol.