Hi I'm trying to use an excel spreadsheet (with the first row containing column names and the rest containing data) as a datasource.
I have built a connection string that works, but when I try to bring back a recordset based on some SQL I fail because I don't know what the 'table name' is for an Excel sheet.
I am connecting using OLEDB (for DSN) I think and the connection code goes through, but when I query using the sql: "SELECT * FROM table" it always gives an error of object not found. I have tried the sheet name, 'Sheet1', the file name, and a variety of other things to no avail.
Anyone got any bright ideas? I am doing some automated data conversion into access from the sheet, so getting a recordset would be a good start, but an alternative would be to get it into a temporary table where I can start to work on it!
Thanks in anticipation
Phil.
I have built a connection string that works, but when I try to bring back a recordset based on some SQL I fail because I don't know what the 'table name' is for an Excel sheet.
I am connecting using OLEDB (for DSN) I think and the connection code goes through, but when I query using the sql: "SELECT * FROM table" it always gives an error of object not found. I have tried the sheet name, 'Sheet1', the file name, and a variety of other things to no avail.
Anyone got any bright ideas? I am doing some automated data conversion into access from the sheet, so getting a recordset would be a good start, but an alternative would be to get it into a temporary table where I can start to work on it!
Thanks in anticipation
Phil.