ADP Ready for Prime Time?

[Neil Ginsberg]

A while back I posted a message re. using an ADP file with a SQL Server back
end as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was that the ADP would be just
as, if not more, problematic; that ADO is far more limited than DAO,
requiring a lot of workarounds; and that it would be better to stay with a
seasoned MDB file than to switch to an ADP.

The database in question was inherited by me from someone else who
implemented things in a less than ideal way, and he, himself, converted it
from an existing database in another application. The database needs to be
revamped, and we are looking to rebuild it from the ground up with
additional functionality. So now the question is not staying with a seasoned
MDB as opposed to a new ADP; but, rather, which would be better for working
with a SQL Server back end, an MDB or ADP file?

Obviously if we go with an MDB file we can reuse many of the DAO routines,
as appropriate. But the main concern here is performance: which one will
provide the better performance, better data access, fewer strange anomalies,
and so on. Development time isn't the main concern.

The database currently relies in part on DAO and in part on stored
procedures. Obviously moving more towards stored procedures would be better,
but there will probably still be a significant amount of code in the front
end.

Any thoughts re. MDB vs. ADP or anything else related to this would be
appreciated.

Thanks,

Neil

[Steve Jorgensen]

So why were we using Access again? Being able to edit in continuous forms is
one way we make user interfaces easier to use, by not making user have to
switch contexts all the time. It's also a way we can use to keep from
duplicating the same controls on multiple forms, this keeping application
complexity down.

If I'm designing an application where you only update one row at a time, I
might as well use a more powerful programming language with weaker bound UI
capabilities.

On Wed, 26 Jan 2005 14:39:49 -0800, <aa*********@ho tmail.com> wrote:

views are a pain to update

so basically use base tables

and get this

1) only load a single record at a time and this should cut down on
complexity
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:n8******* *************** **********@4ax. com...

On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.c om wrote:

>Steve Jorgensen wrote:
>> It's not just the develompment time, it's the unpredictabilit y of
>runtime
>> behavior.
>[snip]
>> It's easier to get higher reliability and good performance with an
>MDB than an
>> ADP - period.
>
>Steve,
>
>What are the issues you're talking about here? Or can you point me to a
>link that talks about some of them (if they've been discussed
>extensively) ? I haven't been around the CDMA 'hood for a while; I know
>there are plenty of people who argue, and I agree with them, that
>learning ADO because it's new doesn't make any sense, but I don't
>remember reading much that talks about ADPs being inherintly more prone
>to falling over at run time, or inherintly requiring more development
>time (other than learning ADO).
>
>This is in no way a troll or a challenge. I've been working on a
>project that uses a MS SQL Server back end and an ADP front for the
>last seven months, and I've run into very few things that I hadn't seen
>in an MDB. Or at least that's my impression--maybe I'm just seeing
>trees, though.

Well, for one thing, ADPs have a habit of making certain multi-table

queries

uneditable. The first version of a query may be editable, but after some
random and seemingly trivial change in the query itself or afger the next
Access service pack or MDAC upgrade, it becomes uneditable again, and the
tweaking process starts all over again. I've finally gotten to the point
where I don't even try to use multi-table queries on bound forms in an

ADP.

Either, I use combo boxes as lookups for data in related tables (uh yeah,
that'll help performance), or have to use separate forms for

adding/editing

which is more clicks and work hassle for the user as well as more

duplication

of form design elements.

For another thing, there is a bug that was supposedly fixed, but actually
isn't in which sometimes an ORDER BY clause in a subform or subreport

causes

an error. To fix it, it is necessary to remove the table name qualifier

from

the expression in the ORDER BY clause. Of course, the query builder

always

puts the table qualifiers on, and it's never obvious what's broken without
some digging.

In ADPs, any time you do anything slightly out of the ordinary in a
server-side query, the ADP gets confused. For instance, in my MDBs,

sometimes

I like to have a view that references a table in another database on the
server. I do this, for instance, to put tables frequently used to hold
temporary data in a database with TRUNCATE LOG ON CHECKPOINT so my backups
aren't filled mostly with irrelevant adding and deleting of temporary

data.

When you try to bind an ADP to this, it gets really confused beacause ADO

is

smart enough to try to look at the underlying tables, but not smart enough

to

look at them in another database on the server.

The ADP looking behind your stored procedures and views also makes it hard

to

do things that work fine in an MDB such as using a view for security where

the

user has permissions granted via the view that they don't haveon the

tables

directly. If you know the issue and are -very- careful, you can work

around

this. This is in the category of ADPs trying to think for you and thus

making

it very hard to use what should be typical rule enforcement on the

back-end.

I can't remember all the other issues right now, but I remember that there

are

several more.

[Steve Jorgensen]

So basically, ADPs are a way to do what MDBs can already do pretty well, only
with more things that don't work or are very flakey. OK, the ability to edit
server-side objects through the Access UI is great, but you can use an ADP for
server-side object development, and use the MDB for all the production
front-end forms, reports, and code.

On Wed, 26 Jan 2005 14:38:01 -0800, <aa*********@ho tmail.com> wrote:

ADPs are ready for primetime.

but they're difficult to implement, especially things like this:

1) forms against views is difficult/unpredictable
2) stored procs bind well to form, but it isn't very well documented
3) sql server doesn't have a crosstab query (yet)

