Can a case be made for Access 2007 projects?

[amos]

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.

 

[amos]

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.

 

[Robert Morley]

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

 

[Bob]

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

 

[Sylvain Lafontaine]

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".

 

[amos]

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.

 

[amos]

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?

 

[Sylvain Lafontaine]

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.

 

[amos]

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?

 

[amos]

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

 

[Robert Morley]

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.

I'm not sure if that was directed at what I had said, but if it was, my
point was not that I don't use any SPs at all, but that I don't use them as
the underlying recordset for forms and reports. Are they faster than the
equivalent view? Possibly. (Though that remains to be seen, since Access
actually deconstructs record-returning SPs in very odd ways to get at the
underlying tables...and as far as I know, it ignores the SP entirely and
never calls it once Access has decided what tables it needs to access). But
I've heard a lot of reports of problems with ADPs, most of which I
personally have never run into. And most of the problems I've heard of
where a form or report was acting oddly were based on SPs.

That said, I've heard of plenty of people who've had few if any problems,
and I don't take sides on the debate in any way. It just happens that
because the database was originally migrated from Access, all
record-returning queries were set up in Views, so that's what we continued
to use after migration, and where that didn't suffice, dynamic SQL was used.

It's truly irrelevant to me now, however, as I'm no longer working on that
project.



Rob

 

[amos]

Does anyone know what how the sp1 runtime works with adp and ade at this
point? I read that it was not so good with the initial release, but have
not heard much about sp1, if it's significantly better with 2007
projects.

 

[Sylvain Lafontaine]

Yes, it's the same (to my knowledge).

 

[Sylvain Lafontaine]

Most people having problem with ADP are mainly newbies who have not really
plunged into it.

The case between ADP and ODBC linked tables is not clear because on one
side, the technology is pretty much limited (personally, I see ODBC linked
tables only as some kind of kludge) but seems to have a strong support from
MS in the long run and on the other side, the technology is more evolued but
has been stopped at mid-track because MS has made the decision that .NET
should now be used for anything technically challenging that cannot be done
with ODBC linked tables.

Personally, I didn't try to have the last word on this question but I simply
suggested to the original poster that he should take a look by himself if he
really want a clear understand of what's involved when chosing between ADP,
ODBC linked tables or .NET.

That said, even if ADP is now probably a dead-end, I prefer to use it
because I find it much more easy and powerfull to access a SQL-Server
backend then by using ODBC linked tables and passthrough queries.

 

[Robert Morley]

Sylvain Lafontaine wrote:

I see ODBC linked tables only as some kind of kludge)

That said, even if ADP is now probably a dead-end, I prefer to use it
because I find it much more easy and powerfull to access a SQL-Server
backend then by using ODBC linked tables and passthrough queries.

Agreed on both counts. ADP is far less of a chore to work with than
anything else I've tried to this point.


Rob

 

[bcap]

Prior to Access 2007, everything I've done for about the last 5 years was
done using ADP's. Certainly the technology has some horrendous bugs (which
have been there since it's first incarnation and which, clearly, are never
going to be fixed) but it's OK once you've found them all and found
workarounds for them. Anyone who's never found any bugs when working with
ADP's simply isn't trying very hard!

However, with Access 2007 I have reverted to ODBC linked tables, simply
because it is looking increasingly likely that not only will ADP's not be
subjected to any future development or maintenance by MS, but that on some
dark night in the future they will take the technology out the back of the
building, shoot it in the back of the head, and bury it in the same shallow
grave as VB6.

Having said all that, my clients are increasingly demanding web applications
which means that, for me, ASP.Net is the way of the future.

 

[a a r o n _ k e m p f]

so grow a backbone, kid
ADP have a future, for sure.

Have you even seen what is coming in the next version?

Have you heard about SQL Server Data Services?

 

[bcap]

A dickhead writes:


so grow a backbone, kid
ADP have a future, for sure.

Have you even seen what is coming in the next version?

Have you heard about SQL Server Data Services?

 

[amos]

so grow a backbone, kid
ADP have a future, for sure.

Have you even seen what is coming in the next version?
Have you heard about SQL Server Data Services?

SQL Server Data Services do look pretty cool, although it's not clear to
me if Access will have native support for that. I have heard little
about the next version of Access, what can you tell us? Or where can we
read about it?