Re: Official Status of SQLServer 2005 ADP

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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.