Read/Update data in SQL Server from Excel
I have a table in SQL Server 2019 that I need to be able to use an excel spreadsheet to read and update the data. Here is what I am looking to do.
1. Create an excel spread sheet that is connected to tableA
2. When opened it needs to read the data from tableA and display it
3. If I change the data in the spread sheet then it needs to update tableA with that new value.
I have found a couple of pricy add-ons that will do this but I have no budget. I should be able to do this with VBA but can't seem to find any good examples. Any help would be greatly appreciated. Thank you!!
Jim
SQL Server is a normalized database with rigid controls over the values that are stored in it: column data types, primary and foreign keys, default constraints, triggers, etc.
Excel is a spreadsheet with zero controls over the values that are stored in it. If somebody wants to put in date values '2021-01-01', '2020-05-07', '2021-01-33', '2021-02-13-14', 'banana', '' empty string, etc. they are free to do so, but all but the first value would be accepted in a normalized database. And about that second value, is it May 7th or July 5th?
Not to mention this opens the door for people of bad intent entering values that would lead to somebody having a resume-generating event.
You can go from SQL Server to Excel, see my article Microsoft Excel & SQL Server: Self service BI to give users the data they want for a correct but pretty outdated way to do it, but there is no button-click-friendly automated way to go from Excel to SQL Server.
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
MS Access might be a better tool for your project.