hth
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:sf******* *************** **********@4ax. com...

On Tue, 18 Jan 2005 07:38:50 GMT, "Neil Ginsberg" <nr*@nrgconsult .com>

wrote:

>A while back I posted a message re. using an ADP file with a SQL Serverback >end as opposed to MDB file with linked tables, thinking that the ADP file
>would be less problematic. The input I got was that the ADP would be just
>as, if not more, problematic; that ADO is far more limited than DAO,
>requiring a lot of workarounds; and that it would be better to stay witha >seasoned MDB file than to switch to an ADP.
>
>The database in question was inherited by me from someone else who
>implemented things in a less than ideal way, and he, himself, convertedit >from an existing database in another application. The database needs tobe >revamped, and we are looking to rebuild it from the ground up with
>additional functionality. So now the question is not staying with aseasoned >MDB as opposed to a new ADP; but, rather, which would be better forworking >with a SQL Server back end, an MDB or ADP file?
>
>Obviously if we go with an MDB file we can reuse many of the DAOroutines, >as appropriate. But the main concern here is performance: which one will
>provide the better performance, better data access, fewer strangeanomalies, >and so on. Development time isn't the main concern.

It's not just the develompment time, it's the unpredictabilit y of runtime
behavior. It's just fastly harder to deliver verifiably high quality code
with ADPs. They're too much of a hodge podge of hastily patched together
kludges. ADPs and ADO also introduce at least as many performance

bottlenecks

as performance improvments, and well-designed MDB client/server apps have

been

known to perform really well.

>The database currently relies in part on DAO and in part on stored
>procedures. Obviously moving more towards stored procedures would bebetter, >but there will probably still be a significant amount of code in thefront >end.

That's obvious, but not necessarily correct. Access works quite well

using

forms bound to DAO recordsets on linked tables. if you deviate from that,

you

might as well scrap Access altogether and implement custom GUIs in

something

like C# or Java - of course, that might really be your best answer if time

is

not an issue.

With respect to stored procedures performing universally better than

Access

queries of linked tables, it's simply not the case. In most cases, an

Access

query generates a prepared statement on the server side which functions

much

like a stored procedure would. When the server sees the same prepared
statement signature again later on, it will realize it can use the same

query

plan it computed for the statement previously - just like a stored

procedurem

only without the headaches of being unable to update via an Access bound

form.

>Any thoughts re. MDB vs. ADP or anything else related to this would be
>appreciated.

It's easier to get higher reliability and good performance with an MDB

than an

ADP - period. If time is not an issue, scrap Access altogether and write
hand-optimized code in a "real" programming language.

[Steve Jorgensen]

On Wed, 26 Jan 2005 14:41:56 -0800, <aa*********@ho tmail.com> wrote:

i believe that i am the person that made the depecrated statement... not
sure

ADP rocks.

Microsoft isn't taking this platform seriously; and they need to.

ADPs might rock if they did and fixed what's wrong with ADPs today, but MS
won't, so ADPs don't.

[Lyle Fairfield]

<aa*********@ho tmail.com> wrote in news:#v0xzf$AFH A.2392
@TK2MSFTNGP14.p hx.gbl:

1) only load a single record at a time and this should cut down on
complexity

Yes, I agree. ADPs are way better when one does not use the parts which don't
work consistently well. Throw out forms and reports and you have a
masterpiece.
This strategy also cuts down time spent in learning to almost nothing.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Larry Linson]

"Lyle Fairfield" wrote

Yes, I agree. ADPs are way better
when one does not use the parts
which don't work consistently well.
Throw out forms and reports and
you have a masterpiece. This strategy
also cuts down time spent in learning
to almost nothing.

ROFL!

[Tim Ferguson]

Lyle Fairfield <Lo******@FFDBA .Com> wrote in
news:Xn******** ***********@216 .221.81.119:

Yes, I agree. ADPs are way better when one does not use the parts
which don't work consistently well. Throw out forms and reports and
you have a masterpiece.

What is left?

Tim F