Official Status of SQLServer 2005 ADP

[Terry M]

I just started reading this group and disapointed to read that it sounds
like ADPs are being hung out to dry.


I was not impressed by this. ADPs would allow someone with a bunch of Access
programming background (like me) to leverage that knowledge to work with a
SQL Server backend, without learning a new environment. If you program in
Access you relize there is no faster way to built an application.


If it is true that going forward less and less functionality, it could be
hard to justify new development with ADP

Those that have implemented (or plan to) these solutions may have an
interesting support problem going forward.

However everything I have read is not 'Official' does anyone know the
status of this, or can you point me in the right direction.

Thanks



Terry Mc

 

[dbahooker]

Terry

MS will come out with a patch on this; from what i've heard.

I mean-- real simple math; microsoft-- server side crosstab query
wizard

come on
plz plz plz

 

[Pat Hartman\(MVP\)]

The Access MDB with linked tables is a superior tool to access SQL Server,
Oracle, DB2, etc. backends. The ADP was extremely limited in its
functionality which is why it never gained wide acceptance. It could only
link to SQL Server tables. It couldn't even link to Access/Jet tables!!!
It also required significantly different techniques to develop applications
such that about the only things an MDB and an ADP have in common is they are
both Access and they both use VBA.

My understanding at the moment is that future development of the ADP will be
limited or non-existent. In fact, with Office 12 which will be released
some time near the end of 2006, the ADP will not be enhanced to allow it to
create tables in SQL Server 2005. It will be able to access tables from
2005 but not create them. So, if you need to use the ADP to create
databases and manage them, you have to stick with SQL Server 2000.

Microsoft plans to continue support for existing ADP but will no longer
support creating them. I don't know what will happen with the upsizing
wizard. I hope it stays so it can be used to create the SQL Server
database. Otherwise, you would need to create the database from scratch or
use a tool such as erWin to translate the Access/Jet schema to SQL Server,
Oracle, DB2, etc.

 

[AlexT]

My understanding at the moment is that future development

of the ADP will be limited or non-existent

Do you have any public source for this ?

Thanks

--alexT

 

[Sylvain Lafontaine]

Excerpt for the integration of the new GUI, the bits for ADP2003 were
exactly the same as for ADP2000 and all of the known bugs were still there.
Since then, practically none of these bugs seems to have been corrupted in
the subsequent service packs for Office 2002 or 2003.

In fact, it's the contrary: the latest service pack (SR2) for Windows XP
seems to have brought new bugs for ADP2002 (but none for ADP2003) while
Win2003 seems to offer new troubles for both ADP 2002 and 2003.

It's fairly obvious that much of the new high-level developments in the
database domains will be done for the .NET Framework only; while the next
version of Access will probably bring new features for entry level design of
databases (better wizards/designers for beginners, etc.)

 

[Terry M]

I guess what we have been looking for is a way to use Access as a front end
for SQL Server.

We have an old Access multiuser app with an MDB backend that has a hundred
or so tables, that is nearing the end of its life.
Performance is a big issue for us. This app is for analyitical lab results
and has reports based on some very complicated queries bases on a dozen or
more tables. Plus many sub-tables with there own multiple table queries.

I though that using ADP would allow me to use SQL views, SP's, etc to speed
this up considerably by puting the query processing load onto our SQL
Server, and carry on using Access like I aways had. Plus I need to draw
some data from a second SQL Server database.

If you think that linked SQL Server tables with a Access front end could
work as well or better I would feel better. Does this mean I would use ODBC
and write passthrough Queries?

If you can point me in the right direction I would appreciate it. With the
exception of an A95 app I wrote years ago (that did some ODBC passthrough
queries to an IBM AS400 database) I have been working with the standard
split FE/BE mdb model.

I liked the idea of being able to do everything from the ADP (create tables,
view, SP) but that would not be the end of the world.

At the end of the day I would like to create a frontend with the ease of
Access and let SQL Server do the heavy lifting.

Thanks

 

[Terry M]

As we started out looking at writing a replacement for the old Access app
that basically runs our small business, I wrestled with the idea of writing
it with Access (maybe ADP) or VB.Net.

Access I know and love, .NET I have played with, but that's about all. I
had decided on using Access because I knew we could get 80% of the
functionality we wanted in no time, plus programing is not my primary job in
our company. We planed on doing some more advanced stuff with .NET (outside
of the main app) once the nuts and bolts were taken care of.
I am rethinking this now. I imagine we will always have Access around for
adhoc query and reporting, plus its integrating into sharepoint better and
better.

