Official Status of SQLServer 2005 ADP

[Norman Yuan]

If the app is ONLY designed for SQL Server7/2000, ADP is fine. However, If
the app must work with SQL Server2005, you cannot do anything with Access
ADP, and with Access12 almost there for the public and MS is still not
willing to say something on this, I'd not bet my app on ADP. I remembered
when SQL Server2K came out (after Access2000), there was almost right away a
Access compatibility to SQL Server 2K patch available, aimed mostly for ADP
to work with SQLServer2K/MSDE2K. Not this time when SQL Server2005 out,
though.

If you want to stick to MS and use SQL Server 2005, .NET might be the only
option, at least MS wants you so , for now.

 

[Sylvain Lafontaine]

You're right that with linked tables, the Jet FE will do most of the work
but this is true only if you don't use Views. With views (and taking some
precautions about VBA functions), must of the work can be done on the
SQL-Server. If you don't use views, your application might be even slower
than with using Jet as the BE.

This good news has its cost: first, you must evidently write a whole bunch
of views and use them everywhere. This process is not so much different
than creating SP and will requires a lot of work in the same way as creating
a bunch of SP; however, their overall capabilities are much more limited,
not only for selecting records but also for saving (writing) through them.
So this will be good as long as their use is sufficient to covers your needs
but as soon as you will need a little more from your application, then it's
overall performance will drop.

However and whatever your final choice, upsizing your MDB application to a
MDB file with linked tables to SQL-Server might be seen as a good
introduction to SQL-Server and nothing forbid to take a look at ADP and/or
..NET later while still keeping the MDB file as an intermediary step.

For updatable linked views:
http://support.microsoft.com/kb/q209123/

 

[Pat Hartman\(MVP\)]

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.

 

[Pat Hartman\(MVP\)]

"You're right that with linked tables, the Jet FE will do most of the work
but this is true only if you don't use Views."

That's not what Robert said. He said that the server will do most of the
work. As I said earlier, if you understand the constraints - ALL Jet
queries will be passed through to the server for processing. Information
regarding how jet works is available in the "Jet Programmer's Guide". It is
out of print but you can probably find a used copy on Amazon or ebay. I was
at a meeting with the A12 design team in Redmond in March and made a point
of discussing how Jet behaves with linked ODBC tables and was reassured by
the Jet team lead that Jet always attempts to pass-through the query. It
only does local processing as a last resort.

I have used Access MDB FE's to Oracle, DB2, Sybase, and SQL Server BE's and
never had response issues even with large (hundreds of thousands of rows)
tables.

MSFT isn't planning on removing support for existing ADP's but they are not
enhancing their current functionality. I believe that in the blog that was
referenced earlier in the thread, they mentioned that there would be no
design support for SQL Server added to the current Access release and it was
not being provided for A12 which is planned for the end of 2006. Access
ADPs will be able to link to 2005 databases. They just won't be able to
make any design changes. You'll need to use Enterprise manager.

Sylvain, I really didn't mean to cause a problem. You obviously know a
great deal about ADPs but they are not the only solution as you keep
implying. The Access team has also come to that conclusion. Rather than
dividing their efforts by trying to maintain two very different
environments, they are cutting their losses and moving on. They made the
same decision regarding Data Access Pages.

 

[Sylvain Lafontaine]

Well, in case like this, you open the SQL-Server Profiler and you take a
look at what happens with a quick test. So I made a query based on three
tables: Organismes, Ligues and Equipes; simple joins, no Where, all fields
retrieved.

It appears that you are partially right: the JET use a Select JOIN query but
only for retrieving the ID of the three tables but after that, it use
separates queries to retrieve the other fields by group of ten records: 10
records for the table Organismes, then 10 record for the table Ligues and
then finally 10 records for Equipes, etc.; repeating as necessary to get all
the records that it needs.

Of course, the above example is far from reality because it's based on only
three tables. With the real schema, the table Ligues is linked to eleven
other tables and its formulaires has about 12 subforms and many combobox and
many of these subforms and comboboxes are themselves based on Select queries
joining multiples tables. Needless to say that with the method JET is using
to retrieve the fields separately for each table by group of ten records,
this will require a fast LAN with a lot of availability and will generate a
lot of circulation on the network. Over the WAN, it's probably totally
useless.

