Future of ADP's

[Baz]

Why on earth would you think that your work is about to end? Microsoft
itself has just made a huge commitment to Access with all the new stuff in
Access 2007, it isn't going to disappear anytime soon. As far as I can see
the only aspect of Access which is seriously under threat is ADP's. That
isn't any kind of show-stopper for new applications with server database
engines, though: I have very happily worked with linked tables in the past
and I will continue to do so in future. The main issue is that those of us
who have already built ADP solutions are probably going to have to convert
them to linked tables at some time in the future.

 

[Armando Vargas]

Thanks!

I agree with that. Access may not disapper, but, I feel that is been
downgraded, I use ADPs because, the links with SQL, but I havent tryed the
linking tables with MDB and a SQL. Maybe it would work real fine.

I stop using linking tables, because I develop some applications that conect
throuth the internet, (ADP and SQL) thing that could not be done by a MDB.
(Link tables) I wonder if linking tables in Access2007 and SQL will work
fine, and give the same performance that ADP gave in the past.

All your comments are welcomed.

Thanks,

Armando Vargas.

 

[Baz]

Hi Armando,

The sidelining of ADP's is disappointing, but against that everything else
has been seriously upgraded. MS has made it very clear that it is fully
committed to Access and has made a huge investment in Access 2007.

ODBC linked tables can perform as well as ADP's, but it takes some care.
The flexibility of Access/Jet is such that it is possible to create queries
which exceed the capabilities of ODBC and/or the server, in which case the
queries will run on the client. This can be a performance problem on a LAN,
and this will obviously be greatly magnified on a WAN. The solutions are
not difficult: modify the design, use a passthrough query, build a
server-side view and link to it, and so on.

If I were happy for a given ADP application to be split across a WAN then I
would be happy to build an equivalent linked-tables application in the same
environment.

 

[Armando Vargas]

Thanks Baz!

This is a releif, I was already for something else to learn and then work
with it. Thanks Now I can focus in creating some new application this way.

Thanks, and hope the best for Access and all it folowers.

(Y)
Armando Vargas.

 

[David W. Fenton]

The sidelining of ADP's is disappointing, but against that
everything else has been seriously upgraded.

Not really. The new database format eliminates user-level security
and replication. Those are as important to some developers as ADPs
are to you folks.

 

[Baz]

Not really. The new database format eliminates user-level security
and replication. Those are as important to some developers as ADPs
are to you folks.

Point taken. I've never found a serious use for those things (I only ever
do SQL Server back-ends these days) but then again I know that some people
feel the same about ADP's.

 

[Vayse]

Didn't realise security had changed. Do you mean there are no more groups
and users, or that all security is through groups?

 

[Douglas J. Steele]

No more groups and users, at least not in the Access 2007 format (.accdb or
..accde)

 

[Armando Vargas]

Douglas.

Any comments on how to handle this? using tables to control Form access?

Thanks

Armando V.

 

[Douglas J. Steele]

Someone has come up with an approach, but I'm afraid I don't remember the
details.

You might check out microsoft.public.access.security

 

[David W. Fenton]

Any comments on how to handle this? using tables to control Form
access?

ULS (and replication) still work with MDBs. They are just not
supported for the new file format (which shows the direction MS
wants to take Access in).

 

[DAVID]

ULS (and replication) still work with MDBs. They are just not
supported for the new file format (which shows the direction MS
wants to take Access in).

And also not supported are dao transactions on linked SQL
Server tables.

Also gone are the Access extensions built in Access
(as the original report export utilities and upsizing
utilities were), and VBA extensions built in Access (as
the original wizards were).

Nor AFAIK, are there any MS applications left that
are built in Access.

But the runtime is free. They don't think anyone will
be making money from selling runtime applications...
.... and they don't want to damage Excel or Word by
adding extra database menu items.

(david)

 

[Sylvain Lafontaine]

There are no more any future for ADP because this technology doesn't provide
any real big advantage over using linked tables in comparaison of what's
really needed to fully access the power of SQL-Server. It's like attaching
a trailer to a car when what you need is a truck: it's slightly better than
having only a car without a trailer but not really much better for any real
hard work.

The solution to your post depends on how you see SQL-Server. If you see it
only as a bunch of tables located on a server - much like a MDB file
backend - then yes using ODBC linked tables is good enough for most
applications and using ADP will give you a slight advantage over them in
term of speed and convenience (for complexe queries). However, if you want
to see SQL-Server as it is - as a fully powered database server with lot of
functionalities under the hood - then both ODBC linked tables and ADP are
totally insufficient and not up to the task.