It might be time for me to bite the bullet and learn .Net, but I think I
knew that..

Terry Mc

 

[Sylvain Lafontaine]

If you still want to keep Access as the frontend while having SQL-Server as
the backend and running complicated queries, then your only practical
solution might be to use ADP.

With MDB and Linked tables, the only ways of accelerating things are the use
of Views and the cumbersome use of SQL passthrough queries. However, you
will have fun to code forms based on pass-through queries under MDB. The
support of ADP for SP is bad and full of bugs, but never as bad as the one
offered by MDB.

MDB with linked tables (and Views) is a good solution because it's simple to
use for upsizing an already existing application, when there is no need to
run complicated procedures on the SQL-Server side.

 

[Douglas J. Steele]

Do you have any public source for this ?

The best source for public information about Access 12 is Erik Rucker's
(Group Program Manager for Access) blog at http://blogs.msdn.com/access/

However, the only real thing said there about ADPs is "The ADP architecture
is conceptually unchanged between Access 2003 and Access12, which means that
the features continue to work in essentially the same way they did. We
continue to believe that SQL Server makes a great store for Access data and
that building the UI either through linked tables or ADPs will continue to
work well."

 

[Pat Hartman\(MVP\)]

Sorry Sylvain , that's a lot of misinformation that you have posted.

As far back as Access 97 (and probably earlier), Jet has made every attempt
to "pass through" every Access query against a linked ODBC datasource.
There are limitations of course. For example, there is no way for SQL
Server/DB2/Oracle/etc. to process your user defined functions or VBA
functions that do not have SQL equivalents. There was only one case where I
actually had to write a pass-through query to obtain better results and that
was a query that deleted all the rows in a table. Take a look at the
knowledge base articles on client/server optimization for Access. Also,
take a look at the "Jet programmer's guide" for detail information regarding
how Jet handles ODBC linked tables. The book is out of print but you may
find a copy in your library or available from a used book seller. I got one
for $5 from Amazon.Com last year.

Most of my applications use Access front ends to a variety of ODBC back
ends - DB2, Oracle, and SQL Server among others. In all cases, the
applications use linked table and stored querydefs with parameters as the
RecordSources for forms. The one thing you must avoid to make effective use
of an ODBC back end is the common Access practice of basing forms directly
on tables or on queries with no criteria. It is imperative that your forms
be based on queries with selection criteria in order to limit the number of
rows returned.

The advantage of having an ODBC back end is that you can create triggers
which will allow you to offload certain business rule processing that Access
with Jet tables can only support via form events.

I have never found it necessary to create stored procedures except in some
complicated reporting situations. I would certainly never use a stored
procedure or pass through query as the RecordSource for a form. They are
not updatable and that would force you to use an unbound form. If you are
going to go through the effort of using unbound forms, you might as well be
writing in VB.Net or C++. Bound forms and reports are the biggest advantage
of working in Access.

Clearly a stored procedure would be faster than a query "passed through" by
Jet whether the query was defined as a pass-through query or not. The
difference is that the stored procedure is bound and the pass through query
is not. Bound in this instance means that the query has been processed by
the query analyzer and the best access path has been determined and stored
for future use. This eliminates some overhead as a query starts. The
situation is very much the same as the difference between using stored
querydefs and using SQL strings to access Jet tables. In the case of the
querydefs, Jet "binds" the query when it is saved and that eliminates work
that SQL strings have to go through at run time - every time they are
executed. If you use querydefs for your queries against linked ODBC tables
rather than SQL strings, Jet processes the query when it is saved and
"remembers" that it needs to pass it through and exactly how that needs to
be done. So, even with ODBC tables, a stored querydef will be ever so
slightly faster than an SQL string.

 

[Sylvain Lafontaine]

I'm sorry, but I'm unable to see anything about some kind of misinformation
that I would have posted from reading your answer.

In my previous post, I have said that the support for SQL passthrough
queries under MDB was bad and worst than the one offered by ADP while you
have said, in your answer, that in the case of a MDB file these passthrough
queries were read-only; which make mandatory the use of unbound forms in
these cases. I'm sorry to say that but I don't see any tangible difference
between these two opinions.

I don't know if the queries needed by the original poster (OP) are really
complicated and cannot be done otherwise or if they could be replaced with
simple views or whatever else as I leave this analyse to the OP himself.
Similarly, I have the same thinking about your work: you make your own
decisions and if you don't need or don't want to use SP, then I'm glad for
you.

