ADP vs. MDB?

[Chris Burnette]

The company I work for currently has all their data in Excel spreadsheets.
We want to set up an actual database using MSDE/SQL Server Express as the
back-end, and Access as the front-end.

The question at this point is whether we should use .adp, or just use .mdb
with linked tables. Personally, I like the fact that .adp seems to be
designed to work with MSDE/SQL Server, but I have heard that the security
model for .adp's is broken, and that .adp won't work with SQL Server
Express.

If the security model is broken, that could be an issue, because we do need
security. I have no problem setting permissions in MSDE or SQL Server, but
I want to make sure that those permissions will be in effect when someone
accesses the database with an .adp front-end.

As far as re-doing work is concerned, that's really not an issue. We do
have 1 form in the Access DB we have now, but as far as I know that was
created in VBA and doesn't have any custom coding.

So, I just want to get some opinions on this; would you recommend .adp or
..mdb, and why?

Any input would be appreciated.

Thanks,

Chris

 

[Sylvain Lafontaine]

The broken security problem that you have heard of is probably the
Application Role problem. If you don't use them, then you will be OK. I
don't know about MDB files but I won't be surprised if you have the same
problem.

It's the design tools of ADP that don't work agains SQL Server Express but
the datas are fine.

ADP is slowly dimmed into oblivion in favor of the .NET framework; so my
suggestion would be that you go with either MDB or with .NET. ADP projects
are fine and powerfull but they have a lot of small problems here and there
and many things about using queries and parameters are not so obvious and
poorly documented.

For MDB, if you have speed problems, then you will have to use Views to make
things faster. The following link will tell you how to have updatable
views: http://support.microsoft.com/kb/q209123/

 

[Mary Chipman [MSFT]]

The Access team at Microsoft recommends that for new Access-SQL
projects you use an mdb for the front-end. This gives you greater
flexibility in designing your application because you can also take
advantage of local storage for database objects and pass-through
queries for stored procedures. Any security that you implement needs
to be implemented in SQL Server, not in Access, and is the same
security model regardless of what the front-end application is. For
more information on security, see
http://www.microsoft.com/sql/techinfo/administration/2000/security/default.mspx.
To obtain the Developer edition of SQL Server (you need this to
implement security for MSDE), see
http://www.microsoft.com/sql/howtobuy/development.mspx.

 

[Malcolm Cook]

The Access team at Microsoft recommends that for new Access-SQL
projects you use an mdb for the front-end.

Do you have a quote or other reference for this statement? Do they give reasons?

Inquiring minds, etc...


--Malcolm

 

[Nick Stansbury]

I just re-converted my .adp project back to an .mdb. Lots of reasons for
it - primarily we found .adp was just full of irratating bugs. Works faster
in an .mdb which I just can't understand!

One major problem we had was that in an adp all calls to the database seem
to run in the same connection context (i.e. adp opens one connection to the
db, and then runs everything down that connection - when you close the db
the connection closes). This caused us all sorts of problems - not least
when certain idiots (i.e. me) omitted a certain "commit tran" after a
"rollback tran" I ended up with ado throwing away the error message (it
seemed to go into the recordset returned by the command!?!) and then every
subsequent insert / update / delete command ran inside that open-ended
transaction context. When you then shut the db all of the changes you made
since the rollback tran get thrown away. Led me to make the decision to
revert to an .mdb, open and close my own connections as required, implement
asynchronous data interaction with ado (i.e. background processing &
fetching of recordsets), client side cursors (no more endless recordlocking
for people who've left the admin client open) and a proper error handling
system - and I also went away and closed up that stupid open ended
transaction.

Still get a few crashes though. Has anyone else had this sort of experience
making heavy use of ado, access 2003 & sql server? Seemingly pretty random
(sometimes get proper crashes, sometimes access just closes itself without
any warning).

Nick

 

[Pete Nurse]

Mary,

Does that mean that ADP is on the skids? I've been wrestling with an
ADP application (as other posts here will testify) for several years
partially at least on the basis that it was Microsoft's preferred
Access/SQL solution.

Have I been living a dream?

 

[aaron.kempf]

Chris

I really highly reccomend using ADP over MDB. i mean-- performance is
a lot better with ADP.

Sylvain
im not sure that I agree with you at all when you said that they're
quietly riding off into the sunset.

I'm sorry that you dont know enough about ADP to do them successfully--
that doesn't mean that they're not the best solution in this situation.

Mary

I want to see that in writing; I believe that you are steering people
in the wrong direction.

Nick

so sorry that you dont know SQL Server well enough. ADP are a lot more
powerful.

Pete

you are on the right track, email me at (e-mail address removed) if you
need help. Do you use Instant Messenger?

I'm damn strong with SQL and ADP is a walk in the park.

-Aaron

 

[Nick Stansbury]

Aaron,

Thanks for the detailed analysis of all of the earlier posts. Care to
take the time to justify your statements in slightly more depth?

Nick

 

[aaron.kempf]

with ADP you dont have
a) performance problems
b) stability problems
c) scalability problems
d) buggy queries
e) versioning problems