With ADP, MS has tried to do what they were unable to do with ODBC linked
tables but with the goal of keeping their ease of use; something like trying
to drive a big truck like if it was only car. Clearly this was a wrong
combination. If you want to tame the full power of SQL-Server then you will
need to learn it and work your a** out; not to rely on an automated process
such as JET with ODBC linked tables or ADP.

Both JET/ODBC linked tables and ADP are automated processes and as such,
they are practically undistinguishable from each other; so clearly, two is
probably one too many.

 

[Robert Morley]

I'm curious what it is you recommend instead of ADPs. .NET, by and large,
is too slow and awkward to program in compared to Access (not to mention the
disk and memory requirements for the framework itself), ACCDB/MDB format
don't offer you easy access to things like Views, Functions, SPs, etc. (and
every time you create a new one, you have to manually re-link them, or
re-program for it, or whatever; in ADPs, they're just there, no questions
asked, no programming required). For now, I'll ignore the design abilities
of ADPs, since you can do more and better in SQL EM or similar tools, though
they're certainly handy to have at times.

So what do you suggest to replace ADPs? I think MS's suggestion of ACCDB
format is an absolute joke, made by people who think and program a certain
way, and are incapable of considering that not everyone follows their
paradigm. There are advantages to ACCDB/MDB, I'll grant, such as the
ability to cache tables client-side, but it's a very different environment
than ADPs, and does not make a suitable "replacement".


Rob

 

[Sylvain Lafontaine]

Very good question. It's a certainty that actually, .NET are an heavy load
on client side; particularly with the WinForms (but I do not feal the same
weight with WebForms/ASP.NET). However, many particularities of SQL-Server
2005 - like the Notification Services - can only be adressed correctly with
..NET and other things like web services also can only be accessed correctly
from the .NET and with the upcoming VS 2008 and SQL-Server 2008, the
situation will become more and more pointing toward the .NET framework.

I know that there are some kits for accessing some web services from Access
but like ADP, these are fossils from the past. You can also circumvent some
of the problems of ADP/ODBC linked tables by using unbound forms but by
doing so not only you loose a good part of the easiness of Access but again,
many new (and future) features like the notification services (and up to a
certain point, web services) remain inaccessible and the situation will
become worse in 2008.

The question is not to know if you will switch to .NET but when you will do
so and the answer to this lies under a variety of factors; like the costs of
quad cores (now at about the same prices as the double cores of last year),
the number of installation of ADP (or MDB/ACCDB) at your clients' sites,
their needs in term of features, security, fiability, etc. In all cases,
it's more a personal and business decision than a magical recipe.

 

[Robert Morley]

So ignoring web apps for the moment, which are not currently a significant
concern for me, what other reasons are there to switch from Access (of
whatever form) to .NET? The last time I looked at it, I found no compelling
reasons to move in that direction at all, and in fact, found several major
blocks to moving in that direction.



Rob

 

[Sylvain Lafontaine]

Another good question, one that sadly would require a whole book to answer
at.

The first chapter would probably deal with a list of the many limitations
and bugs of ADP. For example, ADP is practically unable to understand more
than a single schema name (with exception here and there) and it shows
additional bugs when this schema name is anything but dbo. A little problem
at the beginning when SQL-Server 2000 started but now a much bigger problem
with SQL-Server 2005 (and soon 2008) and one that will become worse and
worse as people start using the advanced features of schema naming provided
by SQL-Server 2005-8. Of course, if you don't use these and the only schema
present in your database is dbo. then you don't have this problem but in all
case, should be very long before you arrive at a new client site and notice
an extensive use of advanced schema naming.

Another problem is the lack of control over the transactions for bound
forms. Notice that at the beginning, after the launch of ADP-2000, they
have been promised by MS that they will be included in the 2002 version,
then in the 2003 version, then nothing.

The lack of control over the parameters for the resync command (nothing else
but the primary key), the absence of any Insert, Update and Delete commands,
the inability to access anything else but a direct link to a single database
at a time all severily limit the capabilities of ADP. (Not only .NET can
access/update multiple databases at a single time for a single form but it
can also use anything else, for example it can use a web service to access a
database protected by a firewall on a local LAN or even over the WAN;
something totally unavailable with ADP unless you start coding your own
messy code using unbound forms.).

