ADP or MDB - which is a better front end for SQL Server?

When Access 2000 and 2002 came out, Microsoft published information
suggesting that Access ADP Projects would be the preferred choice over an
Access MDB file as a front end for a SQL Server database.
More recently, however, several people have told me they actually prefer to
use MDB files as their front ends, while using the Enterprise Manager and
Query Analyzer in SQL Server to build all of their objects in the SQL Server
back end.
I recently attended one of the MS SQL Server Roadshows, and none of the
sessions I attended made any mention of front end development platforms,
which was somewhat surprising, since it would seem to be such an important
part of the development picture. Am I wrong in having the impression that
Microsoft is deemphasizing the benefits of using Access Projects over Access
MDB files?
I am just now getting around to adopting SQL Server for our databases, and
it would therefore be helpful to know which type of file would make a better
front end for SQL Server databases that don't require web access.
Any comments in this regard would be helpful.
Thanks in advance,
Paul
I have found that the effort required in putting together a good ADP is
substantially more significant than an MDB. But it has been worth it.
Example: I had a client using MDB/SQL and they had some very complex
reports that took anywhere from 5 minutes to 14 hours to run. Same reports
take from 5 seconds to 10 minutes in the ADP, with none of the underlying
code/queries changing. Speed of the SQL Server engine makes a lot of
difference. So does indexing (for mdb or adp).
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul Ponzelli" <pponzelliREMOVE@.ANTISPAMsurewest.net> wrote in message
news:OGFADYYUFHA.3944@.tk2msftngp13.phx.gbl...
> When Access 2000 and 2002 came out, Microsoft published information
> suggesting that Access ADP Projects would be the preferred choice over an
> Access MDB file as a front end for a SQL Server database.
> More recently, however, several people have told me they actually prefer
> to use MDB files as their front ends, while using the Enterprise Manager
> and Query Analyzer in SQL Server to build all of their objects in the SQL
> Server back end.
> I recently attended one of the MS SQL Server Roadshows, and none of the
> sessions I attended made any mention of front end development platforms,
> which was somewhat surprising, since it would seem to be such an important
> part of the development picture. Am I wrong in having the impression that
> Microsoft is deemphasizing the benefits of using Access Projects over
> Access MDB files?
> I am just now getting around to adopting SQL Server for our databases, and
> it would therefore be helpful to know which type of file would make a
> better front end for SQL Server databases that don't require web access.
> Any comments in this regard would be helpful.
> Thanks in advance,
> Paul
>
|||Hi Paul,
Using an .mdb on a local network has always worked fine for me. For
projects needed
across our wan, I use an .adp simply for the SQL Client/Server advantages.
A good way to look at it is for an .mdb query to run on someone's local
front end,
they pull the table data to them and query it locally. On a wan this is
unacceptable.
With an .adp, you can send the query string to the remote SQL server, it's
processed there,
and only the result set is returned. When you factor in triggers & stored
procs that run
at the server you can easily see the true benefit.
In my opinion, any new solution that is not exclusive to the branch running
it,
is developed using an .adp with SQL backend.
The RAD environment that Access provides, combined with the seemingly
limitless
potential to put together whatever specs are required, makes it my choice
for all
our wan solutions.
..aspx pages working against SQL backends takes it to the next level for
solutions
needing web access.
...just my humble opinion..
bob mcclellan
"Paul Ponzelli" <pponzelliREMOVE@.ANTISPAMsurewest.net> wrote in message
news:OGFADYYUFHA.3944@.tk2msftngp13.phx.gbl...
> When Access 2000 and 2002 came out, Microsoft published information
> suggesting that Access ADP Projects would be the preferred choice over an
> Access MDB file as a front end for a SQL Server database.
> More recently, however, several people have told me they actually prefer
> to use MDB files as their front ends, while using the Enterprise Manager
> and Query Analyzer in SQL Server to build all of their objects in the SQL
> Server back end.
> I recently attended one of the MS SQL Server Roadshows, and none of the
> sessions I attended made any mention of front end development platforms,
> which was somewhat surprising, since it would seem to be such an important
> part of the development picture. Am I wrong in having the impression that
> Microsoft is deemphasizing the benefits of using Access Projects over
> Access MDB files?
> I am just now getting around to adopting SQL Server for our databases, and
> it would therefore be helpful to know which type of file would make a
> better front end for SQL Server databases that don't require web access.
> Any comments in this regard would be helpful.
> Thanks in advance,
> Paul
>
|||On Thu, 5 May 2005 11:19:34 -0500, "Kevin3NF"
<KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote:

