Excel to SQL

kw66722
kw66722 used Ask the Experts™
on
I have a SQL database in Azure. My client wants to be able to connect to one of the tables in excel and update and add to the data from the spreadsheet directly in real time.  

Is there a way to do this?  

I had created an application in .net using a dataview they did not like it because not all the features that they use in excel are available. Most importantly easily increasing the font size, and filtering the data.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Let me clarify - you want the client to connect to the database from Excel spreadsheet?

The client can connect to Azure SQL from Excel spreadsheet by going to Data tab, then clicking 'From other sources" button, selecting "From SQL Server".

Then the connection wizard will pop up and prompt for Azure SQL server name and login credentials. Then the user will be prompted to select the database and table in question

Author

Commented:
When I do that the data is imported to the excel spreadsheet and any changes made are not saved on the server.  I need the changes made to be added to the Azure database in real time.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
You could modify the Excel VBA in my article Microsoft Excel & SQL Server:  Self service BI to give users the data they want to connect to a table/view instead of a SP, but just to 'check the box' here, if your intent is to allow users to edit database data in Excel, then

YOU ARE PLAYING WITH FIRE!!

as this will prevent any validation of that data before it is inserted into the database, plus security will need to be considered so that only certain users who should be allowed to enter data have access to open the Excel database.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I redesigned the table based on the data integrity issues. I will read your article.

Author

Commented:
I reviewed the article I did not see where the solution presented updates sql or where an inserted row is added to the sql table
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".

Author

Commented:
This answers part of the question. I will submit another question to answer the rest.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial