A2007 ADPs

[Sylvain Lafontaine]

Yes as a subdatasheet but not as a continuous subform. If the
(sub)datasheet format is sufficient for you, then you're OK but if it's not,
then you're out of look.

I must add that I've thrown these examples only to etablish the lack of
extensibility from the Access interface. The number of extensions asked by
people over the years is astonishing and while you can do practically all
you want with the display of your data when you are coding with C++ in a
Windows environment, it's incredibly difficult to do the same under Access.

If you throw a sufficient big chunk of money at it, you can develop a very
complicated ActiveX control that will probably do what you want under Access
but in this process, the simplicity of Access will be gone forever. (In
fact, it's even more complicated to do an ActiveX control that will run
under Access than it is to do the same thing under VB6).

Of course, you will say that you don't need this but the problem from MS is
that a lot of people are asking for these and they will all go elsewhere if
they don't find it in the MS offering. You could say that .NET is not so
good in comparaison of VB6, maybe; but doing a (+/- bad if you think so)
replacement where you are trying to correct things is much better than doing
nothing or trying to patch an old system who had already to many layers of
duct tape on it.

In 2007, you have to compete against things like Delphi 2007, SUN Java or
IBM WebSphere, this is the reality of MS; not a bunch of a few thousands of
programmers who would like to live in the past.

 

[Sylvain Lafontaine]

The only objective of ADP was to have the capability to directly bind a
passthrough query to a form and have it read/write instead of read only, as
it is actually the case with MDB.

With MDB against a sql-server, you have to use a passthrough query when your
design become too complicated or to slow but these queries are read-only and
cannot be binded directly to a form/report. ADP was developed to correct
this deficiency but beside this point, they have done absolutely nothing
beyond that. For example, it's impossible to manipulate your insertings,
updatings and deletings using stored procedures or to control the refreshing
of the current record with anything but a primary key. I won't pass any
comment on all the other limitations.

From the point of view of a programmer working against a SQL-Server, ADP is
incredibly limited and offers nothing more than the possibility of directly
reading the result of a SP into a bound form; nothing else. In comparaison
to other database interface tools, MDB and ADP are more like twins than
anything else.

(Of course, I talking here about the use of bound forms and not about the
direct manipulation of ADO objects with VBA code; because you can do the
later as easily with MDB than with ADP or with any other VBScript compatible
language like ASP.).

 

[Robert Morley]

It also addresses the need not to have to manually or programmatically link
every single table/view/SP/function that you need. You simply open an ADP
and they're all there. Accomplishing a similar feat in MDB or ACCDB is a
somewhat lengthier and more laborious process.

MS can recommend MDB or ACCDB all they want, but at the moment, ADP is
serving my needs far better.



Rob

The only objective of ADP was to have the capability to directly bind a
passthrough query to a form and have it read/write instead of read only,
as it is actually the case with MDB.

With MDB against a sql-server, you have to use a passthrough query when
your design become too complicated or to slow but these queries are
read-only and cannot be binded directly to a form/report. ADP was
developed to correct this deficiency but beside this point, they have done
absolutely nothing beyond that. For example, it's impossible to
manipulate your insertings, updatings and deletings using stored
procedures or to control the refreshing of the current record with
anything but a primary key. I won't pass any comment on all the other
limitations.

From the point of view of a programmer working against a SQL-Server, ADP
is incredibly limited and offers nothing more than the possibility of
directly reading the result of a SP into a bound form; nothing else. In
comparaison to other database interface tools, MDB and ADP are more like
twins than anything else.

(Of course, I talking here about the use of bound forms and not about the
direct manipulation of ADO objects with VBA code; because you can do the
later as easily with MDB than with ADP or with any other VBScript
compatible language like ASP.).

 

[Robert Morley]

Which gets into the infamous problem of .NET...sure, you can do quite a bit,
but it's not included, so you have to pay some company significant $$$ to
get the functionality you're looking for.

How are subforms done in .NET? Is it a simple drag & drop like it is in
Access, or something more? I don't mind more, but I'd like to know, in case
I actually consider moving to .NET at some point down the road.



Rob

 

[Guy]

Hi All,

What a response... not sure who to respond to, but just to add another 2
cents worth based on my own on historical experience.

I've tried using Linked tables to both Oracle and SQL Server in A2/A95 and
A97 and frankly the performance and reliability problems I experienced were
insurmountable. In one case I had quoted the client to build a A97/SQL
Server combination and after 4 weeks of issues (sorry too long ago to give
specific information) I gave up and used VB instead. Now I accept that this
was many versions of Access ago and I hope that the problems I experienced
have since been ironed out (comments please), but as they say once your
fingars have been burned...

The application I am currently upgrading was upsized, for performance,
reliability and scalability reasons, to an ADP from an MDB when A2000
appeared. Despite the initial agnst I incurred due to it being a new
"product" ie. the learning curve and bugs (I accept Dave that most have now
been corrected) the objective was eventually achieved and the results have
been and continue to be outstanding.

Frankly whilst I know some Access developers don't like ADP's, thinking them
inflexible, I think there terrific for your standard client/server
application.

