Learn How to Create a User-Friendly Excel SQL Tool
Learn how to create an Excel tool to export, update and insert data out of SQL Server tables and into SQL Server tables. Learn how to add a user login pop-up box to prompt for a valid username and password. Learn how to add pre-SQL data validation with user-friendly validation error messages. Learn how to only detect and update local Excel data changes. Add in-cell dropdown options that users can choose from. SQL filters and much more!
Hello, my name is Jon Muller. In this article, I’m going to summarize how you can create a complete solution to export data from a SQL table into Excel and then update the edited data back in the SQL Server table, or even insert new records into the SQL Server table. I will show you how you can validate the data. I’ll show you how we can add a log-in prompt and how you can filter the data and much more.
Local edits are another big thing. I’ve had a few people ask me about whether it’s possible to add a local edit option so that you can only let Excel update or insert the data back into SQL Server that has changed. Adding functionality to detect local changes and only validate and update those rows of data can improve performance and not waste SQL server resources by updating records that did not change.
If you are new to my kudutek YouTube channel then if you are comfortable with VBA and you are a seasoned power Excel user, I would recommend that you start with this video:
Update SQL Table from Excel - Learn How to Create a User-Friendly Excel SQL Tool
This video will show you what caliber of Excel SQL solution is possible and what the ultimate SQL Excel data editing solution could look like. If you develop a tool that you want to give to your users that they can use to manage data and bulk update SQL data, insert and update data between Excel and an SQL table, then this video is for you.
But if you are not that familiar with VBA and if the concepts look a little complicated and a little overwhelming to you then I would recommend that you start with these videos first:
How to Export and Update SQL Server table data using Excel Step by Step PART 1
How to Export and Update SQL Server table data using Excel Step by Step PART 2
After you have watched the above videos you should watch Part 3:
How to Insert Data into SQL Table from Excel: Export, Validate, Update and Add using Excel
In the above video, I take you through the basics and use some VBA to show you how to export the data from SQL Server from an SQL table into Excel. And then I show you how you can do some basic updates and inserts back into the SQL Server table from Excel.
Finally in Part 4 without diving too deep into detail I show you the complete Excel SQL Export, Update and Insert solution complete with Local Edits detection, SQL user login prompt, excel and pre-SQL data validation, as well as SQL query filters.
Update SQL Table from Excel – Learn How to Create a User-Friendly Excel SQL Tool
The above video is different from the preceding three videos because in this video I do not dive deep into the VBA nitty-gritty details. My thinking is that after watching the first three videos about exporting the SQL Server data and updating and inserting the SQL table data back from Excel, you have a good foundation.
So this video again is to show you what is possible and practical and that means that you should download the complete demo example Excel file <<here>> that I show in this video. Once you downloaded it, open it and poke around and reverse engineer it and learn from this Excel SQL data editing demo tool. And then take this solution and develop your own for your SQL Excel project requirements.
Let me know if you have any questions. I am going to create a Facebook group soon specifically to, for those who want to learn more about Excel and SQL and some programming. And so we can just join that group when it is ready and we’ll help each other.