>I have found that the effort required in putting together a good ADP is
>substantially more significant than an MDB. But it has been worth it.
>Example: I had a client using MDB/SQL and they had some very complex
>reports that took anywhere from 5 minutes to 14 hours to run. Same reports
>take from 5 seconds to 10 minutes in the ADP, with none of the underlying
>code/queries changing. Speed of the SQL Server engine makes a lot of
>difference. So does indexing (for mdb or adp).
Personally, I've always found the effort required to optimize MDB reports is
well worth it compared to the constant battle required to develop and maintain
an ADP. I've worked extensively on one large ADP project, and it was torture.
Previously, I worked on several MDB front-ends to MS SQL Server that went much
more smoothly. Basically, to make an MDB work well with SQL Server, you have
to know some tricks and work-arounds, but they're not too arduous, and they
pretty much always work as expected. Note that you don't have to rewrite all
your Access queries as SPs and view, just the few that JET can't translate
properly for you without help.
To get an ADP to just work as advertized requires a vast number of
work-arounds made more difficult be the fact that ADP and ADO try so hard to
think for you that you're not allowed to do the thinking when you know how you
want it to do its work. Furthermore, each version of Access has such
different quirks with ADPs that work-arounds for one might break the app in
the other. Some bugs have even been fixed, then reappeared again more than
once. ADPs also became less stable, not more in Access 2002, plus Microsoft
seems to have removed almost all resources from improving and maintaining ADP
capability because it has never taken off.
|||My thanks to all the contributors to this thread. The information you have
provided will be very helpful to us.
I'm surprised to read that even with mdb files, there are issues that have
to be dealt with in getting it to work well with SQL Server. It makes me
wonder whether there are better applications than Access to use as a
front-end for SQL Server. If no version of Access is entirely seamless, is
there some other application that would be a preferred front-end for SQL
Server?
Thanks again in advance,
Paul
|||Hi Paul,
I personally write software in access/vb6/vb.net/asp.net and they are all
good for different types of projects, what I would say about access projects
is in my view they are the quickest to write by quite a large margin, it is
a system developed as a database program and as such is highly optimised to
work with databases. VB6 working with SQL is quite straight forward, takes
longer to write than the equivalent access program, in that system you have
a much larger array of components at your disposal so your application can
seem much richer in functionality. VB.Net/C# in my view the most robust
language I write in, the exception handling is so much better than previous
versions of vb/vba. The main point here is all these systems are using very
similar technologies under the hood to access databases, either
DAO/ADO/ADO.NET the choice really is a personal one as all the above systems
can produce relatively fast and reliable code. If I had to recommend any one
of them for a brand new project my choice for a standard workstation
installed application working with SQL server would be VB.NET, currently the
beta version of VS2005 and SQL 2005 are what all my new projects are being
written in with both programs due for release sometime this year (I hope).
About 50% of my development time is spent writing/re-writing access mdb/adp
projects and personally I don't see this type of system being phased out by
Microsoft in the near future as I believe Access is one of the best programs
that Microsoft has ever written (I know a lot of people that would disagree
with that comment).
Just my thoughts...
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Paul Ponzelli" <begone@.spam.forever> wrote in message
news:%23VrhFapUFHA.2172@.tk2msftngp13.phx.gbl...
> My thanks to all the contributors to this thread. The information you
> have
> provided will be very helpful to us.
> I'm surprised to read that even with mdb files, there are issues that have
> to be dealt with in getting it to work well with SQL Server. It makes me
> wonder whether there are better applications than Access to use as a
> front-end for SQL Server. If no version of Access is entirely seamless,
> is
> there some other application that would be a preferred front-end for SQL
> Server?
> Thanks again in advance,
> Paul
>
|||Thanks for adding these further thoughs to the conversation, Alex. They're
very helpful and also encouraging.
Another question - are you finding that the beta versions of both VS2005 and
SQL 2005 are far enough along that you're actually able to develop a
production application with them?
|||Thank you for this information, Sylvain.
I'm puzzled about one thing you said in your message. When you said
"MS is in the process of slowly killing . . . ADO and Recordsets"
Could you explain a bit about what that means? I'm confused when you say
that, because there is an ADO.net, and it would seem that if MS were phasing
out ADO, I wouldn't have expected them to develop a methodology to work with
ADO objects in the dot net framework. Could you elaborate a bit further on
this?
Thanks for your help on this.
Paul
|||Sylvain, since I'm new to both SQL Server and dot net, I need to ask a very
basic question just to make sure I'm understanding the implication of what
you're saying.
Are you saying, in effect, that dot net is likely to become the platform of
choice in the future for both OS and web applications, and that mdb and adp
files are likely to be deprecated or deemphasized?
|||> I would say (given the newest beta's) MS are a few months away from
> releasing both products.
At the SQL Server Roadshow I attended last week, someone asked the head of
the SQL Server 2005 development team when it would be released, and he
answered by saying it was "aptly named." I took that to mean that it would
be released sometime in 2005, but he wouldn't commit to a more specific
timeframe. He did say, however, that Barnes & Noble has actually been using
SQL Server 2005 in production for some time now.