The performance and reliability is as good as any VB6 (haven't used .NET yet
for client/server) application I've developed and once you know what your
doing easier and quicker too.

I recently developed a simple Financials Consolidation package for a client
using an MDB, the only issue was the volume of data involved and the
complexity of the few reports (all with 15/20 crosstab sub reports). The
performance however was less than startling (as in minutes to generate each
report). I upsized it to an ADP/SQL Server 2000 with all the additional work
involved in creating the crosstab queries and the performance to generate
each report went from minutes to sub 5 seconds.

Please convince me that Linked Tables work as well as an ADP.

Cheers
Guy

It also addresses the need not to have to manually or programmatically
link every single table/view/SP/function that you need. You simply open
an ADP and they're all there. Accomplishing a similar feat in MDB or
ACCDB is a somewhat lengthier and more laborious process.

MS can recommend MDB or ACCDB all they want, but at the moment, ADP is
serving my needs far better.



Rob

 

[Sylvain Lafontaine]

With your comment about MDB and crosstab reports against a SQL-Server, you
are hitting one of the main point of using passthrough queries.

When the schema of the database is not too complexe, ODBC linked tables will
work well because JET is able to translate sql queries into T-SQL and
execute them directly against SQL-Server. However, when these queries
become too complexe, JET can no longer do this and the performance will drop
like an airplane in flames. On many occasions, it's with reports that you
will be hit first with this problem first because queries for reports are
often (but not always) more complexe than the queries for the forms.
Because reports are read-only, the use of passthrough queries are then a
perfect solution.

However, if you add another level of complexity to your schema, queries for
the forms will become themselves too complexe and the speed of ODBC linked
tables will look like a turtle. Of course, there are solutions to this
(there is always some solution to a problem) but the simplicity of using JET
with ODBC linked tables is now gone. The only reason of using JET when
working against a SQL-Server is to remain inside its range of simplicity.
If you have to wander beyond that, the amount of work required to redesign
your schema and your forms to circumvent these problems (if and only if
these changes are acceptable to your client, which is not always the case)
make you loses any advantage of using JET in comparaison of other solutions.

 

[Guy]

Thanks Sylvain,

Appreciate your thoughts.

What is the defination of a query being too complex? Is it one that causes
an Access MDB to suck more data across the network than necessary? the
volume of data? How many tables/joins are in the query? The quality of the
database design?

Of course it is all of those things and more, but I'm sure you get my
somewhat tonque in cheek point.

Let's say you develop an MDB using linked tables you complete the task and
the customer is so happy that he wants you to add one more feature, however,
the addition of this feature breaks the too complex query rule and you end
up with a turtle. From my experience why risk turning your application into
an aeroplane in flames just use an ADP instead. Of course I accept that you
could apply the same argument to an ADP, but as yet I haven't found it's too
complex query point

I vaguely remember aspects of one of the problems with my finger burning
A97/SQL Server linked table app. I was trying to populate a form combobox
with a query on a self-referencing table it only had something like 50
records in it, but would take upwards of 30 seconds to complete.Can't give
you any further information than that, it was too long ago, and isn't really
relevant anyway.

Still not convinced that ADP's don't offer significant advantages over
linked tables.

Cheers
Guy

 

[Guy]

Sylvain,

Sorry, was rushing out to lunch and wanted to finish my last posting
quickly.

You did make a very good point that you have the option to use passthrough
queries with an MDB and these are ideal for complex queries and reports
which are read-only.

I admit I had forgotten about this feature, not having done much MDB work
recently (being an ADPer).

I won't forget in future.

Your comments appreciated.

Cheers
Guy

 

[Vayse]

Its drag and drop. Well, its simple if you have a relationship between the
tables.
Which is true in Access as well.

Most of my time at work is occupied with dotnet apps, but I have some
development in adps.
Access is simply a quicker development tool. I'm involved with scouts. We
need to set up a membership database.
There is no way I'm doing that in dotnet - Access will be much easier, and
there will be less potential issues for me.
Vayse

 

[Robert Morley]

Good, glad to hear it's that easy. That's one down. Now, if they can give
me some decent continuous forms, I might eventually consider it for a FE.


Rob

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

Yes as a subdatasheet but not as a continuous subform.

No, the subform of the datasheet is a continuous subform. Try it,
you'll see.

If the
(sub)datasheet format is sufficient for you, then you're OK but if
it's not, then you're out of look.

The main form will be in datasheet format, but the child form will
be the same format as it would be viewed in regular form view.

Try it and see -- you obviously haven't or you'd have discovered
that what I described actually really does work.

I must add that I've thrown these examples only to etablish the
lack of extensibility from the Access interface. The number of
extensions asked by people over the years is astonishing and while
you can do practically all you want with the display of your data
when you are coding with C++ in a Windows environment, it's
incredibly difficult to do the same under Access.

To get the most out of Access you have to do things the way the
Access programmers provided for you. That's good enough for me, to
be honest. The mindset of the programmer who must do things a
particular way is not a mindset for any user of a RAD development
platform. The whole point of RAD is that decisions have been made
for you in the creation of high-level objects for you to use quickly
to build your app.

