I have created a System DSN which connects, and tests successfully to an excel workbook.
I then set up a new SSRS report in BIDS, and a new Shared Data Source (and corresponding Data Source), which connects successfully to the Excel workbook.
I then setup a new dataset, which simply says "SELECT * FROM [StaffStatistics$]", and this, again, when run in the Query Designer of the Dataset Properties, returns all of the required data.
However, when I then press "Preview" to return this data to a Tablix, it gives the error:
"An error occurred during local report processing.
An error during report processing.
Cannot create a connection to data source 'DATASOURCE NAME'.
ERROR [HY000] [Microsoft] [ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data. ERRRO [IM006] [Microsoft] [ODBC Driver Manager] Driver's SQLSetConnectAttr failedERROR [HY000] [Microsoft] [ODBC Excel Driver] The Microsoft Office Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.
So... In before: "Is the Excel workbook definitely not open, anywhere on the network"! No, I can confirm it is not. The Excel sheet has been saved as 97-2003 and Office 12, and both deliver data top the point of Query Designer in SSRS, but will not run when Previewed...
I am running on an x64 machine, so there is the BIDs x86 -> OS x64 to consider, maybe this only presents itself when previewing the report as the 'engine'(?) is different to that when just viewing the data, as opposed to running the report?
Any help or new google search paths would be great, I am on, say, page 20 of google searchs containing "Excel ODBC SSRS" etc!