with MDB you can keep on writing your crappy DAO .UPDATE bs.

ADP rocks my world; you guys are crazy and need to learn how to write
TSQL.

What do you want?? I hereby challenge ANY mdb developer in the _world_
to a cook-off.

 

[Mary Chipman [MSFT]]

No, you haven't been living a dream That was indeed the original
message. However, it changed in the last couple of years as new
technologies evolved, specifically SQL Server 2005, which offers
limited ADP support. Microsoft will of course continue to support ADP
applications written against SQL Server 2000, and will support
connecting design-complete ADP's against SQLS 2005. I don't believe a
final decision has been reached by the Access team on the future
ADP's, but in the meantime they recommend MDB's as the front end for
all *new* Access-SQL projects.

--Mary

 

[Mary Chipman [MSFT]]

That link is intended to clarify your optins -- the Access team has
been actively promoting the SQLS-MDB message in talks and keynotes at
industry conferences. I am not aware of any other links offhand.

The reason is greater flexibility and the uncertain future of ADP
support for SQL Server 2005 and beyond. I don't believe a final
decision has been reached by the Access team on whether ADP's will be
deprecated or not, but in the meantime they recommend MDB's as the
front end for all *new* Access-SQL projects.

--Mary

 

[Mary Chipman [MSFT]]

You have it in writing in this thread. As I posted elsewhere, ADP's
will continue to be supported and may indeed be the optimal solution
for your particular application and environment. However, there is no
one-size-fits-all in database development, and often the resulting
application depends more on the skill of the developer than the
platform or programming language chosen. That being said, the Access
team recommends MDB's for *new* Access-SQLS applications while being
committed to ongoing for existing ADP applications.

--Mary

 

[aaron.kempf]

mary

i dont believe you; sorry.

anyone can add a [MSFT] to their name.. right?

i believe you are preaching a 'one size fits all' by talking shit about
the ADP.

ADP are the best platform in the world; and nobody should have to use
MDB in the real world for *ANYTHING*.

I mean

Linked Tables
Refreshing Tables
Update SQL Passthroughs
DSNs
***PERFORMANCE***

there are a billion reasons not to use MDB and if you say 'dont use
ADP' then im going to take my shit and go and buidl simple apps like
this for linux.

So if that's what you're telling me that Microsoft

a) sells a crappy database
b) 'doesnt reccomend'-- gag me with a spoon-- the most powerful
application ever written

then i'll take my shit and go elsewhere.

it's obvious that Microsoft has NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
comprehension that there are HUNDREDS of access applications at use in
the typical company.

I've been to a dozen companies that have over 100 Access applications.

And for them to not have a clear migration path to something that HAS
QUERIES THAT DONT CRAP OUT

Mary; I'm sorry that you're a SQL wimp and you just can't learn SQL
Server.

STOP ****ING TALKING TRASH ABOUT ADP.

IF YOU HAVE SOMETHING TO SAY THEN YOU TELL THOSE CORNHOLERS TO MAKE A
PRESS RELEASE.

OTHERWISE YOU CAN TAKE YOUR 'OH, MY UNCLE IS ON THE ACCESS TEAM AND
DOESNT RECCOMEND ADP BECAUSE HE IS MENTALLY RETARDED' mentality and
_SHOVE_ it up your ass.

I'm sick and tired of your bullshit rumors and 2nd hand truths.

From what I understand; ADP are going to work _FINE_ on SQL 2005.

From what I understand, ADP are going to work _FINE_ in the next

version of Access.

I'm sorry that you work for the great SATAN.

That ****ing company fired me twice for speaking the truth.

They fired me for BITCHING about sql authentication.. this from a
company with $70bn in the bank that 'cant afford to protect sql server
against a simple dictionary attack'.

I just dont believe your crap.

I'm sorry that you have friends that are too wimpy to learn SQL Server.

ADP is the best platform in the world.

ADP doesnt have to link, refresh tables. ADP doesnt have to edit SQL
passthroughs before running it-- if it really needs to you can use temp
sprocs.. right??

SQL Server is a much better repository than a handful of MDB.

And you can tell your SQL-wimp friends to come out and be men and talk
to the world.

Because I'm not ACCEPTING that answer from you because I have
SPECIFICALLY heard otherwise.

It is about SKILL. It's about the fact that you MDB-wimps are scared
of learning new things. It's about the fact that you MDB-wimps won't
bother to give it a chance.

It's about the fact that you MDB-wimps wont consider that maybe there
is a better way than copying and pasting different queries 100 times a
day