If you throw a sufficient big chunk of money at it, you can
develop a very complicated ActiveX control that will probably do
what you want under Access but in this process, the simplicity of
Access will be gone forever. (In fact, it's even more complicated
to do an ActiveX control that will run under Access than it is to
do the same thing under VB6).

Those who *must* have things that Access can't provide should not
program in Access. But most of my clients aren't that picky about UI
that they are bothered by being limited to the interfaces MS
provides.

And, of course, there are major changes to what you can do in this
regard with Access 2007.

Of course, you will say that you don't need this but the problem
from MS is that a lot of people are asking for these and they will
all go elsewhere if they don't find it in the MS offering.

My advice to them is that if they *must* have it and their clients
are willing to pay the extra money it costs to abandon RAD
development in Access, then they shouldn't use Access.

My clients aren't that picky. They are perfectly happy with UIs that
accomplish the same tasks, even if they aren't implemented in the
way that Access isn't designed for.

You could say that .NET is not so
good in comparaison of VB6, maybe; but doing a (+/- bad if you
think so) replacement where you are trying to correct things is
much better than doing nothing or trying to patch an old system
who had already to many layers of duct tape on it.

The whole point is using a generalized tool or a tool that is
optimized for a specific task.

In 2007, you have to compete against things like Delphi 2007, SUN
Java or IBM WebSphere, this is the reality of MS; not a bunch of a
few thousands of programmers who would like to live in the past.

I think it's wrong to compare Access to standalone, generalized
development platforms. Of *course* Access can't do everything that a
generalized development package does. The trade-off is that what
Access is optimized for is mostly much, much easier in Access than
it is in a generalized environment.

For any particular application, one chooses the development platform
accordingly.

Blaming Access for lacking capabilities of .NET or VB is just
senseless. It's like asking a light-duty pickup to be a Mack truck.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

The only objective of ADP was to have the capability to directly
bind a passthrough query to a form and have it read/write instead
of read only, as it is actually the case with MDB.

Do ADO disconnected recordsets provide any kind of workaround for
that? I've never used them (don't need them), but they osund like
they'd allow certain kinds of things to be editable that were not in
the past.

 

[David W. Fenton]

we've heard companies make
promises of full forwards and backwards compatibility in the past,
or that something would be THE way to do things from now on, and
they've only rarely ever actually stayed around for more than a
few years.

ADO and ADPs are perfect examples of just this kind of thing.
Microsoft has completely changed direction since the release of
Access 2000 and the introduction of those technologies in Access.

 

[Vayse]

Oh, and drag and drop bound combo boxes won't work half the time. It seems
to depend on the order on which you load the data for the combos.
But outside of that.......

 

[Sylvain Lafontaine]

Sorry for the delay, I was away.

Passthrough queries can be good for complex reports but excerpt for the
cases when you need to have complex sub-reports, too. I don't remember
exactly but my guess is that trying to manage sub-reports with passthrough
queries is probably a very big hassle; particularly but not limited to the
cases when these sub-reports will flow/span over more than a single page.

 

[Sylvain Lafontaine]

Sorry for the delay, I was away.

I remember some discussions a ferw years ago - around circa 2000 - about the
comparaison between using disconnected recordsets or ADP. I don't remember
all the conclusions but if I remember correctly, the main point against
using disconnected recordsets (or passthrough queries for reports) was the
problem with using subforms and subreports. For example, you cannot set
different recordsets to subforms on a continuous form. Same thing with
subreports: anyone trying to use the event model of Access' reports to
dynamically feed the data for subreports from passthrough queries is
probably going for the ride of his life; particularly if these subreports
can spread themselves on more than a single page.

At least, with ADP, you don't have to mess with this if you also want to use
SP as the source of your subforms and subreports.

 

[Tony Wickernark]

Microsoft invested a lot in ADP.. and there have been great modifications
made in every release.

2000, XP, 2003 and 2007 all have tremendous improvements for ADP
where do you come up with your facts, kid?

 

[Tony Wickernark]

it's not a variation of the jet format.. because it doesn't use jet
it uses ADO and SQL Server and it is 100 times better


stop spreading lies David.. in one thread you claim that Jet Replication is
flawless.. except that it doesn't work in Access 2007 or with Vista ROFL


stfu kid

ADP roicks and it always will

 

[Tony Wickernark]

and what all three formats have in common.. is that they're all obsolete in
favor of ADP

 

[Tony Wickernark]

re:

ADP has been cancelled because it offers no real advantage over MDB file but
this conclusion is not based on performance but on functionality. The
capabilities of ADP are so closely related to MDB that if you cannot do it
with MDB, chances are high that you cannot do it with ADP either. When you
take into account the vast amount of money invested by MS into the .NET
technologies, the slight gain of speed or the easier communications with SP
that you get using ADP is not sufficient to justify the continuous supports
by MS of three different methods to work against a SQL-Server database. The
ratio costs/benefices is simply just not there to justify the presence of an
intermediate solution between the two others.


YES IT IS WORTH IT-- MICROSOFT IS LOSING THE DATABASE WAR BECAUSE THEY DO
NOT COMMIT ENOUGH RESOURCES