However and beside the fact that you don't use SP yourself, surely you
don't expect me (and others) to limit myself and my work to your own
knowledge/needs/decisions/line of work?

 

[Robert Morley]

I have to agree with Sylvain, here. There was no "misinformation" in his
post, simply a different approach to doing things. There are certainly some
very good arguments, at least in some instances, for using Stored Procedures
and/or Functions to create recordsets instead of using Views themselves.
I've even known some advocates of an SP-only approach, ignoring Views
altogether.

But more importantly, going back to the poster's original request, all I've
seen about a lack of support for ADP's in future versions of Access has come
from MVP's in newsgroups. Not one of you is actually on the Access
development team, and so far, the best reference we have is a newsgroup
blog, which neither confirms or denies the supposed lack of support for
ADP's. If you're going to continue posting this "information", then perhaps
you should post a source for it, lest it too be deemed "misinformation".



Rob

 

[Brendan Reynolds]

There are certainly some
very good arguments, at least in some instances, for using Stored
Procedures
and/or Functions to create recordsets instead of using Views themselves.
I've even known some advocates of an SP-only approach, ignoring Views
altogether.

<snip>

If I may be forgiven for going off on something of a tangent here ...

Given an environment that supports both views and stored procedures, just
what *is* the purpose of views, anyhow?

I ask because most of my work with SQL Server has been via ASP.NET rather
than Access, and in that environment I've yet to find a use for a view.

 

[Douglas J. Steele]

<snip>

If I may be forgiven for going off on something of a tangent here ...

Given an environment that supports both views and stored procedures, just
what *is* the purpose of views, anyhow?

I ask because most of my work with SQL Server has been via ASP.NET rather
than Access, and in that environment I've yet to find a use for a view.

Views allow you to join tables together, just like queries in Access. You
can create views that denormalize your data for presentation purposes.

 

[Sylvain Lafontaine]

This is the problem here: on one side, we don't have any clear information
from MS about the future of ADP and all we have are very little pieces of
information and some relative conclusions that can be drawn from the past.
On the other side, the OP has made a request about a decision he has to
take. Obviously, the best scenario would have be a full disclosure of
information from MS but we don't have it and the fact that we don't have it
doesn't discharge the OP from the responsability that he still have a
decision to take.

I don't know what will be his decision (to wait one more year (or more if
his company have a policy of waiting until the release of SP2 so that the
major bugs have been iron out) until Office 12 is out and then take his
final decision; close his eyes and jump ow with ADP, whatever the real
consequences will be; remains with Jet and linked tables or remains with Jet
but go with SQL passthrough queries and unbound forms; take a look at .NET;
etc.) and I don't care because this is his responsability only. All we can
do is to provide him with the little pieces of information that we have at
this moment and let him go making his own decision. Hopefully, he will take
a good one but if he don't, then it's not our business.

Your point about the requirement of posting a source is interesting but if
you read my previous posts, you will see that this is exactly what I've
always said: that we don't have any specific information about that subject
from MS and that all we can do is to draw conclusions from other pieces of
evidence. For those people who can take the luxury of waiting, these
conclusions are probably useless but for those others who cannot afford this
luxury, then this is only all we can give to them. These conclusions might
be totally right or totally wrong, I don't know because I don't have ESP and
cannot read the futur or in other minds; all I can say is that this is all
we have at this moment and that the ultimate responsability of making a
decision lays in the hand of the reader, not in my hands.

My "information" is not information; they are only conclusions and they have
always been labelled as such.

 

[Brendan Reynolds]

Views allow you to join tables together, just like queries in Access. You
can create views that denormalize your data for presentation purposes.

I know, Doug, but so do stored procedures. What I meant was, given an
environment that supports both, what can you do with a view that you can
*not* do with a stored procedure?

 

[Sylvain Lafontaine]

The first thing that comes to my mind would be to ramp up the security of
confidential informations; particularly when you need a row level security
not only for your users but also for your programmers. It's not all
compagnies that are willing to accept that the programmer of a SP will have
a full access to any confidential information inside a table and even for
those that might accept this, the use of Views can reduce the risk of an
"accidental" release or writing.

The second thing is about performance: the use of indexed (materialized)
views will boost performance and can only be achieved by using a view (you
cannot simulate that inside a SP).

