I'd be interested to hear from some of you that are using adps, or have
tried thoroughly and given up on them. I currently use pass-through
queries and stored procedures with Access 2003 mdbs to sql server 2005.
That works pretty ok once you get the hang of it all. I've always
regarded projects as being off the table since MS clearly shifted away
from adp years ago.

I suppose there are two crucial aspects to my question.

First, for current development, is adp solid enough to use for complex
projects; is development notably faster than with odbc and pass-through
queries; are there performance gains?

Second, what is the sense about adp in the next version of Access, or
preferably a replacement that's even better for working with robust
backend rmdbs. I have not read anything about MS' view of Access to SQL
Server going forward, which is pretty insane.

In article <(E-Mail Removed)>,
(E-Mail Removed) says...
> I'd be interested to hear from some of you that are using adps, or have
> tried thoroughly and given up on them. I currently use pass-through
> queries and stored procedures with Access 2003 mdbs to sql server 2005.
> That works pretty ok once you get the hang of it all. I've always
> regarded projects as being off the table since MS clearly shifted away
> from adp years ago.
>
> I suppose there are two crucial aspects to my question.
>
> First, for current development, is adp solid enough to use for complex
> projects; is development notably faster than with odbc and pass-through
> queries; are there performance gains?
>
> Second, what is the sense about adp in the next version of Access, or
> preferably a replacement that's even better for working with robust
> backend rmdbs. I have not read anything about MS' view of Access to SQL
> Server going forward, which is pretty insane.
>
Not exactly a rousing response in the adp newsgroup <g>. So I'm going to
post this reply to the odbc newgroup and forms coding to see if I can
get some input.

> First, for current development, is adp solid enough to use for complex
> projects; is development notably faster than with odbc and pass-through
> queries; are there performance gains?

I haven't tried ADP with 2007, but I've never had any insurmountable
problems with ADP, and the fact that all tables, views, functions, etc. show
up instantly without any intervention on my part has been a HUGE time-saver
for me. That said, I know that others have had more significant
problems...perhaps it's a question of what design-style you prefer. For
example, I never use stored procedures or table-valued functions as the
record source for a form; it's always tables, views, and dynamic SQL.

I'm unclear on whether there are any performance gains, as it was never a
big concern in my environment. I believe 2007 was optimized with
ODBC/pass-through queries in mind, but I'm not entirely certain of that, and
even if it was, that doesn't really give a clear indicator of whether it's
actually BETTER. I know ADP was side-lined because there are better data
access methods out there; but don't have a "ranking" of them handy.

> Second, what is the sense about adp in the next version of Access, or
> preferably a replacement that's even better for working with robust
> backend rmdbs. I have not read anything about MS' view of Access to SQL
> Server going forward, which is pretty insane.

MS has definitely NOT been forthcoming about this issue. A lot has been
made over a few small, unclear statements that seem to indicate that ADPs
are being put on the back-burner, and MS' actions thus far would seem to
back that up. Other than SQL Server 2005 compatibility, no significant
enhancements have been made to ADPs since Access XP as far as I'm aware.

In terms of a replacement, MS would have you think that .NET is *always* the
way to go for everything, but that view is highly suspect, as they seem to
be trying to push .NET down everyone's throats, even in view of some fairly
clear cases where it would be undesirable. That said, .NET does have some
fairly powerful development tools, but at least the managed code side of
things still significantly under-performs compared to unmanaged code (C++)
or the equivalent swept-under-the-rug VB6/VBA code.

So there's my views and my experience. I don't claim to be fully
up-to-date, but considering the responses so far, I thought I should at
least chime in.



Rob

Within our company...
I currently develop in 2007 but save and distribute the .adp front ends
as 2000 .adp files. Our company has many users spread out over the WAN
using anything from 2k to 2007. Everything works well. speed and
performance
are there for the apps we have out there. I use all 2005 SQL Server
instances, with
the exception of one SQL Server 7.0 machine. I use stored procs for every
form and
sub form, typically passing the input params from the on open event
or a button, or tab control index number. Web views for users not on our
WAN
and not having VPN access, interact via web views. It works out very well
for us.

I see negative posts all the time for .adp solutions.
So far I've not come across anything that can't be accomplished using them.
Additionally, the speed across the WAN or VPN connection is very good as
long
as you write effecient stored procedures and the table indexes are where
they need to be.

...just my humble opinion.
hth,
...bob

"amos" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'd be interested to hear from some of you that are using adps, or have
> tried thoroughly and given up on them. I currently use pass-through
> queries and stored procedures with Access 2003 mdbs to sql server 2005.
> That works pretty ok once you get the hang of it all. I've always
> regarded projects as being off the table since MS clearly shifted away
> from adp years ago.
>
> I suppose there are two crucial aspects to my question.
>
> First, for current development, is adp solid enough to use for complex
> projects; is development notably faster than with odbc and pass-through
> queries; are there performance gains?
>
> Second, what is the sense about adp in the next version of Access, or
> preferably a replacement that's even better for working with robust
> backend rmdbs. I have not read anything about MS' view of Access to SQL
> Server going forward, which is pretty insane.

