Is it just me or are ADP's very very cool!

[Jon Furman]

I've been seriously working with ADPs for the past few months. My background
has been in Access (Jet and as a client to SQL server and DB2) and SQL
server. I initially had some trouble learning how think about ADPs, but now
I've climbed the learning curve I'm really liking them. I've been using
Access 2002 for this and it's pretty smooth excpet for a few gotchas here
and there. Just curious if anyone else is finding data projects as useful
and productive as I am. Also curious about any improvements in Access 2003
that may exist(I haven't tried 2003 yet.)

Jon

 

[Steve Jorgensen]

I found them really cool too until I tried doing a real project with them.
now, I still think they're really cool, but only as a tool for building a
database schemaa and simple stored procedures and views, and for trivial
application prototyping. Go much beyond that, and the gotchas will getcha,
and the work-arounds have gotchas, etc.

Most of the truly useful things it seems like an ADP should allow you to do,
like binding a form to a disconnected BatchOptimistic recordset, just don't
work - before they just crash Access.

 

[Sylvain Lafontaine]

The commercial release of Access 2003 ADP has the same bits as Access 2002 -
same capabilities, same bugs. However, I have found that with SP2 for
Windows XP came some new bugs for Access 2002 that I don't have found their
counterparts in Access 2003; so for systems with SP2, it's probably a good
idea to use Access 2003 instead.

You can make great things with ADPs, especially if you are willing to deal
with the bugs here and there. I have currently two successful projects
which have been done with them. However they are based on Recordsets and,
as such, are no longer part of the Microsoft database strategy; which is to
use Datasets under the .NET Framework. This is why you will find the same
bits for ADPs under Access 2003 and also why they won't probably climb their
way up to the next version of Access. (By the way, driving recordsets into
oblivion is not a bad idea, either, but before you can use their
replacement - the datasets - you will have to flexe your muscles against the
step learning curve of the .NET framework.)

S. L.

 

[Jon Furman]

Very interesting...what you've been doing mirrors what I've been doing with
them. I've been using it mostly as a development and testing environment.
The ADP environment seems especially good for designing the db and the sp's
that are meant to be called from a client application (potentially not
access, the stuff I'm working on now is being called from ASP .Net). It's
very easy to see the database from both the relational perspective and the
application perspective from within the ADP. If you have question about how
to present data to a client app it's very easy to set something up and test
it with a form and see if it really makes sense. I'd say right now I'm
spending about 70% of my SQL Server development time in the Access/ADP
environment and 30% in EM/QA. I also feel like with some further
enhancements to Access that ration would go even higher. Two immediate
things that could be better are:
1. The handling of functions..Access seems to not treat them that
same as sp's and I've had to go to QA to manually fix them a few times.
2. Table properties, I use this alot in jet devlopment and I'd like
to add the properties in ADP as well.

What other gotchas have you run into? I'd love to be forewarned before I
find out about them the hard way myself.


Jon

 

[Jon Furman]

Hello Sylvian! Good to hear from you again. I hear you about .Net, we have
been using it heavily. But my personal experience with it is very abstract
(meaning I've read books about it, installed VS .NET, built a few sample
apps and nothing else). I always get stuck with the database work so I wind
up having little time for the sexy stuff. The current project that I'm
using ADP as a db development environment for uses .NET exclusivley as the
real user interface. I don't work on the interface....at least not yet...but
the developers that do seem to be having a very easy time working with my
ADP developed database. I attribute part of that to me being able to test
stuff out from a client point of view easily there before they run into it.

Do you really think that ADP's will be dropped in the next version of
Access? I think that would be shameful as the idea is just too good to let
die. What do you make of this clipping (this is actually from another Access
newsgroup from a while back):

MICROSOFT INVESTING HEAVILY IN ACCESS

Last week at the Access Advisor conference in Las Vegas, Microsoft announced
their plans for
enhancing Access over the next several versions. In his keynote, Richard
McAniff, Corporate Vice
President of Access, Excel and Office Programmability, revealed the future
direction for Access
and their renewed commitment to the roots of making databases easy, along
with SharePoint
integration for web connectivity. With the largest Access development team
since the early days
of Access, Microsoft is refocusing its efforts on making Access the
no-brainer choice for Excel
users who need more power. By simplifying the development of database
applications, information
workers will be empowered to solve more database problems on their own.
Meanwhile, the developer
features will allow the continued creation of professional solutions.
Overall, the investment
Microsoft is making, the change in focus and simpler marketing message for
Office, is quite
tremendous and bodes well for the future of Access.

At FMS we are extremely pleased to see this new focus. Rather than focusing
on SQL Server or
...NET, Microsoft is returning focus to Access' core strength: a rapid
development environment for
users that can be extended by experienced database developers and
programmers. Very few
programmers train to become Access developers, but many developers start
from their use of
Access. The new initiative will result in even more databases that can be
created by information
workers, and gives skilled Access users and developers more opportunities.
While there are
tradeoffs between Access and other, more advanced platforms, making it
easier to create Access
projects will allow organizations to build applications they could not
cost-justify on more
expensive platforms. We at FMS have always believed there are a wide range
of business and
organizational challenges that require database solutions. Some justify
expensive and
sophisticated solutions, while others are best satisfied with the rapid,
cost effective solutions
Access offers.

 

[Sylvain Lafontaine]

Hi,

Well, I'm not in the shoes of Microsoft, so I cannot tell anything sure
about the futur of Access. However, if you take a look at what happened to
VB6; you can be sure at about 99.99999% that ADP and VBA for Access will
follow the same road.

After reading the clipping, we can see that Access will become some sort
of Excel for easy databases: simpler to use for the casual user; which want
something as easy to use as tables in Word but with the possibility of
having a greater number of records. The coding model will be probably
something like VS.NET but with its engine well hidden from the average user
and with a lot of pre-designed templates. However, for the developper,
there will be few, if any, differences from the regular VS.NET.

The fact that MS will invest heavily into the next version is also a
confirmation of the bad news for ADP: if MS was to keep the core of ADP and
doing only a simple facelift to its interface, they wouldn't need to invest
heavily into this next version of the product.

S. L.

 

[Lyle Fairfield]

I've been seriously working with ADPs for the past few months. My
background has been in Access (Jet and as a client to SQL server and
DB2) and SQL server. I initially had some trouble learning how think
about ADPs, but now I've climbed the learning curve I'm really liking
them. I've been using Access 2002 for this and it's pretty smooth excpet
for a few gotchas here and there. Just curious if anyone else is finding
data projects as useful and productive as I am.

Used to love 'em. But now I think binding forms creates serious security
concerns. Application roles address these concerns but they're flakier than
Kelloggs. And if not bind, then why use? Surely no one would argue that
Access's connections or Access's script language are efficient? Color me
VERY disenchanted. Unless $$$$$$$$$$omeone very much want$$$$$$$$$$ an ADP
created by me, they're history for me.

 

[Jon Furman]

All excellant observations, making Access easier as an application can't be
anything but good. I spent years using it in such a way before I started
using it as a development environment. When I was using it as an application
to store and analyze data I was very appreciative of what it could do, so I
hope that whatever they come up with continues and improves that tradition.
As a development environment it was good, although a little hard to learn.
But I still think that the VBA extensibility is and important componant of
it's power, so that I hope that element is significant in Access.Net or
whatever it becomes. I wonder if possibly office will become an
additional/optional part of the framework so that it can be called easily
from .NET apps. Maybe the office suite will ship with some kind of VS lite.
When .Net was first introduced I attended the local MS sponsered grand
unveiling, my first question to the presenter was what does this mean for
Office based development and what changes can we expect and also when will
there be a tight integration between Office and .Net. What he told me was
that since Office is essentially the sample COM based system it would have
to be rewritten entirely in CLR for this to happen, so basically don't hold
your breath. But it sounds like maybe this is now happening.

I don't mind if they rebuild the whole thing to be CLR complaint and in
process streamline the application features, but I will worry if they "dumb"
it down to be a relational notepad. My feeling is that nothting should be
removed from the current product without replacing it with something
obviously improved.

Jon

 

[Jon Furman]

That's funny, the ADP file I'm using now has 40 some forms and only one is
bound and that's just becuase I wanted to see how it would work. I figured
that was just because I'm using the ADP mostly as a SQL IDE and a place to
test stored procedures, but maybe it's lucky for me that it's turned out
that way.

When you say application roles are flakey, I assume you're talking about SQL
server application roles. I haven't had the opportunity to use them, I would
have thought that as being part of the SQL server security system that they
would be solid. Is that not true? Or are they just flakey when used in
conjunction with ADPs?

Jon

 

[Lyle Fairfield]

That's funny, the ADP file I'm using now has 40 some forms and only one
is bound and that's just becuase I wanted to see how it would work. I
figured that was just because I'm using the ADP mostly as a SQL IDE and
a place to test stored procedures, but maybe it's lucky for me that it's
turned out that way.

When you say application roles are flakey, I assume you're talking about
SQL server application roles. I haven't had the opportunity to use them,
I would have thought that as being part of the SQL server security
system that they would be solid. Is that not true? Or are they just
flakey when used in conjunction with ADPs?

Jon

Because an Access ADP uses several connections depending upon what it is
doing (maybe not, because sometimes the choice of connection seems to be
random), and because the nature of these connections is at best unclear,
it's extremely difficult to predict how the connection (whichever one is
"dealt") and the application role will interact. I found that about 50% of
the time I got messages saying the stored procedure, table, view whatever
did not exist. After I programmed around one of these, I expected the
solution to work in what seemed to me to be an identical situation;
sometimes it did but sometimes it didn't.

VBA is an archaic programming language and sometimes its execution is very
slow. Its syntax is clumsy and verbose. Add that to the connection nonsense
and there seems (to me) to be no justification for using this bloated
application except for its wonderful bound forms. These do not work well in
ADPs. Often one struggles to update records based on a view of data
residing on more then one table. There are problems with finding records in
continuous forms on open. There are problems with stroking input pramaters
in reports. Then there are the service pack and dao-jet-ado library
updating problems. Arggggghhh. I'd rather just write asp.net or old
fashioned asp or hta applications. More work at the beginning, but these
things do what you tell them (a very strange occurrence for anything from
MS), they don't break every two weeks and they don't open up vast security
holes (the programmer may do this for them of course, but that's another
issue).

And they're grateful as hell ... oh no ... that's a male teen ager's
fantasy about older women. Maybe I'm wandering a bit here.

 

[Jon Furman]

I'm still chuckling....too funny! I'm not sure why the ADP would establish
more than one connection unless you explicitly told it to. If I hear you
right..Access is taking it upon itself to manage multiple
connections....which is a big OUCH. In my VBA code I've been explicitly
setting my ADO commands to use the projects active connection under the
assumption that there would be only one, but if it's not doing that then my
efforts are futile and unecessary. Not sure if I'll go too much farther with
my ADP work than I have already. Even if all of this is true I still have to
say that ADP is a good SQL server development environment. Guess we'll all
be waiting to see what Access.Net turns out to be. I really hope that they
get it right. Thanks for your comments.

Jon

 

[Larry Linson]

Do you really think that ADP's will be
dropped in the next version of
Access?

No, I think they will be retained for compatibility. I think fixing the
problems or improving them is very low on the priority list.

I think that would be shameful as the
idea is just too good to let die.

What idea? My work with ADP and ADO didn't give me a warm feeling that this
was _better_ or even _would be better_ if only some of the objections you
read about here were to be fixed; I only found it _different_ and not that
much different, other than a few restrictions.

Besides, the successor is already here . . . ADO.NET, which if you look
carefully, is an "entirely different beast", not even built on the same
object model, not even built on OLEDB.

What do you make of this clipping (this
is actually from another Access
newsgroup from a while back):

I make that Microsoft recognizes that Office, including Access, is their
"cash cow". They know they need to do some things to make it not just
competitive (which it already is) but _compelling_, particularly in the
novice and casual end-user markets. And, despite all the drawbacks of its
development features, it is the only Microsoft refuge of the
"Not-Yet-Dot-Net" developer crowd.

Larry Linson
Microsoft Access MVP

 

[Jon Furman]

Thanks for the reply Larry. What I thought was a particularly good idea was
being able to directly do SQL server development from within the Access
environment. I think that it's a blendig of the best of two worlds.

I hear you about the .NET future. I guess we'll just have to see what
happens. Once upon a time good VBA skills allowed you to work in many MS
enviroments (VB, Office...etc..) but now we're all fragmented again. So
really to be effective you've got to maintain the VBA/COM skillset and
advance your .NET skillset. Actually I know some developers that still won't
use anything but C/C++ for windows development. Sheeewww...no rest for us
wicked ones I suppose!


Jon

 

[Sylvain Lafontaine]

With VBA, you can work in many environment but only in a skimpy and
superficial way. For exemple; it's very hard to design a 3-tier environment
with VBA or to access a web service from Office. There is an offering of a
SDK for accessing some web services and SOAP messages from the Office
environment but this is a very limited functionality and doesn't offer much
help for the web server side or the SQL-Server side.

But doing a quick research, it's easy to find many others limitations of the
current VBA/COM offering and they can really hurt you when you have to dealt
with them. For exemple, one my client has paid to have of his reports
publicly available on the internet with DAP pages. Two weeks later after
its installation, the system went done simply because the web site hosting
service has decided to block the SQL-Server 1433 port; for "security"
reasons and telling us that this blocking was now the "norm". (By the way,
what is the need to pay for SQL-Server database access instead of having a
simple MDB file as the backend if you cannot access it from the web?)

The .NET framework is designed to overcome these limitations and in this
environment, the above problem would have been easy to deal with.

S. L.

 

[J. Clay]

OK...Been watching this thread for a while and thought I would offer my
$.02.

For the right project, I really like ADPs. In our case, I had written an
Access app several years ago. It ran stable with a few minor upgrads for
about 3 years and then we converted to SQL and I converted it into and ADP.
It has grown dramatically over the past 3 years and we have a bunch more to
do. It has really made a difference in my development and its usability.

The key is that our system is in house only. There is now links outside of
our firewall. And, the development speed has really helped. I still do
things in EM & QA, but most everything is done in the Access & VBA
environments.

I have invested a lot of time to this app and I would assume others have as
well. I certainly hope that MS keeps the ADP and does do some work to
enhance it. I personally like it.

Jim