Similarly, the use of partitioned views over a federated database will also
boost performance in a way that cannot be really duplicated with a SP
because of the lack of optimisation by SQL-Server in the later case. (See
http://vyaskn.tripod.com/what_are_federated_databases.htm for more info on
federated databases).

Finally, even if you don't have security or performance concerns, the use of
Views can simplify the writing of SP in the same way as the use of functions
in general coding. This may be especially when you're not the only one
working on a project. See http://odetocode.com/Articles/299.aspx for an
example.

 

[Brendan Reynolds]

Thanks Sylvain. That's interesting and useful information - I particularly
like the idea of using views to simplify stored procedures.

--
Brendan Reynolds

The first thing that comes to my mind would be to ramp up the security of
confidential informations; particularly when you need a row level security
not only for your users but also for your programmers. It's not all
compagnies that are willing to accept that the programmer of a SP will
have a full access to any confidential information inside a table and even
for those that might accept this, the use of Views can reduce the risk of
an "accidental" release or writing.

The second thing is about performance: the use of indexed (materialized)
views will boost performance and can only be achieved by using a view (you
cannot simulate that inside a SP).

Similarly, the use of partitioned views over a federated database will
also boost performance in a way that cannot be really duplicated with a SP
because of the lack of optimisation by SQL-Server in the later case. (See
http://vyaskn.tripod.com/what_are_federated_databases.htm for more info on
federated databases).

Finally, even if you don't have security or performance concerns, the use
of Views can simplify the writing of SP in the same way as the use of
functions in general coding. This may be especially when you're not the
only one working on a project. See http://odetocode.com/Articles/299.aspx
for an example.

 

[Terry M]

This thread has been interesting.



Clearly things are never black and white so I appreciate all points of view.



From my point of view there are a number of certainties.



First SQLServer 2005 is here and is much different than 2000



We have a purchased integrated business management package that runs on SQL
Server 2000, and when this package can/needs to go to SQL Server 2005 we
will.



Our current LIMS (Lab Information Management System) is based on a very
basic all Jet split FE/BE model and has served us well for 6 years (Ver 5)
but is at the end of its life cycle. We have no plans to make new changes
(other than bugs and bandaids) and we cannot wait until Office 12 (or SP2)
to decide which way to go. We will be using SQL Server as the new backend.



It was my understanding that in a FE/BE Access setup, the Jet in the front
end did the query processing, the BE merely served up the data, and even if
SQL tables where linked to Access FE, the jet FE still did the work.



If I wanted something else for performance reasons then my options were
either ADP (so I could easily work with SQL Server Views or SP's), or
Pass-through queries.

Otherwise I would be working with disconnected recordsets (or unbound forms)
so I might as well go .Net.



At the day, if the future of ADP is uncertain then it is unlikely I will go
that way. But If I can achieve the same results with Access and a different
method I would sure like to hear about it.



Terry Mc

 

[Robert Morley]

Hi Terry,

I'm sort of pulling a few messages in this thread together, and working on
my own knowledge as well, but summarizing everything, here's how I see it:

Access still lets SQL do most of the hard work in a FE MDB/BE SS2k setup, so
that's probably the best way to go if the uncertain future of ADPs is a
concern. It's not as easy to work with some things like Stored Procedures
and Functions, and you can't do any serious back end design work from the
front end like you could with ADPs, but if you limit yourself to the back
end strictly being a provider of tables & views, and your front end
providing most of the fancier functionality, then MDB with SS2k ODBC links
will get the job done without placing an undue burden on your front end.

That said, it's all too easy to make mistakes when designing an ODBC-based
client/server database such that you end up pulling an entire table down to
the front end to be processed, even though you only need a few rows. The
two biggest "gotchas" that come to mind (and I believe have been mentioned
elsewhere in the thread) are using custom functions in Access that SQL
Server cannot duplicate, and joining a local table with a server-side table,
directly or accidentally (typically through the use of multiple query
levels, where you forget that your base queries are using local/server-side
tables).

Personally, I went with an ADP about a year ago, and haven't looked back
since. But then again, at the time, I didn't have any indication that they
might no longer be supported in future releases, either. For now, we've
moved to an ADP and we'll stick with that unless/until I hear something
absolutely definite from MS in regards to the future of ADPs. If I have to
move back to an MDB-style design in the future, so be it. It means a few
changes, but considering that the original design was a Jet FE/BE, it's
probably not as big of a leap as it could have been. By then, we may have
finished work on the VB middle tier, and may be wanting to migrate to .Net
anyway...who knows?




Best of luck,
Rob