Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple 'front ends' for non-developer?!


Simple 'front ends' for non-developer?!

Author
Message
J. McCarthy
J. McCarthy
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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.
Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 684
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search