Using Excel to work with SQL data (read/write)

I have a ton of data in a sql database which I would like to be able to import and display in excel (I can already do this) and additionally modify or append to the dataset within excel and write the changes/additions back to the 开发者_StackOverflowdatabase.

What is the best way to go about doing something like this?

Please let me know, thanks!

The way to do this is via Sql Server's DTS/SSIS capabilities. Create SSIS packages for Excel import and export and execute them as needed.

However you still have the issue of people having to share this massive spread sheet. You should consider importing the data into the db permanently and providing a winforms interface for the data entry. You'd be surprised how quickly you could whip out an app with a databound grid view control that would give you decent, Excel-like ability to add/edit/delete table data.

Although Excel is great at displaying/reporting on data stored within a SQL DB, it has no built-in controls for updating the data.

I would recommend investigating using VBA (Visual Basic for Applications) or based on your coding experience/tools available to you, VSTO (Visual Studio Tools for Office).

This method will allow all of your users to share the spreadsheet at the same time and allow incremental updates plus validation of the data being entered by the user at the point they enter it.

All the usual gotchas apply though - mainly GIGO (Garbage In, Garbage Out). Correctly authenticate your users and what they are allowed to update