Even if I don't use JET linked tables, I hope that the Access team will take
the time of changing this ridiculous behavior in the next version of Access
and reduce this great number of unnecessary round-trips to the server for
even simple queries. In its actual state, this is a strangulation of
performance and explains why everyone else is suggesting to use Views
everywhere when working with linked tables on a SQL-Server backend.

Finally, I never said that using a MDB FE with linked tables was a bad
solution; in fact, I've made the suggestion of using this many times in the
past. The only thing that I'm saying about this solution is that it can
only be seen as a first step because it only gives the possibility of taking
a fraction of the power of SQL-Server. If this is enough for you, than
search no more but if you need something more, then you have to go with
another solution than the use of linked tables and/or linked views.

Finally, about the futur of ADP, if you read some of my previous in this
newsgroup, you will see that's now more than two years that I've said that
ADP were doomed because MS have took the decision to let them go and replace
them with .NET technologies. MS never make any public announcement about
this but this conclusion was pretty obvious not only to me but also to a
bunch of other people when Access 2003 came out: excerpt for the color of
the menus, it was strictly identical to ADP 2002; with exactly the same
features, the same limitations and none of the numerous known bugs had been
corrected and the subsequent releases of SP for Office made it clear that
this conclusion was true.

 

[Sylvain Lafontaine]

Hum, Technet at Microsoft should clears up their things; look under the
section "Using Views for Queries with Complex Joins" in:

http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

 

[Pat Hartman\(MVP\)]

Try your query with ALL the tables but only select a limited set of records
rather than the entire table. The reason that Jet is bringing back only a
few records at a time is because there is a limited number of records you
can view on a form and it wants to be able to populate the form's recordset
as quickly as possible so the form will open up without delay. If it
retrieved all the records before opening the form, performance would be
dismal indeed. This is why is simply makes no sense to use unqualified
queries as RecordSources for forms. The user isn't going to look at
thousands of records. Why drag them to the client?

 

[aaron.kempf]

pat

you're a fucking idiot and a crackhead

mdb is a joke

not reliable

not stable

and not dependable.

it's not faster. it's not more flexible.

MDB is crap.

They are coming out with a ADP SQL 2005 patch soon.

 

[aaron.kempf]

sylvain

you're a fucking lying bitch

2002 was vastly improved over 2000

 

[aaron.kempf]

terry

use ADP don't listen to these assholes

 

[aaron.kempf]

you're crazy

you can update a form that uses a sproc under certain circumstances; i
swear

and they work a lot better for little lookup queries and all that

 

[aaron.kempf]

is Sylvain a guy?

i thought it was a chick; i mean.. only an emotional bitch would put
out misinformation like this.

you can use ADP in 2002 or 2003 and you have an AWESOME sproc designer.

MUCH MUCH MUCH better than in 2000; better than in Visual Studio; or
Enterprise Manager.

 

[aaron.kempf]

i use Views a LOT more than typical developers; they're basically the
only way to get anything done using Analysis Services.

 

[aaron.kempf]

Norman

you are full of shit; microsoft is coming out with a patch for SQL 2005
and ADP 2003.

the fact that we were required to have a patch when Access 2000 against
SQL 2000 is an indication that MS is mismanaging Microsoft Access.

I call for the public execution of all MS management in charge of
Access. it is obvious that they aren't doing their jobs correctly.

get off your fat ass, microsoft; and start fixing bugs.

 

[aaron.kempf]

sylvain

this shit
Even if I don't use JET linked tables, I hope that the Access team will
take
the time of changing this ridiculous behavior in the next version of
Access
and reduce this great number of unnecessary round-trips to the server
for
even simple queries. In its actual state, this is a strangulation of
performance and explains why everyone else is suggesting to use Views
everywhere when working with linked tables on a SQL-Server backend

is why MDB is a disease.

USE ADP AND DONT LISTEN TO THESE MDB-SISSIES!!!

 

[Sylvain Lafontaine]

Of course I have made a test with a limited number or records. However, as
JET doesn't have any clue on the real number of records in the SQL-Server
tables, this change nothing as expected.

 

[Mark Shultz Jr]

Terry,

I've found this thread to be quite interesting and just thought I'd offer my
humble opinion.

I am a big fan of the ADP/SQL server combination. I really fought it at
first, as it does require a different coding style than MDB solutions,
probably the five biggest differences that I've noticed are

