Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Simple 'front ends' for non-developer?! Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 1:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
Guys,

I've been thinking of a few different ways to tackle a new project, using Excel, third party web-based tools, some other 'corporate' tools etc, at the end of the day the flexibility of Excel works well, however, the further I delve into this the more I'm trying to shoe-horn Excel into being a database! - With far more SQL knowledge than Excel knowledge this doesn't seem to make sense (I was even Googling how to replicate select top x in Excel this morning!).

The only stumbling block for me using SQL is that I'm not a front end developer - I have no idea how I'd actually insert and update my data besides using SSMS, clearly using SSMS would work, however, I would prefer some form of front end.

So, my question is what methods are available (aside from building a .net website!) for managing the data within the structures I'll create? - For example can Excel be used? Can I create a front end in SSRS? Is there a stupidly simple way of creating a webpage which just has in effect raw tables? - In essence I'd like to see an entire table in Excel and be able to add and edit rows with the changes being replicated in SQL.

Sorry for this rather newbie/odd question - In all my SQL time I've just been querying data, creating SPs and reports, I've never got involved in front end presentation (besides a report).

Thanks!
Post #1450921
Posted Thursday, May 9, 2013 3:17 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
Another alternative is to use Access as the front end for your SQL Server database. I haven't used Access in years but from what I remember, it was pretty straightforward to setup up forms/reports.

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1450951
Posted Thursday, May 9, 2013 3:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
I did actually consider Access this morning (for everything) but thought it was a step backwards to try and remember access from about 15 years ago when I was at school, when I already know SQL well enough for what I'm doing.

I'd not considered using the two as a hybrid, I didn't know that was possible... interesting...!
Post #1450955
Posted Thursday, May 9, 2013 3:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:27 PM
Points: 13,776, Visits: 28,178
I've been using Access as a front-end for Windows Azure SQL Database for a project for the Boy Scouts. It works well enough. You can get going pretty quickly. There are some odd behaviors when it comes to lookup values in drop down lists that I'm still struggling with (my Access days are about 20 years behind me), but it got most of the job done very quickly and easily, which was my requirement. It'll work great with SQL Server too.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1450968
Posted Thursday, May 9, 2013 4:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
I do have Access installed too as part of Office 2010 so I'm giving it a look right now, using this: http://www.access-programmers.co.uk/forums/showthread.php?t=216956 as a v quick guide for linking to SQL... not sure how I'll find it after that! Time for a play me thinks.

It's basically for my own benefit - a tracking system if you like - Excel alone didn't seem to cut it, things like Basecamp seemed a bit too distant and something like project seemed too rigid, expensive and also came with a learning curve.

Thanks for another access vote!
Post #1450978
Posted Thursday, May 9, 2013 4:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
Well I've got Access talking to my test database and I can edit and add rows to tables so in principal it's working, tarting it up a bit and making it a bit more useful will come later. That was actually far easier than I expected - scary!

On a side note, it's possible what I'm trying to do might be possible via TFS, which we're getting soon so I might try and hold off for a little while.
Post #1450999
Posted Thursday, May 9, 2013 6:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 358, Visits: 645
Hi
my instinct is Access would be best, but iirc, you can create parametrized stored procedures in SSMS & invoke them from Excel...i guess it depends on how robust the front end must be and how much you want to stick with Excel, but its viable.

http://blogs.office.com/b/microsoft-excel/archive/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba.aspx
best
drew
Post #1451065
Posted Friday, May 10, 2013 6:15 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 760, Visits: 2,222
drew.georgopulos (5/9/2013)
Hi
my instinct is Access would be best, but iirc, you can create parametrized stored procedures in SSMS & invoke them from Excel...i guess it depends on how robust the front end must be and how much you want to stick with Excel, but its viable.

http://blogs.office.com/b/microsoft-excel/archive/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba.aspx
best
drew


You can definitely call parameterized stored procedures from Excel, with a few caveats. 1. You must use an ODBC connection. 2. The only way I've found to set up the query in Excel is to add a simple table query, then modify the command text on the connection to the stored procedure.

There are also ways to call stored procedures from Excel VBA, but I don't do that often so I don't remember offhand how.




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1451546
Posted Friday, May 10, 2013 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
Here's another idea I had put together a simple Reporting grid app for our end users;
it's a windows app featuring the DevExpress Report Grid;

on the left, it has a list of all the "public" VIEWS int he database that follow a naming convention of "VW_{viewname}"

the DataGrid is ReadOnly, every time they add a filter to the Dev Express Grid, a SQl query for the view is modified to be ready if they hit the Run button. It also does a Limit of TOP 1000 Rows (order by 1 if no sorting is selected on the grid)

since the grid has the ability to save to just about any format, that works pretty well for us so far;


If someone needs a new "report", someone lime myself just needs to create a view, test it, and then rename it to the naming convention to be immediately available.





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1451559
Posted Friday, May 10, 2013 7:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
The more I've played with this (and made some interesting discoveries along the way, thanks for hte pointers guys!) the more I realise that trying to build a homegrown solution might not be the best approach. I keep going around in circles as to the best way to do this, I think it might be more sensible to dedicate some time to learning MS Project - trying to create my own project management solution is probably going to snowball out of control and end up with me spending ages building it rather than actually project managing!

Thanks for the feedback guys, @Lowell, that's an interesting approach, something I think we pretty much achieve with SSRS either via Report Manager, file share subscriptions or email subscriptions, I keep telling people reports shouldn't really be full data dumps but it seems to work out okay!
Post #1451592
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse