May 16, 2018 at 10:46 pm
Comments posted to this topic are about the item Using SQL Server as a Source with for an Excel Application
May 17, 2018 at 2:35 am
I do a lot of extracting data from SQL Server for displaying in Excel.
What I have found work best is to use ExcelDNA (https://excel-dna.net/) to provide an intermediate C# layer to a) do the SQL query, and then b) format then returning the result as a 2-D object array. ExcelDNA allows you to create both Functions (static methods) that called directly from the sheet and self-registering COM objects that can be created and called from VBA.
May 17, 2018 at 3:40 am
I suggest that people check out the excellent approach in DataPig that allows direct conectivity to SQL Server and enables Excel to run stored procedures ( or SQL scripts if you can deal with the VBA formatting ).
http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel/
http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/
One can still do straight data table dumps into Excel, as well, of course, but using procs is the basis for building an extensive reporting solution.
Regsrds,
Leo
May 17, 2018 at 7:47 am
Our approach is to use ADO within VBA in Excel. We use this for connectivity to execute SQL procedures for the data extraction.
May 17, 2018 at 8:21 am
I have no issue with using ADO or not. Is there some particular benefit from using ADO and datasets in your solution?
The approach from DataPig is very simple and allows the developer to avoid ADO and data sets when returning data from a proc, with or without parameters.
I used to use ADO and write a lot of VBA until I found this alternative approach, which I have used successfully in several corporate environments.
I would prefer to run everything from the web with browser-based ASP.Net components or to use Sharepoint as a foundation, but when this has not been possible ( IT politics ), the non-coded approach is is easy, quick and reliable.
Regards,
Leo
May 17, 2018 at 8:34 am
Well, I'm not familiar with DataPig so I can't comment on the relative merit of it vs ADO. One advantage of ADO is it comes with Excel.
We use VBA to handle the inputs via form controls, create the connection to SQL using ADO, execute the data extraction using ADO and a SQL procedure (returning datasets) then format data, created plots etc. in Excel using VBA again.
In the past we created some ugly 'command strings' i.e. SQL commands using VBA and then executed the strings. Now we do the SQL part using SQL procedures; and arguments required are assembled on the VBA side.
Doing the SQL in procedures is a big improvement over the execute string approach for clarity, maintainability, etc.
May 17, 2018 at 9:15 am
Well, the point of my suggestion that you evaluate the Data Pig approach is that eliminates VBA and the connectivity used is built into Excel. There are no "add-ins" and, if there are no user-set parameters, you do not need any VBA.
I am merely suggesting an approach that I have used extensively over a number of years that is easy to implement.
Regards,
Leo
May 17, 2018 at 9:29 am
use VBA as a major data management tool
Somehow, that frightens me.
Gerald Britton, Pluralsight courses
May 17, 2018 at 9:51 am
Leo Cohn - Thursday, May 17, 2018 9:15 AMWell, the point of my suggestion that you evaluate the Data Pig approach is that eliminates VBA and the connectivity used is built into Excel. There are no "add-ins" and, if there are no user-set parameters, you do not need any VBA.
I am merely suggesting an approach that I have used extensively over a number of years that is easy to implement.Regards,
Leo
I had a quick look at Data Pig. I think our applications are a bit different. We're doing canned reports with user specified parameters. It appears to me that Data Pig is more of an ad-hoc data access tool. I didn't mean to imply I had anything against the Data Pig approach.
May 17, 2018 at 10:09 am
The 2nd link in my initial post is to an article that shows an easy way - with minimal VBA - to facilitate the use of user-set parameters with stored procedures.
I would note that, if the Excel file is saved after going through the steps in either of the 2 articles, then the Excel file is portable with its connectivity, and the ODC file that is created is actually not required.
( I take it as given the SSPI Integrated Security is used to control access. )
As noted, I have implemented this solution in several corporate environments.
Regards,
Leo
May 17, 2018 at 12:28 pm
I once worked for a very large beverage company that also owned its own packaging (think bottles and cans) subsidiaries. Everything actually worked very well and very fast. Almost all of the major systems including warehousing of the product was performed by robotic systems that relied heavily on the databases to record data as it occurred.
Plant managers wanted to have access to the status of the plant at all times, and to facilitate that, they were given MS Excel and trained to load data into Excel from the same SQL databases that so diligently monitored production. It was fairly simple for the managers to establish some charts and what-not, and then set the auto-refresh to every minute. Everyone was happy. Even the DBA's, because we had no idea that this sanctioned access to the database had been allowed and encouraged. This was at the start of a matrixed service support model at the company. We were a small team of DBA's that covered a lot of ground but were treated like outsiders for the most part by the subsidiaries that used our services. We were not the masters of our domain when it came to the databases. Instead we were shepherds and caretakers.
Around this time, database performance suddenly went into the toilet, and this, in turn affected automated systems that were programmed to wait on database commits. Phone calls were made, DBA's were blamed, same old story. After some quick investigation (this was using SQL 2000 and some SQL 2005), we determined that the connections from the plant manager's machine was causing considerable locking and blocking and effectively bringing the server to its knees. As soon as we pointed this out, we were shouted down, called stupid, and told to keep looking, because this excel spreadsheet could not possibly have been the culprit. And the managers liked having the almost real-time data. This was the start of a long month or so of watching performance around the clock, noting the time when the excel queries hit the system (the manager's desktop only ran during business hours), and then killing the queries to clear blocking and allow cans to be produced and tracked by the computer systems. Occasionally we would go out to lunch,only to be paged because of database performance affecting plant output. We would mention the excel connections. Shouting would occur, our management would be called, and we would be told to just make it all work.
This was all actually due to the way that the Microsoft JET engine copied data from SQL tables into Excel and also Access. The JET engine transferred all of the data in the tables to the the requesting computer and then performed the queries at the destination. If the destination computer did not have enough memory to handle these tables, then it would start to page data. We had to show the documentation from Microsoft to management again and again and eventually, they brought in some real programmers to pull the data out of the databases with .NET. It was a long summer.
So whenever I hear that Excel is connecting directly to a SQL database, I get a little tic that twitches in one eye for a while.
May 17, 2018 at 1:06 pm
I am sorry to hear of your history at the bottling company. Seems like a high pressure environment; I don't know if I would have lasted very long there. My experience with the SQL/Excel connection was a four month contract with a telecom company transmitting inventory reports to branch offices. The database connections were nothing like production/realtime processing data. The clients had already set up their SQL Server platform and needed a simple data connection and automation of the transmittal process. For this the VBA application SendMail dialog function through MS Outlook was convenient. I have had other experiences with Excel and Access VBA, mostly with small to mid-size companies which have begun their data management processes with small spreadsheets or databases. As the data operations have gained scope and size, the clients realize the need for increased data capacity and automation. Some have remained with MS Office, some have moved up to SQL Server with an eye to eventually moving to Big Data or some cloud products. One works with what one has been given. I hope this reply has not caused any additional tics.
May 18, 2018 at 2:20 pm
This brought back some not-so-fond memories of working with Excel/Access and all their limitations. I guess I've been fortunate to be working with "real" databases over the last decade or so. But as previous poster pointed out "we work with what we are given". Thank you for sharing your experience, who knows what I "will be given" in the future.
February 16, 2021 at 7:02 am
Hi, I want to ask if you please can let me know the password to unlock the protected sheet. So, that I can see it in action.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply