Colloboration between T-SQL Table and Excel Sheet(s)

Question

Let's say I have a T-SQL table with 20-30 columns and 2-3 millions rows (huge table). I want to allow a user to update this data directly (the solution must work for different tables). I generally have the following options:

• to create simple web interface displaying only part of the data via paging
• to create desktop application which can download the data (on parts) and sync it when it is changed

This will probably work, but the userwill be force to learn how to work with the interface, and the application functionalities will consume a lot of time to develop - it will be easier to use tool that is already created and the user is familiar with (for example excel).

I know I can export/import data between T-SQL table and excel file, but is there a way to build some kind of collaboration between them - for example the excel to call a stored procedure which is returning the data (it will be nice if I can send a hash of the data or the rows in order to return only the rows that have been changed). And to call a stored procedure which can upload/delete/insert data in the table.

If not, I guess I am going to work with files - download the file, edit the data in excel, then upload it, if there aren't other ways?

Answer 1

It's very easy to do this in access. Setup an ODBC Connection on the users computer then add in a linked-table into the access db.

I don't think it would take too long for someone to create an application with a nice winform interface. We have one at my office where if you create a function/stored procedure in SQL starting with certain characters, lets say dbo.app_*******, it will appear as a report in the application and any parameters in the stored procedure will prompt the user to input a value in the app.