[AccessD] Going CRUD way...

Since you wrote this to the AccessD list, I'll begin there with my response.
I'm currently doing an ADP for a riding stable (which will soon be for sale,
so if any listers have any friends with riding stables... LOL). The general
approach in it is forms bound to views. (Nobody but me gets to talk directly
to tables.) Some of the views use table-UDFs to simplify joins. All of the
combo-boxes and list-boxes use named queries (views) to retrieve their
contents. This app is simple, so there's not much need for complicated
sprocs, except here and there. These fall mainly in the Reports realm; a
dialog box opens, requests parameters such as "Horse", Start Date and End
Date, then invokes the report which invokes the sproc and passes the
parameters, so you end up with a cross-tab report showing the particular
horse's activities ( group lesson, private lesson, sem-private lesson,
injury day, etc.) during the date range.

In the larger scheme of things, I use ERwin. Its code-gen capabilities are
totally wonderful. It has a template language built-in which will generate
your CRUD code automatically, and even give you a choice between returning a
rowset and a set out OUTPUT parameters. I hadn't realized the benefit of the
latter strategy until I worked on a large project with my friend Dejan
Sunderic (who wrote a great book about SQL 2005). That type of sproc is
useful only when you want exactly one record back, but if you're searching
millions of rows, it's demonstrably faster than returning a rowset. You
don't even need a timer to note the difference.

In Access, there are significant advantages to using views as the data
source, IMO, not the least of which is how easily subforms behave. Access
does the dirty work for you. You just create a subform based on a view,
plonk it onto a master form, and Access handles the plumbing. It couldn't be
easier, and in addition you insulate the actual tables. Suppose that your
app contains a form that only selected people (let's call them Managers)
ought to see. So in SQL you create a Managers role and grant access to the
view(s) in question. Then even if you forget to program around it in your
Access app, it's ok -- no one but managers will be able to run that report.
The message they will receive isn't elegant, but the data is safe.

I'm not an ERwin expert but I have worked with one or two. At one point, I
asked my friend and colleague Andrei Pascal whether we could customize the
template to place what ERwin calls a description into the Extended
Properties code. It took Andrei about 5 minutes to modify the template so it
did this. That's two "hats off" -- one to the template language and one to
Andrei. The template language is pretty much beyond my feeble intellect, but
Andrei just whipped out a tiny little loop that walked every table and added
an extended property to every table for every column that had a Description,
and poof! All done.

I used to hate ERwin and I much preferred PowerDesigner and Dezign (whose
interface is pretty much a clone of PowerDesigner, although it lacks lots of
the PD power). I was dragged kicking and screaming into using ERwin, but
have since grown into an enthusiast, not least because generating CRUD and
even customized CRUD is a one-click operation.

Arthur

On 7/30/07, Shamil Salakhetdinov <shamil at users.mns.ru> wrote:
>
> Hi All,
>
> It looks like we haven't yet have here CRUD vs. (mainly) dynamic manually
> written SQL vs. metadata-driven application (frameworks) development
> debate?
> Or did I miss it?
>
> Anyway my question is what do you prefer to use when developing
> applications
> against MS SQL backend:?
>
> - 1) CRUD SPs based approach to work with base tables + custom SPs(views,
> UDFs,...) to implement custom functionality - and SPs only "visible to
> outer
> world"?
>
> - 2) dynamic SQL - DAO, ADO, ADO.NET (mainly) manually written etc. with
> or
> without SPs (views, UDFs,...)?
>
> - 3) metadata driven (flexible) dynamic SQL approach?
>
> - 4) you do not use not the first not the second not the third approach -
> you do use a "mixture" of them IOW you just write code to implement custom
> functionality and whatever approach to use in every certain case you
> usually
> decide as you go...
>
> - 5) something else (please add other useful approached I missed to
> mention
> here)...
>
> Thank you.
>
>
> --
> Shamil
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>