Views and Sprocs are about a billion times more powerful than MDB
queries.

Views and Sprocs dont just randomly CRAP OUT.

Mdb queries DO just randomly crap out.

I mean.. query on top of query on top of query in MDB and you get some
'this query is too complex' error

So then you have to build a bunch of temp tables

MDB apps-- as a whole-- use temp tables way too much. you sit around
and wait 20 minutes for a report to run because MDB queries are too
flaky to do anything without making a dozen temp tables.

ADP apps-- as a whole-- work like a charm. You can join 12 views
together and shit doesnt crap out. you can put 12 views on top of each
other and shit doesnt work out. You can bind SPROCs to forms.. and
it's easy as pie

or let me guess, Mary.. you've never given ADP a serious chance and
you've never used them right?

How about DAP?

You ever bothered to look at DAP?

DAP-- via ADP are an AWESOME data entry tool. It makes no sense to use
those with MDB; as it is
sooooooooooooooooooooooooooooooooooooooooooooooooooooooo sharp with SQL
Server.

How are you going to use it with SQL Server without using an ADP?

I mean-- in an ADP it is drag and drop; fields from tables onto HTML
forms.

With MDB it takes 10 minutes to do ANYTHING from an end-users
perspective.

It's time to stop being so lazy Mary-- I know for a fact that you have
the mental capacity to learn more about ADP.

I know for a FACT that ADP are going to work for the next 20 years.

I'm just sick and ****ing tired of hearing 'one size doesnt fit all'--
THEN DONT PREACH ONE SIZE FITS ALL

You're the one that is cutting down the OPTIONs that people have for
deploying simple apps.

You're the one that is cutting down the ABILITY TO PROVIDE DECENT,
STABLE DATA ENTRY ENVIRONMENTS.

One size doesnt fit all

SO WHY IN THE HELL DO YOU USE MDB FOR EVERYTHING??

 

[Nick Stansbury]

How sad. Either you didn't read *any* of the earlier posts or you didn't
understand them. No one is talking about writing *DAO* b/s as you so
eloquently described it. We're all talking about using *ADO* within an
..MDB - no DAO, no Jet-SQL, no "Update bs".

I don't know where you get your list below - I've had better performance and
better stability with an MDB than I ever had with ADP's.

 

[aaron.kempf]

that's because you can't write SQL Server code.

sorry. that is how i see it.

-Aaron

 

[aaron.kempf]

I just think that you guy are crazy

instead of having ONE DATABASE you're telling this guy to have TWO
DATABASES?

isn't that inherently more complex?

I mean-- duh-- even you mdbkiddies can count to 2 right?

ADP keeps everything in one place. these guys are wimps and trying to
promote .NET in an Access newsgroup is LAUGHABLE.

I mean-- ROI-- .NET doesnt have it; Access does.

I just see the whole MDB ***CARCASS*** as a mess of DAO crap that will
never work correctly.

I just KNOW that the best route to ADO is to use ADP. I mean-- they're
almost spelled the same.

aren't you guys tired of writign a couple of queries and just having
MDB crap out?

or does that honestly not happen to you guys?

I know of one bug that is so ****ing simple i'll never use MDB in my
life

try this in an mdb

select 'aaron'
union
select 'matt'

I mean-- why the hell doesn't that work? i mean seriously?

I just think that MDB is much more complex than it needs to be.

And microsoft needs to keep on taking FEATURES from MDB and recreating
them in SQL.

like the crosstab query wizard

the only thing that company has to do to save the world-- is to make a
crosstab query wizard in ADP.

for SQL 2005.

I mean-- then there wouldnt ever be a single reason to ever use MDB.

i mean-- what it comes down to is that you guys think that VBA is more
powerful than TSQL.

And I disagree with that so strongly-- i mean.. VBA is **CRAP**
compared to SQL Server user defined functions

and you kids can't even use stored procedurs in MDB.

you show me EXACTLY the code that it takes to execute a single sproc
from MDB and I'll show you the code from MDB

you guys just have been living in a cave the past 5 years

ADP has taken over the world kids

 

[Eva E.]

Hello All

I really highly reccomend using ADP over MDB. i mean-- performance is
a lot better with ADP.

I agree

I'm sorry that you dont know enough about ADP to do them successfully--
that doesn't mean that they're not the best solution in this situation.

That's true. Usually, changes are hard. You _must_ learn new techniques,
change your habits.... You can't criticize ADPs because you can't work
exactly the same way as you do with MDBs

And, Mary During the 2003 Summit, I asked about the future of ADPs. Same
last year. I miss this year Summit, but the answer is... in the wind? We
(people with a lot of work done with ADPs) _need_ to know the future of
them.

Regards

Eva Etxebeste
[MS MVP Access]