Simple 'front ends' for non-developer?!
-
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!
-
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[/url]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 -
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...!
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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!
-
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.
-
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.
best
drew
-
drew.georgopulos (5/9/2013)
Himy 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.
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. -
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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!
-
I've been using Access as an SQL frontend for over a decade, both as Access Projects (.adp) and Access Databases (older versions .mdb & new versions .accdb). Using Access can be as simple or as complicated as needed for the frontend. For the most part, Access builds the forms for you using wizards. First, let the wizard create the basic form. Then, you customize it as needed by creating combo boxes for lookup tables and command buttons to link to other forms by dragging and dropping to initiate the wizard. You don't need to know how to code a command button or create a lookup combo box. However, because I implement various business rules at the frontend level, I use the code-behind VBA of the forms to enforce these rules. For example, I'll disable certain fields based on the data of other fields. The only caveat to using Access is that sometimes it is tricky to use stored procedures, which you typically do have to create VBA code to execute. Further, for maintenance purposes, I find using Access to do quick data edits in a table or view easier than in SSMS. Of course, this is for when an Update query or stored procedure can't be used. I'm frequently having to clean up imported data, where the bad data is too inconsistent to use an Update query with a Where clause.
My decision to use a Project over a Database depends on my users. My SQL Server databases tend to contain data for several audiences. If the audience of the intended frontend uses only a few tables and views, I'll create an Access Database with an ODBC connection and link only the necessary items. However, I use an Access Project, which directly connects to the SQL Server, for when the audience uses several objects or when I need to use stored procedures as the record source for my forms. There are major differences, though, between a project and a database. An Access Project only allows you create and maintain forms, reports, macros and VBA modules. Whereas with an Access Database, you can also create frontend queries and tables that are useful with static lookup data (i.e., a lookup table of the 50 states for all State fields), which can minimize data transmission to/from the server. In addition, accessing stored procedures differs between the two depending on how you need to execute them.
-
J. McCarthy (5/10/2013)
Further, for maintenance purposes, I find using Access to do quick data edits in a table or view easier than in SSMS. Of course, this is for when an Update query or stored procedure can't be used. I'm frequently having to clean up imported data, where the bad data is too inconsistent to use an Update query with a Where clause.That's one of the main things I took from my brief Access experiment to be honest, in fact I was almost frightened about how easily I could edit SQL data from access.
As regards your comments about project vs db, I was referring to using Microsoft Project for Project Management as opposed to creating a homebrew solution, the comments about projects vs databases in Access are something I'll take on board though 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply