September 17, 2013 at 3:46 am
Hi all,
My first post here on SSC. Some of the core systems where I work use Oracle databases and Hyperion which comes with the Smart View MS-Excel Add-in which allows queries to be easily created to interrogate and retrieve data from Hyperion cubes.
Nothing that can't be achieved with Excel and a SQL Server data connection so far, but there's a key difference. With Smart View data can also be submitted from Excel into the cube with one click using either free form query grids or pre-defined forms.
Given that most of the end users where I work are used to the ease of submitting data using Excel via Smart View (with all the copy/paste, auto-fill etc familiarity and ease of using Excel), the ASP.NET based web app that we're currently using to input data into SQL Server 2005 feels positively clunky.
To add all the multi-cell copy/paste, autofill etc functionality to the web application would take a fair amount of ASP.NET/Javascript coding. I'm just wondering, given that Excel and SQL Server are both MS products, is there an existing way to easily integrate them to allow that crucial upload of data to go with the existing excellent data retrieval methods?
November 13, 2013 at 11:22 am
First of all, I am a Newbie myself.
The answer is .... it depends.
I have set up a table in SQL Server, usually a staging table rather than a direct link to the final table. This allows the data types, the quality checks and other data scrubbing to take place first.
Make the Table sharable. Then in Excel use the external data link. Probably the most common way is ODBC.
Set the start location for data in Excel. The data will come into the Excel workbook.
This is linked data. Yes, it can be added and modified.
A better method:
Use MS Access. Then use DSNLess code to create a link from an Access Table to the SQL Table. My preferred driver is the free SQL Server Native Client 11
If you want to really make sure the Access table field types match SQL Server (including autocounter, index, numeric, binary, ...) then design the table in MS Access. From there use the free download Microsoft SQL Server Migration Assistant for Access.
Between these two, you will have a very high convertibility factor between Access and SQL Server. The Access Table is basically the Linked Clone of SQL server. Professional Access Developers do this all the time.
Now that MS Access has a linked table with all the security connection embedded in code, put the Access DB on a shared network.
It can even be compiled to prevent mid-level users from seeing any menu items.
NOW - use the Excel to link to this Access Table that is Linked to SQL Server.
This removes any UN/PW /Link information from Excel to SQL Server.
Just to take it a little further... I use Excel Object Model Programming to evaluate the tables and create custom Excel workbooks on demand. From Access the user can request something, then the custom Excel appears in their home directory. This way, the custom functionality, formatting, results and the rest can be presented to that individual viewer. Granted, this is more specialized. By using Stored Procedures and SQL Server Views, my preference is to create extremely customized "reports" with customized array formulas that match the data pull and utilize business Class Modules to analyze the data across large data sets into a concise data-mined report with all of the custom settings.
This is just a sample of the options.
When you find the SQL Server Native Client download, there is also a new Excel analysis add-in for SQL Server. My hope is to learn what it is about and write some articles about it in 2014. It sounds very exciting for those of us who use Citrix.
http://www.access-programmers.co.uk/forums
You can find a lot of this at the site above. I have over 1,000 post and the total is over 1,000,000
I visit this site often because writing TSQL is only about 10% of my time. The gracious people here help me often.
November 14, 2013 at 9:16 am
Thanks for the pointers/suggestions. I'm not familiar with some of the techniques / functionality you mention, so will go away and do some reading up.
I have negative memories about using Access databases on a network share (admittedly the older versions) with databases getting corrupted. But even more recently, I found creating a linked SQL Server table in Access 2010 yielded unreliable data updates (retrieves from SS were fine) even when pasting in only 20-30 records from Excel with only some of the rows making it into the SQL Server table for some strange reason that I didn't have time to get to the bottom of. In the end I ended up importing the Excel file directly using SSMS.
Obviously that works fine for ad-hoc updates with me as the developer/dba, but not fine for finance users who need something simple that just works straight from Excel.
I just tried creating an ODBC linked table from within Excel 2010 and it uses Microsoft Query to define the query. I just noticed the "Records\Allow Editing" option for the first time in MS Query. It works within MS Query and I managed to save a new record back to the database. However, when I choose to return the data to Excel in a data table, although it allows me to edit the existing rows and insert new records within Excel, I can see no way of pushing the new/changed records back to SQL Server from within Excel itself. Does this bit need to be done programmatically with e.g. VBA?
The other idea I've been toying with is to create a link to the database table from SharePoint and then use a SP list or Infopath form to allow editing, but didn't get this to work on my first attempt so need to have another go. But in all honesty would probably prefer an Excel solution over the SP one.
November 14, 2013 at 10:29 am
The Access DB corruption isn't unique. But, it often is tied to a lack of a plan. It is not uncommon to see people trying to open up the combined front-end / back-end application from a network location. That will guarantee failure. It is like my IT support staff setting up SQL Server using the "recommended requirements". They had used that as the metric for why SQL Server wasn't any good. LOL MS doesn't do any better to warn about some basic Access setups.
My preference and suggested mode is to link Access Front-End to a SQL Server back-end database. There are so many advantages to SQL Server.
I am taking next week off to spend time with my son returning from his USMMA 300 days at Sea engineering internship.
The IT group told me this morning to prepare for Office 2013 in early Jan 2014.
You might ping this about mid Feb next year. I will set up some test to evaluate this once the newer versions are installed.
Also, this morning on my MAPS (MS partner) site, they are starting the new SQL Server - Excel add-in training.
Rather than cobble something together that is becoming obsolete, it will be worth waiting for the new.
For sure Sharepoint has gained popularity to distribute data. But, with any technology comes an overhead.
Your question brings up an interesting aspect to simplicity for managing records.
It is something I should look into after the New Year.
Sorry I can't spend more time on it now.
November 14, 2013 at 3:00 pm
"I can see no way of pushing the new/changed records back to SQL Server from within Excel itself. Does this bit need to be done programmatically with e.g. VBA?"
Yes there is a way with ODBC or OLE-DB and VBA. Excel can be an excellent front end to SQL-Server.
The step you have to take is to learn VBA do it!
I have many examples if you like to have a direct Contact.
Gosta
November 15, 2013 at 4:37 am
Yes please! I'll pm you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply