Re: A2007 ADPs

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


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uF0Iy2VrHHA.4548@xxxxxxxxxxxxxxxxxxxxxxx
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.

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


"Guy" <Guy@xxxxxxxxxx> wrote in message
news:%236SCC3UrHHA.1172@xxxxxxxxxxxxxxxxxxxxxxx
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




"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uU13OQUrHHA.4764@xxxxxxxxxxxxxxxxxxxxxxx
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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eOHHuAUrHHA.2152@xxxxxxxxxxxxxxxxxxxxxxx
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.).

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


"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message
news:Xns994D92A168426f99a49ed1d0c49c5bbb2@xxxxxxxxxxxx
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in news:#Bc78dLrHHA.4624@xxxxxxxxxxxxxxxxxxxx:

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.

I always felt that one of the chief motivations for creating the ADP
was the unreasonable prejudice among many outside the Access
community against Jet, either as data store or as data interface to
server back ends.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/










.

Relevant Pages

  • Re: From .mdb to .adp database
    ... there is no exact equivalent query object in SQL Server to MDB's query. ... Most likely, the wizard converts MDB queries to Viwes or SP, if the queries are convertiable. ... Also, when you decide to use MDB front-end, you can choose use MDB queries in the front end or use SQL Server side query objects. ... SQL Server is very powerful server software, whether you use MDB, ADP or anything else to access data from it, you MUST learn how to use it and almost for sure you need to learn another programming environment. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: A2007 ADPs
    ... queries with an MDB and these are ideal for complex queries and reports ... I admit I had forgotten about this feature, not having done much MDB work ... On many occasions, it's with reports ... Please convince me that Linked Tables work as well as an ADP. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: From .mdb to .adp database
    ... there is no exact equivalent query object in SQL Server to MDB's query. ... Most likely, the wizard converts MDB queries to Viwes or SP, if the queries are convertiable. ... Also, when you decide to use MDB front-end, you can choose use MDB queries in the front end or use SQL Server side query objects. ... SQL Server is very powerful server software, whether you use MDB, ADP or anything else to access data from it, you MUST learn how to use it and almost for sure you need to learn another programming environment. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: From .mdb to .adp database
    ... I decided to go on with adp project, and i have created a store ... But queries are different stories. ... In SQL Server, there are Views, Stored Procedures, UDFs. ... Also, when you decide to use MDB front-end, you can choose use MDB ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Official Status of SQLServer 2005 ADP
    ... "remains with Jet and linked tables or remains with Jet ... but go with SQL pass-through queries and unbound forms" ... RecordSource is a query with a where clause that limits the number of rows ... > queries under MDB was bad and worst than the one offered by ADP while you ...
    (microsoft.public.access.adp.sqlserver)