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 Friday, May 10, 2013 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 2:06 PM
Points: 4, Visits: 21
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.
Post #1451659
Posted Friday, May 10, 2013 10:02 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
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 :)
Post #1451671
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse