Re: Official Status of SQLServer 2005 ADP

The problem is that you are saying that an MDB with linked tables isn't a
viable alternative to an ADP and I strongly disagree.
"remains with Jet and linked tables or remains with Jet
but go with SQL pass-through queries and unbound forms"

It is most certainly not necessary to go with unbound forms or pass-through
queries. Performance with bound forms is quite acceptable provided the
RecordSource is a query with a where clause that limits the number of rows
returned from the server and this has been the case as far back as A97.
This is nothing new.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eAWEiKW$FHA.4080@xxxxxxxxxxxxxxxxxxxxxxx
> 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?
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Pat Hartman(MVP)" <patsky@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:%235PtMwU$FHA.2740@xxxxxxxxxxxxxxxxxxxxxxx
>> 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" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:upSWV7Q$FHA.912@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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.
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: http://cerbermail.com/?QugbLEWINF
>>>
>>>
>>> "Terry M" <terrym@xxxxxxxxxxxxxxxxxx> wrote in message
>>> news:um%230$hQ$FHA.2620@xxxxxxxxxxxxxxxxxxxxxxx
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>> "Pat Hartman(MVP)" <patsky@xxxxxxxxxxxxxxxxxxxx> wrote in message
>>>> news:%23681X3P$FHA.1568@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> 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 s
>>>>> 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.
>>>>>
>>>>> "Terry M" <terrym@xxxxxxxxxxxxxxxxxx> wrote in message
>>>>> news:%23mFW4XO$FHA.2520@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Exploding query text
    ... I have an app that's using DAO to automatically add some queries to an .mdb ... write my SQL Jet seems to rewrite the SQL sometimes creating a much, ... Here's an example of 2 queries that my app is creating using DAO that do the ... To me, the 2nd query is ...
    (microsoft.public.access.queries)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Sometimes the JET optimizer does not run the subquery to completion. ... Problem is in the ORDER BY statement of the problem query below ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • Re: Update db FE with "power user"
    ... You mentioned you are distributing an MDB. ... Your boss also modified the original query to ... You could likely do that by hiding the table links and only giving him access to queries that are snapshots. ...
    (comp.databases.ms-access)
  • Re: A2007 ADPs
    ... What is the defination of a query being too complex? ... an Access MDB to suck more data across the network than necessary? ... an aeroplane in flames just use an ADP instead. ... are hitting one of the main point of using passthrough queries. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Query is too complex after install Access 2007 SP 2 !?
    ... To be honest, the recurrent and inconsistant problems (sometimes they work, ... queries using a mix of UNION, Sub-Query and Outer Join is one of the main ... reasons that I stopped using JET for most of my work a few years ago. ... query problem would not be affected by decompiling. ...
    (microsoft.public.access.queries)
Loading