Is it possible to make some kind of link from SQL to Excel making the Excel documents the true datasource? I don't want any data to be stored in SQL, all data editing will be done in the Excel sheets.
I know this is far from optimal but I don't have a choice, the data needs to stay in Excel.
I know there is an option to do this kind of links in Access and there I can access the data from within VS but I would really prefer SQL.
It's also possible to use the Jet driver directly from your application and skip out SQL server.
I know this isn't a proper answer to your question but another thought that may be useful :)
Yes, you can set up excel as a linked server. You'll need to use a jet driver but this is only available on 32bit machines (Microsoft dropped their support a while back). In other words, this is completely not scale-able and not recommended in most production environments.
If you want to choose Excel as a datasource then you can connect to it using .Net Oledb provider.
Consider VB.Net example below to read rows on excel sheet:
imports System.Data.OleDb
dim connstr as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\exceldb.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim conn As New OleDbConnection(connstr)
conn.Open()
Dim da As New OleDbDataAdapter("select * from [Sheet1$]", conn)
Dim dt As New DataTable()
da.fill(dt)
For i As Integer = 0 To dt.Rows.Count - 1
'do your thing
next
conn.close()
Regards