1)Not using VBA functions in Queries
2)Figuring out how to pass parameters to stored procedures
3)The preference to use ADO as apposed to DAO and
4)Lack of local data storage
5)Knowing when to use SQL's ' and when to use VBA's " (this one can really
make you pull your hair out!)

With that said, once I learned my way around using ADP/SQL server, I have
never looked back. For example, for simple form filters, in ADP, MS Access
will automatically setup a server filter. This allows the SQL server to only
return the request row.

Another advantage is that there is no question where queries are being
evaluated, you do not have to worry about accidentally bringing large tables
and queries to the workstation.

It took me a while to figure out how to pass parameters to a stored
procedure programmatically, as it didn't seem to be documented well, but
once I figured that out, I've found stored procedures to be my best friend.

One of the advantages that I've found to using ADP is the "implied"
connection that always exists. To me, it's a very powerful feature to just
type docmd.runsql "sql statement...." and have any sql statement execute
against my SQL server, for example, this syntax could be used with the
xp_shellcmd to execute a local command on the SQL server or xp_sendmail to
have the SQL server send an email (which can be a lot easier than dealing
with MAPI at the workstation in some cases)

If/when your line of business app migrates to SQL server 2005, I wouldn't
think it would be a big deal to continue to run SQL 2K on a different box,
as you'll probably want to upgrade your hardware when you migrate to SQL
server 2005 anyway.

I personally will be sticking to ADP files as long as they are available,
however, I will be changing to .NET if they ever take my beloved ADP files
away.

Just sharing my opinion here of course. I'm a believer in ADP, but I do not
feel the need to be as rude about it as some people.

Mark Shultz
Procurement Data Specialist
RAD, Inc.

 

[Mark Shultz Jr]

I just wanted to add a couple things to my previous post,

My motivation for using ADP files is more for the end-user performance, not
for the SQL design tools. ADP files utilize a completely different model for
accessing data than jet and thus has much faster experience for the end
user...that is, after all, who most of us are developing for. My users like
being able to query a table with nearly 6 million records in a few seconds.

There are some query types that the ADP query designer doesn't support, such
as the insert from type. With that said, the query designer that is part of
the Enterprise manager does support building these types of queries and the
SQL can simply be pasted into the SP designer in ms access, or you can
create the SP directly from enterprise manager. I like having the
flexibility to do both.

Another HUGE advantage to using ADP/SQL is there is no ODBC links to
maintain at the client. You simply make sure the client has a current
version of the front end and that's where the client setup ends. Even when
dealing with other data sources outside the SQL server, you can use the
"Linked Server" functions on the SQL server, this way you only have ONE
connection to maintain to your other data sources, as opposed to a
connection from every workstation. This also means that any joining of data
with other data sources, such as data from your line of business app, which
you pointed out also uses a SQL Server DB is evaluated on the server. And in
a case like this, where both apps are on the same server, you can access
data from your line of business app using simple 3 part tables names, and
data from other outside sources, even data from access mdb's using 4 part
table names.

ok, end of rant...lol

I hope everyone is having a good Friday!

Mark Shultz
Procurement Data Specialist
RAD, Inc.

 

[aaron.kempf]

mark

i totally agree with you

im sorry that im come across as rude

every mdb-sissie in the world sits around and talks trash about ADP;
just because they're too scared to learn a little bit about SQL Server.

and I think that it's ridiculous.

ADP is the most important app to come out of redmond; ever-- and
Redmond just doesn't take it seriously.

it just drives me crazy.

I got fired from Microsoft TWICE for screaming bloody murder about all
these goddamn bugs

open up QA
a) create proc sphappy as select * from sysobjects
b) open the proc in adp; change the sql statement; his save

you get the error 'the parameter is incorrect'

I believe that this is representative that MS isn't taking ADP
seriously enough
I mean..

get real MS; ADP chokes on the 'create proc' syntax? (vs create
procedure)

that syntax is listed in books online and it just pisses me off that
you fat lazy microsoft dumbasses won't fix this bug.
i mean-- you guys can eat shit; i would rather work for free against
mySql than continue to sing the praises of your piece of crap
architecture.

Microsoft just needs to raise the bar

start taking this shit seriously.

AND MARKET ACCESS. When was the last time you saw a commercial about
MS access??

why do we have commercials about the xbox but not about ADP?

eat shit microsoft

fucking piece of shit company; crack-smoking foreigners

OH LETS GET DRUNK AT LUNCH AND DRIVE AROUND MY BMW

wake up you fat, lazy company