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

[Paul Ponzelli]

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

 

[Kevin3NF]

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

 

[Alex White MCDBA MCSE]

Hi Paul,

if you are starting from scratch and SQL is going to be your backend then
ADP is the way to go as long as you are going to make good use of the power
of the SQL server, to me using SQL like access for storing data is a shame
the power of SQL is in the client/server model being able to send relatively
small lines of code from the front-end to the server for the server to the
go off and process that information, I personally have not moved from access
2000 ADP projects yet as they have worked perfectly for me for 5 years. I
use both mdb and adp's for my front-ends and they both have a place in my
developments. I think the long term is more and more people will eventually
move over to using SQL to store data rather than access mdb's, but at the
moment that is not going happen on a lot of existing projects based on
access mdb's. Personally I feel the ADP/SQL route is more scalable but that
is mainly due to the SQL backend rather than the ADP project, what I will
say is I have a client running access 2000 ADP project against SQL7 and it
has run flawlessly on over 150+ clients for more than 4 years.

At the end of the day it is a personal choice as both have their strengths
and weaknesses.

 

[Jim~C]

What about security? Is it not true that when you use an MDB front-end you
are tied to the workgroup security model provided by Access?

If you use an ADP, you can use integrated security and not worry about the
workgroup files, extra user profiles to maintain, etc. The other benefit is
that if you have a combination of frontends (i.e ADP, ASP.NET, etc) the
security model will work for all of these scenarios.

I'd be interested to hear feedback from more experienced developers in this
area.

Jim

 

[John316]

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

 

[Alex White MCDBA MCSE]

Hi Jim,

Yes security is an issue, in my view access does not have good security, the
use of either database passwords or system.mdw files is not a secure
situation at all, both systems are hackable because of one simple reason the
security exists within files that can be moved/copied/hacked , in both
instances I have had need in the past to 'recover' passwords and this is
very easy. If security is a requirement then the integrated security of
windows within a SQL server installation is the strongest solution as the
operating system has the ability to lock out users for bad password
attempts. The issue of security is something that should be considered up
front, e.g. is the database in a closed situation or is it available on the
web 2 totally different security requirements. The ADP solution is a single
file program with all the security being applied on the server, with mdb
security the use of WRKADMIN.EXE and system.mdw files does mean that just
setting security on a new workstation is more complex. Also the use of mdb
level security does require 2 levels of configuration as you need to
configure network security and database security as 2 different steps. From
a personal perspective SQL server as a product is the best product for a
wide range of database solutions, and ADP projects complement that solution
perfectly. I know there are many developers out there developing SQL
backend's with mdb front-ends, if I was starting from scratch then I would
choose SQL/ADP for all but the smallest of projects. From the perspective of
different front-ends, my thoughts on this are simple, I develop database
solutions, what does the client want as the front-end
access/vb/vb/net/asp/asp.net it does not matter because the intelligent part
of the system should be the database backend and you only get that
intelligence from using a client-server solution like SQL server.

Security is a very large topic and is something to be considered up front in
any project, thinking about it at a late stage in the project can have
serious affect on functionality and delivery times of that project.

 

[Steve Jorgensen]

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.

 

[Steve Jorgensen]

Hi Paul,

if you are starting from scratch and SQL is going to be your backend then
ADP is the way to go as long as you are going to make good use of the power
of the SQL server, to me using SQL like access for storing data is a shame
the power of SQL is in the client/server model being able to send relatively
small lines of code from the front-end to the server for the server to the
go off and process that information, I personally have not moved from access
2000 ADP projects yet as they have worked perfectly for me for 5 years. I

That might explain why you've been able to make them work reliably. You might
want to consider continuing that policy indefinitely.

 

[Alex White MCDBA MCSE]

Hi Steve,

I have not moved from access 2000 (ADP) on any of my clients for that very
reason....

 

[Paul Ponzelli]

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

 

[Alex White MCDBA MCSE]

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

 

[Paul Ponzelli]

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?

 

[Sylvain Lafontaine]

With the Beta 2 of VS2005 and its GoLive license, MS has created a new
concept: « Beta product » and « going into production » in the same
sentence.

However, MS is in the process of slowly killing COM/COM+, ADO and
Recordsets. Even if ADP is a much more better technology in its design than
the ODBC linked tables of MDB; it has been killed in favor of .NET before
having the chance of being finished. VB6 and its easy way of creating COM
components are already out; so MDB and its ODBC linked tables are probably
next on the list of MS but they are in a different situation because of its
current very large number of users; so the process of killing them will be a
little more slowly than with ADP (maybe something like the DOS Box mode
under Windows).

IMHO, start playing with VS2005 and SQL2005 and see what happens. If
necessary, go back with VS2003 and SQL-Server 2000 (or MSDE).

In the mean time, you can use MDB (for small and simple projects) or ADP
(for a better performance in the case of a big database or the necessity of
going over the WAN) but only for short term projects: six months or one year
max.

 

[Alex White MCDBA MCSE]

Hi Paul,

Absolutely I have been beta testing both since beta1 and all my previous
vb.net (.net 1 and 1.1) code is now vs2005 beta 2 (.net 2) runs like a
dream, still some bugs but on the whole excellent stuff, one of the major
new points of SQL 2005 is managed code within the SQL server, simply put
write your normal vb.net code and run it inside SQL, anyone who has written
large and complex transact sql stored procedures knows how difficult it can
be in comparison to normal vb. MS have on their website some public beta's
of both that are worth looking at. As far as production applications are
consigned my whole company is now running on SQL 2005 with ASP.NET v2, and a
mixture of different front ends. I would say (given the newest beta's) MS
are a few months away from releasing both products.

 

[Paul Ponzelli]

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

 

[Paul Ponzelli]

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?

 

[Paul Ponzelli]

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.

 

[Sylvain Lafontaine]

Excerpt for the three letters ADO, there is practically no resemblance
between ADO and ADO.NET and these two technologies are very, very different.
The « ADO.NET » is simply a very poor choice for a name; one that bring a
lot of confusion when it comes to compare these two.

 

[Alex White MCDBA MCSE]

Hi Paul,

MS seem hell bent on converting the world to managed code, e.g. .NET
everything, these technologies are radically different from the vb6/ActiveX
world, if you looked at the coding differences between ADO/DAO and ADO.NET
you would see a totally different way of working in some ways more long
winded (more lines of code to do the same thing), but more streamlined. I
personally still use vb6 in some of my work because of the ActiveX stuff in
them but for all new projects it's vb.net. Everyone who has tried to convert
an existing vb6 application over to the .net version has probably been
disappointed at the incompatibility between the two systems and has
considered writing the application from scratch rather than converting.

 

[Sylvain Lafontaine]

Yes.

For ADP, the job of deprecation is already well done.

For MDB, they will be kept around a little more longer but only for
supporting these innumerable kitchen recipes' databases that already exists.
Strictly speaking, we will have more knowledge about this only when the next
version of Access will come out next year but when you see what's already
happened to VB6; you shouldn't put to much faith on JET (and ADO) as a
platform of choice for any future development.

Honestly, if you take a look at m.p.access newsgroups, probably that you
will find many peoples whose opinion is exactly the contrary. However, if
you had took a look at m.p.vb.* newsgroup last year; you would have also
found many people thinking that VB6 had still a bright future at that time.