For your first question, you should try by yourself. This way, you'll
reach your own conclusion adapted to your own needs and capabilities and you
won't get mixed by any religious issue.

For your second question, it looks clear to me (but others might disagree)
that in MS's eyes, the future for any high-end development lays in .NET
technologies; so don't expect any breakthrough to be added to ADP or to
MDB/ACCDB with ODBC linked tables and passthrough queries soon.

Personally, I would be surprised if ADP was to disappear entirely in the
next two or three versions of Access. However, what we could see could be
the integration of ADP directly into the ACCDB format in a next version. If
you take a look at it, there's really no need to have a separate format
between ADP and MDB or ACCDB files. If ADP ever get integrated into the
ACCDB file format, it's unclear if MS will take the time to correct any of
its current deficiencies or leave it as it is.

As for a performance comparison, the main job is done by SQL-Server; to
there is really no point in comparing ADP with MDB. Any difference of
performance between ODBC and ADO when accessing a SQL-Server backend is
probably more immaterial than anything else.

The difference with ADP is not a performance issues when working against
views but the fact that you can have your resultsets (or recordsets) who are
based on stored procedures (SP) to be updatable while in MDB, they're
read-only because you must a passthrough queries for querying these. The
integration between SP and sub-reports is also much better with ADP than
with MDB. If you don't use SP, I don't see any real difference between MDB
and ADP; however, I don't understand people telling that they are using
SQL-Server at its best but saying at the same time that they don't use any
SP at all.

Personally, I've never been able to extract a good performance from any
SQL-Server database without relying on stored procedure. I suppose that
everything resides on the way you're defining the word "performance".

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"amos" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'd be interested to hear from some of you that are using adps, or have
> tried thoroughly and given up on them. I currently use pass-through
> queries and stored procedures with Access 2003 mdbs to sql server 2005.
> That works pretty ok once you get the hang of it all. I've always
> regarded projects as being off the table since MS clearly shifted away
> from adp years ago.
>
> I suppose there are two crucial aspects to my question.
>
> First, for current development, is adp solid enough to use for complex
> projects; is development notably faster than with odbc and pass-through
> queries; are there performance gains?
>
> Second, what is the sense about adp in the next version of Access, or
> preferably a replacement that's even better for working with robust
> backend rmdbs. I have not read anything about MS' view of Access to SQL
> Server going forward, which is pretty insane.

In article <(E-Mail Removed)>, "Sylvain Lafontaine"
<sylvain aei ca (fill the blanks, no spam please)> says...
> For your first question, you should try by yourself. This way, you'll
> reach your own conclusion adapted to your own needs and capabilities and you
> won't get mixed by any religious issue.
>
> For your second question, it looks clear to me (but others might disagree)
> that in MS's eyes, the future for any high-end development lays in .NET
> technologies; so don't expect any breakthrough to be added to ADP or to
> MDB/ACCDB with ODBC linked tables and passthrough queries soon.
>
> Personally, I would be surprised if ADP was to disappear entirely in the
> next two or three versions of Access. However, what we could see could be
> the integration of ADP directly into the ACCDB format in a next version. If
> you take a look at it, there's really no need to have a separate format
> between ADP and MDB or ACCDB files. If ADP ever get integrated into the
> ACCDB file format, it's unclear if MS will take the time to correct any of
> its current deficiencies or leave it as it is.
>
> As for a performance comparison, the main job is done by SQL-Server; to
> there is really no point in comparing ADP with MDB. Any difference of
> performance between ODBC and ADO when accessing a SQL-Server backend is
> probably more immaterial than anything else.
>
> The difference with ADP is not a performance issues when working against
> views but the fact that you can have your resultsets (or recordsets) who are
> based on stored procedures (SP) to be updatable while in MDB, they're
> read-only because you must a passthrough queries for querying these. The
> integration between SP and sub-reports is also much better with ADP than
> with MDB. If you don't use SP, I don't see any real difference between MDB
> and ADP; however, I don't understand people telling that they are using
> SQL-Server at its best but saying at the same time that they don't use any
> SP at all.
>
> Personally, I've never been able to extract a good performance from any
> SQL-Server database without relying on stored procedure. I suppose that
> everything resides on the way you're defining the word "performance".
>
>
I appreciate the responses. ADP looks viable, after some additional
testing and eval.

One question, from what I've read, the Access 2007 adp still uses the
old encryption, not the new and superior encryption of the accdb format?

Yes, Access ADP 2007 use the same old format as 2003 but don't forget that
you data resides on the SQL-Server and that by using stored procedure, you
can have a better control over access to the data than by using ODBC linked
tables. However, this second feature (protection of access to the data) is
somewhat limited even in ADP so if this protection is very important to you,
you should use .NET.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"amos" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> One question, from what I've read, the Access 2007 adp still uses the
> old encryption, not the new and superior encryption of the accdb format?

Possibly I didn't make myself clear. It's not the data side that I was
asking about in encrypting, though that's certainly worthwhile. I meant
mde vs accde vs ade. I know accde is better than mde and old ade. The
new ade...is the same as the old ade?

What'a a web view? Is that a sql server reporting engine thing?