Finally, I won't pass any comments on the support for Images and other Blob
files offered by Access and on these little things like the near total
absence of control over localisation/internationalisation/collation
problems. Of course, other things like the Notification Services, Microsoft
Message Queuing (MSMQ) Services or using a local storage are totally out of
question or might be very complicated to access for ADP or MDB/ACCDB.
(Notice that I don't want to enter into the details of each particular case
here in order to simplify the discussion.)

It's a fact that .NET is slow and need a powerful motor to make it advance
at a reasonable peace but at least, it offering you access and control about
anything you might think of. Excerpt for the speed problem (something that
will soon be solved by more powerfull hardware), you'll find it hard to
simply find something that it's not there. With ADP or MDB/ACCDB, doesn't
take long before having your head hitting the walls/ceiling and unlike the
speed problem of .NET, this is something that will never get solved. With
the soon the be released version of SQL-Server 2008 and VS 2008, the
situation will become even more unconfortable for ADP and MDB/ACCDB.

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

So ignoring web apps for the moment, which are not currently a significant
concern for me, what other reasons are there to switch from Access (of
whatever form) to .NET? The last time I looked at it, I found no
compelling reasons to move in that direction at all, and in fact, found
several major blocks to moving in that direction.



Rob

 

[Robert Morley]

Thanks for the input, Sylvain. Most of the problems you describe are not
concerns for me at the moment (nor are they likely to be in the immediate
future), so .NET remains off my radar, but at least I know what sorts of
things it's good for, should my needs ever change.



Rob

Another good question, one that sadly would require a whole book to answer
at.

The first chapter would probably deal with a list of the many limitations
and bugs of ADP. For example, ADP is practically unable to understand
more than a single schema name (with exception here and there) and it
shows additional bugs when this schema name is anything but dbo. A little
problem at the beginning when SQL-Server 2000 started but now a much
bigger problem with SQL-Server 2005 (and soon 2008) and one that will
become worse and worse as people start using the advanced features of
schema naming provided by SQL-Server 2005-8. Of course, if you don't use
these and the only schema present in your database is dbo. then you don't
have this problem but in all case, should be very long before you arrive
at a new client site and notice an extensive use of advanced schema
naming.

Another problem is the lack of control over the transactions for bound
forms. Notice that at the beginning, after the launch of ADP-2000, they
have been promised by MS that they will be included in the 2002 version,
then in the 2003 version, then nothing.

The lack of control over the parameters for the resync command (nothing
else but the primary key), the absence of any Insert, Update and Delete
commands, the inability to access anything else but a direct link to a
single database at a time all severily limit the capabilities of ADP.
(Not only .NET can access/update multiple databases at a single time for a
single form but it can also use anything else, for example it can use a
web service to access a database protected by a firewall on a local LAN or
even over the WAN; something totally unavailable with ADP unless you start
coding your own messy code using unbound forms.).

Finally, I won't pass any comments on the support for Images and other
Blob files offered by Access and on these little things like the near
total absence of control over localisation/internationalisation/collation
problems. Of course, other things like the Notification Services,
Microsoft Message Queuing (MSMQ) Services or using a local storage are
totally out of question or might be very complicated to access for ADP or
MDB/ACCDB. (Notice that I don't want to enter into the details of each
particular case here in order to simplify the discussion.)

It's a fact that .NET is slow and need a powerful motor to make it advance
at a reasonable peace but at least, it offering you access and control
about anything you might think of. Excerpt for the speed problem
(something that will soon be solved by more powerfull hardware), you'll
find it hard to simply find something that it's not there. With ADP or
MDB/ACCDB, doesn't take long before having your head hitting the
walls/ceiling and unlike the speed problem of .NET, this is something that
will never get solved. With the soon the be released version of
SQL-Server 2008 and VS 2008, the situation will become even more
unconfortable for ADP and MDB/ACCDB.

 

[Guest]

Well Now... As I prepare to end the day I feel well sad, frustrated lost!
Should I get up in the morning? Like you all I have invested a lot of time
in the adp, the best feature for me is the close link with the stored
procedure to loose this would be a great shame and make me look at other
products - yes even outside microsoft. - In fact in the latest project I
have no tables just very keen fast sp's so for me to loose this power would
be a great shame and involve huge loss

I have in the mean time to come up with a plan to keep my sanity, and to
help me with this I need some advice or even examples of how to use oledb
from an mdb or accdb just NO ODBC its primative and makes access all that we
dont want it to be - a non enterprise product!

Please help me before I loose my marbles (If found please return to author)

Kindest regards to all adp lovers (an under estimated bunch with an
undervalued product)

John Hart

PS Microsoft give us some good news!