Avatar of Jim Youmans
Jim Youmans
Flag for United States of America asked on

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.

Open in new window

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

SQLMicrosoft SQL ServerMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
Fabrice Lambert

Ms Excel is a great tool to pull data from a database, but it does not update it (Excel's purpose is not to be a front-end), and there is no easy solution to do it.

MS Access might be a better tool for your project.
Jim Horn

SQL Server and Excel do not support bi-directional movement of data back and forth, mostly because they are polar opposites.

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.


Jim Youmans

ASKER
Thank you all!  I am working on using a power app to do this now.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Jim Youmans

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Horn

In that case I'll take credit for the correct answer as 'You can't do that'.  Thanks in advance.