bizwoman
asked on
Connecting Excel to SQL Server
I have a client who is wanting an Excel quote sheet connected as a bi-directional read-write to SQL Server. I have a plethora of experience with Access in the front-end and SQL on the back-end, but not sure of this scenario. I also have a lot of experience with ODBC connections to pull SQL to Excel for datamining, etc.
I have spent all afternoon playing with options, and can't find a good solution.
They insist their sales reps use the Excel Quoting sheet they have been using for years, but all the data is in these huge data spreadsheets. Here is what I need:
** The quote form to be able to read items from SQL and populate the dropboxes for the reps to choose from.
** I need a way of a very non-tech person to maintain the droplist tables in SQL.
Any suggestions are greatly appreciated!!!
I have spent all afternoon playing with options, and can't find a good solution.
They insist their sales reps use the Excel Quoting sheet they have been using for years, but all the data is in these huge data spreadsheets. Here is what I need:
** The quote form to be able to read items from SQL and populate the dropboxes for the reps to choose from.
** I need a way of a very non-tech person to maintain the droplist tables in SQL.
Any suggestions are greatly appreciated!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I was in a rush and did not see Brian's comment before sending my last one. I just reacted to the question.
Rereading the the thread, I have to add that I am completely in accord with his opinion that it could lead to a mess.
I had to confront that situation very often in my 25 years as a professional programmer dealing with many different customers. Excel is the worst interface that you can use as a data input interface.
Unless you have a lot of VBA in the background to validate whathever the user type in a cell, you end up with so many mistakes that it can lead to a lot of problems with updates and/or database corruption.
The problem is that except for a few programmers who had to work long hours to repair problems that came from the use of Excel as a data entry form, it looks as if everybody feels confident that everything is good when they type in Excel.
Format a column to display dates, and try to type December 32. It works... in Excel. But it bombs in the database... unless the database has the field formatted as a text field, something that I have seen all over the place in the field, and everywhere in questions asked on Expert Exchange. Then, good, nice, hurrah, the updates won't bomb on you. But the data will, eventually. Same thing with numbers. Same thing with almost anything. How many times have I seen something like -------- in a database field, wondering what it was, and discovering that somebody had type this as a separator line in an Excel row. It just went through the update to the database without any glitch.
Gee! Of most of the applications I have seen, and I have seen a lot, Excel is one of the worst I know with numbers. Yes, you read me right, Excel is awful with numbers. I mean numbers typed into Excel itself.
The role of Excel is not for you to type data in it. It is to get valid data from a database or any valid data source, and perform calculations or scenarios on that data. When you type data in Excel, because of the lack of basic validation, Excel is crap.
The reason why you can type in Excel is to enable you to type the formulas for your calculations, to type values that need to be used in calculations, defined as goals or as limits in your scenarios, and the likes.
I have seen huge Excel sheets, in a big corporation, where half the data had been entered twice, and about 10% was in triplicate. Nobody had seen it, because Excel does not validate that type of situation. And important decisions were taken based on the result obtained from that data. Millions were spent uselessly because of these decisions.
Going back and forth between Excel and SQL Server enables you to detect these problems... if the SQL Server side of the operation is done properly. That is exactly how we detected their duplicates when I was asked as a consultant on a project in which we needed to use that data. We removed the duplicates. 2 days later the problem was back. Because "new" data was typed in, and that new data was again a duplicate of what had already been entered.
Excel is not something you use to enter data. Everybody does it. But if you think that data is important, then "you" do not do it.
Rereading the the thread, I have to add that I am completely in accord with his opinion that it could lead to a mess.
I had to confront that situation very often in my 25 years as a professional programmer dealing with many different customers. Excel is the worst interface that you can use as a data input interface.
Unless you have a lot of VBA in the background to validate whathever the user type in a cell, you end up with so many mistakes that it can lead to a lot of problems with updates and/or database corruption.
The problem is that except for a few programmers who had to work long hours to repair problems that came from the use of Excel as a data entry form, it looks as if everybody feels confident that everything is good when they type in Excel.
Format a column to display dates, and try to type December 32. It works... in Excel. But it bombs in the database... unless the database has the field formatted as a text field, something that I have seen all over the place in the field, and everywhere in questions asked on Expert Exchange. Then, good, nice, hurrah, the updates won't bomb on you. But the data will, eventually. Same thing with numbers. Same thing with almost anything. How many times have I seen something like -------- in a database field, wondering what it was, and discovering that somebody had type this as a separator line in an Excel row. It just went through the update to the database without any glitch.
Gee! Of most of the applications I have seen, and I have seen a lot, Excel is one of the worst I know with numbers. Yes, you read me right, Excel is awful with numbers. I mean numbers typed into Excel itself.
The role of Excel is not for you to type data in it. It is to get valid data from a database or any valid data source, and perform calculations or scenarios on that data. When you type data in Excel, because of the lack of basic validation, Excel is crap.
The reason why you can type in Excel is to enable you to type the formulas for your calculations, to type values that need to be used in calculations, defined as goals or as limits in your scenarios, and the likes.
I have seen huge Excel sheets, in a big corporation, where half the data had been entered twice, and about 10% was in triplicate. Nobody had seen it, because Excel does not validate that type of situation. And important decisions were taken based on the result obtained from that data. Millions were spent uselessly because of these decisions.
Going back and forth between Excel and SQL Server enables you to detect these problems... if the SQL Server side of the operation is done properly. That is exactly how we detected their duplicates when I was asked as a consultant on a project in which we needed to use that data. We removed the duplicates. 2 days later the problem was back. Because "new" data was typed in, and that new data was again a duplicate of what had already been entered.
Excel is not something you use to enter data. Everybody does it. But if you think that data is important, then "you" do not do it.
https://www.mssqltips.com/sqlservertip/1540/insert-update-or-delete-data-in-sql-server-from-excel/