By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,304 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,304 IT Pros & Developers. It's quick & easy.

ADP Ready for Prime Time?

P: n/a
A while back I posted a message re. using an ADP file with a SQL Server back
end as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was that the ADP would be just
as, if not more, problematic; that ADO is far more limited than DAO,
requiring a lot of workarounds; and that it would be better to stay with a
seasoned MDB file than to switch to an ADP.

The database in question was inherited by me from someone else who
implemented things in a less than ideal way, and he, himself, converted it
from an existing database in another application. The database needs to be
revamped, and we are looking to rebuild it from the ground up with
additional functionality. So now the question is not staying with a seasoned
MDB as opposed to a new ADP; but, rather, which would be better for working
with a SQL Server back end, an MDB or ADP file?

Obviously if we go with an MDB file we can reuse many of the DAO routines,
as appropriate. But the main concern here is performance: which one will
provide the better performance, better data access, fewer strange anomalies,
and so on. Development time isn't the main concern.

The database currently relies in part on DAO and in part on stored
procedures. Obviously moving more towards stored procedures would be better,
but there will probably still be a significant amount of code in the front
end.

Any thoughts re. MDB vs. ADP or anything else related to this would be
appreciated.

Thanks,

Neil

Nov 13 '05 #1
Share this Question
Share on Google+
25 Replies


P: n/a
On Tue, 18 Jan 2005 07:38:50 GMT, "Neil Ginsberg" <nr*@nrgconsult.com> wrote:
A while back I posted a message re. using an ADP file with a SQL Server back
end as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was that the ADP would be just
as, if not more, problematic; that ADO is far more limited than DAO,
requiring a lot of workarounds; and that it would be better to stay with a
seasoned MDB file than to switch to an ADP.

The database in question was inherited by me from someone else who
implemented things in a less than ideal way, and he, himself, converted it
from an existing database in another application. The database needs to be
revamped, and we are looking to rebuild it from the ground up with
additional functionality. So now the question is not staying with a seasoned
MDB as opposed to a new ADP; but, rather, which would be better for working
with a SQL Server back end, an MDB or ADP file?

Obviously if we go with an MDB file we can reuse many of the DAO routines,
as appropriate. But the main concern here is performance: which one will
provide the better performance, better data access, fewer strange anomalies,
and so on. Development time isn't the main concern.
It's not just the develompment time, it's the unpredictability of runtime
behavior. It's just fastly harder to deliver verifiably high quality code
with ADPs. They're too much of a hodge podge of hastily patched together
kludges. ADPs and ADO also introduce at least as many performance bottlenecks
as performance improvments, and well-designed MDB client/server apps have been
known to perform really well.
The database currently relies in part on DAO and in part on stored
procedures. Obviously moving more towards stored procedures would be better,
but there will probably still be a significant amount of code in the front
end.
That's obvious, but not necessarily correct. Access works quite well using
forms bound to DAO recordsets on linked tables. if you deviate from that, you
might as well scrap Access altogether and implement custom GUIs in something
like C# or Java - of course, that might really be your best answer if time is
not an issue.

With respect to stored procedures performing universally better than Access
queries of linked tables, it's simply not the case. In most cases, an Access
query generates a prepared statement on the server side which functions much
like a stored procedure would. When the server sees the same prepared
statement signature again later on, it will realize it can use the same query
plan it computed for the statement previously - just like a stored procedurem
only without the headaches of being unable to update via an Access bound form.
Any thoughts re. MDB vs. ADP or anything else related to this would be
appreciated.


It's easier to get higher reliability and good performance with an MDB than an
ADP - period. If time is not an issue, scrap Access altogether and write
hand-optimized code in a "real" programming language.
Nov 13 '05 #2

P: n/a
Steve Jorgensen wrote:
It's not just the develompment time, it's the unpredictability of runtime behavior. [snip] It's easier to get higher reliability and good performance with an MDB than an ADP - period.


Steve,

What are the issues you're talking about here? Or can you point me to a
link that talks about some of them (if they've been discussed
extensively)? I haven't been around the CDMA 'hood for a while; I know
there are plenty of people who argue, and I agree with them, that
learning ADO because it's new doesn't make any sense, but I don't
remember reading much that talks about ADPs being inherintly more prone
to falling over at run time, or inherintly requiring more development
time (other than learning ADO).

This is in no way a troll or a challenge. I've been working on a
project that uses a MS SQL Server back end and an ADP front for the
last seven months, and I've run into very few things that I hadn't seen
in an MDB. Or at least that's my impression--maybe I'm just seeing
trees, though.

Jeremy

PS: I will tell you about the coolest run-time bug I've ever seen in
Access, though. It only happened in a2k3, and only with one particular
datasheet-viewed subform. And only very rarely and unpredicatbly. But
on one of our forms, if you got lucky, you could resize one of the
subforms by grabbing the scroll bar. At run time. We have a lot of
people using the database, and we've never gotten a report of anyone
finding this particular bug. But it sure scared the heck outta me when
I saw it!

Nov 13 '05 #3

P: n/a
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.com wrote:
Steve Jorgensen wrote:
It's not just the develompment time, it's the unpredictability of

runtime
behavior.

[snip]
It's easier to get higher reliability and good performance with an

MDB than an
ADP - period.


Steve,

What are the issues you're talking about here? Or can you point me to a
link that talks about some of them (if they've been discussed
extensively)? I haven't been around the CDMA 'hood for a while; I know
there are plenty of people who argue, and I agree with them, that
learning ADO because it's new doesn't make any sense, but I don't
remember reading much that talks about ADPs being inherintly more prone
to falling over at run time, or inherintly requiring more development
time (other than learning ADO).

This is in no way a troll or a challenge. I've been working on a
project that uses a MS SQL Server back end and an ADP front for the
last seven months, and I've run into very few things that I hadn't seen
in an MDB. Or at least that's my impression--maybe I'm just seeing
trees, though.


Well, for one thing, ADPs have a habit of making certain multi-table queries
uneditable. The first version of a query may be editable, but after some
random and seemingly trivial change in the query itself or afger the next
Access service pack or MDAC upgrade, it becomes uneditable again, and the
tweaking process starts all over again. I've finally gotten to the point
where I don't even try to use multi-table queries on bound forms in an ADP.
Either, I use combo boxes as lookups for data in related tables (uh yeah,
that'll help performance), or have to use separate forms for adding/editing
which is more clicks and work hassle for the user as well as more duplication
of form design elements.

For another thing, there is a bug that was supposedly fixed, but actually
isn't in which sometimes an ORDER BY clause in a subform or subreport causes
an error. To fix it, it is necessary to remove the table name qualifier from
the expression in the ORDER BY clause. Of course, the query builder always
puts the table qualifiers on, and it's never obvious what's broken without
some digging.

In ADPs, any time you do anything slightly out of the ordinary in a
server-side query, the ADP gets confused. For instance, in my MDBs, sometimes
I like to have a view that references a table in another database on the
server. I do this, for instance, to put tables frequently used to hold
temporary data in a database with TRUNCATE LOG ON CHECKPOINT so my backups
aren't filled mostly with irrelevant adding and deleting of temporary data.
When you try to bind an ADP to this, it gets really confused beacause ADO is
smart enough to try to look at the underlying tables, but not smart enough to
look at them in another database on the server.

The ADP looking behind your stored procedures and views also makes it hard to
do things that work fine in an MDB such as using a view for security where the
user has permissions granted via the view that they don't haveon the tables
directly. If you know the issue and are -very- careful, you can work around
this. This is in the category of ADPs trying to think for you and thus making
it very hard to use what should be typical rule enforcement on the back-end.

I can't remember all the other issues right now, but I remember that there are
several more.
Nov 13 '05 #4

P: n/a
Steve Jorgensen wrote:
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.com wrote:

<snip>
The ADP looking behind your stored procedures and views also makes it
hard to do things that work fine in an MDB such as using a view for
security where the user has permissions granted via the view that
they don't haveon the tables directly. If you know the issue and are
-very- careful, you can work around this. This is in the category of
ADPs trying to think for you and thus making it very hard to use what
should be typical rule enforcement on the back-end.


I haven't had any problems implementing row level security (eg. a
manager can only see the employee they manage as defined in a table).
Can you elaborate what problems you encountered?

Br@dley
Nov 13 '05 #5

P: n/a
Neil Ginsberg wrote:
Any thoughts re. MDB vs. ADP or anything else related to this would be appreciated.


If you bind your forms then your users must have SQL table permissions.
If they have table permissions what is to prevent them from using these
permissions directly on the table, in your database or another?
One can prevent this by using application roles. Unfortunately ADPs and
application roles combined are a cruel joke, because ADPs have various
connections for various interfaces and one cannot predict what
connection will be used where.
And if you don't bind your forms why use Access, ... for its limited
forms, its bizarre idiosyncracies, its memory glut, its archaic
scripting language? Maybe for its reports? MAYBE!

Access is dead. It's fine with JET; but JET has been retired and is
rusting out slowly on one of those big California desert airfields. As
a front end for other db engines it's a joke, a bad joke.

What are those lines from "Red Rubber Ball"?
"I bought my ticket with my tears,
That's all I'm gonna spend."

Nov 13 '05 #6

P: n/a
ly******@yahoo.com wrote:
Neil Ginsberg wrote:
Any thoughts re. MDB vs. ADP or anything else related to this would
be appreciated.
If you bind your forms then your users must have SQL table
permissions. If they have table permissions what is to prevent them
from using these permissions directly on the table, in your database
or another?
Always use views/stored procedures.
One can prevent this by using application roles. Unfortunately ADPs
and application roles combined are a cruel joke, because ADPs have
various connections for various interfaces and one cannot predict what
connection will be used where.
And if you don't bind your forms why use Access, ... for its limited
forms, its bizarre idiosyncracies, its memory glut, its archaic
scripting language? Maybe for its reports? MAYBE!

Access is dead


The system I converted from MDB to ADP is going great. There were a few
hurdles but I've managed to work around them all (esp. using A2000 which
was released before SQL2000 and so won't allow updatable views unless
you specify WITH VIEW_METADATA)

Br@dley
Nov 13 '05 #7

P: n/a
There seem to be indications that Jet isn't quite "rusting away" despite it
having once been declared "in maintenance mode" -- it has been updated since
that declaration and Jet-ODBC-Server is now recommended over
ADP-ADODB-Server by knowledgeable Microsoft insiders.

In fact, one poster in CDMA said that Microsoft had told him that ADP-ADO
was being "deprecated" in the next version. But I have not personally seen
such a statement from Microsoft.

The old crystal ball is cloudy, at best, but it's clear from what Microsoft
has publicly stated that there is going to be another release of Microsoft
Office and Access is going to be part of it.

Larry Linson
Microsoft Access MVP

<ly******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Neil Ginsberg wrote:
Any thoughts re. MDB vs. ADP or anything else related to this would

be
appreciated.


If you bind your forms then your users must have SQL table permissions.
If they have table permissions what is to prevent them from using these
permissions directly on the table, in your database or another?
One can prevent this by using application roles. Unfortunately ADPs and
application roles combined are a cruel joke, because ADPs have various
connections for various interfaces and one cannot predict what
connection will be used where.
And if you don't bind your forms why use Access, ... for its limited
forms, its bizarre idiosyncracies, its memory glut, its archaic
scripting language? Maybe for its reports? MAYBE!

Access is dead. It's fine with JET; but JET has been retired and is
rusting out slowly on one of those big California desert airfields. As
a front end for other db engines it's a joke, a bad joke.

What are those lines from "Red Rubber Ball"?
"I bought my ticket with my tears,
That's all I'm gonna spend."

Nov 13 '05 #8

P: n/a
On Wed, 19 Jan 2005 12:08:32 +1100, <Br@dley> wrote:
Steve Jorgensen wrote:
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.com wrote:

<snip>
The ADP looking behind your stored procedures and views also makes it
hard to do things that work fine in an MDB such as using a view for
security where the user has permissions granted via the view that
they don't haveon the tables directly. If you know the issue and are
-very- careful, you can work around this. This is in the category of
ADPs trying to think for you and thus making it very hard to use what
should be typical rule enforcement on the back-end.


I haven't had any problems implementing row level security (eg. a
manager can only see the employee they manage as defined in a table).
Can you elaborate what problems you encountered?

Br@dley


If you use the WITH VIEW METADATA option, then using views for security works,
but only assumning there is only one table behind the view. If there is more
than one table behind the veiw, then either no key fields are reported, or too
many key fields are reported. The first case slows down the recordsets and
ensures that they are not editable even if you have an INSTEAD OF trigger that
should make it so. If too many key fields are reported, then rows can seem to
disappear after editing. Either way, it's screwey.

The work-around is to use only single-table views for security, then make
multi-table views that query from those views if necessary. This still
thwarts any attempts to use INSTEAD OF triggers on the multi-table view
because since that view does not have WITH VIEW METADATA, the ADP will go
around it and try to update the single-table views directly.
Nov 13 '05 #9

P: n/a
Well, Steve... for a "nice little desktop database", Access has given us a
"pretty good ride"... it was first released in October 1992, so it has
lasted over 12 years, so far, and it's not nearly dead yet.

But, even if it were... 12 years is a significant lifetime for a software
product.

(And, another thing, I made a good living just out of Access for most of
those 12 years. That, too, is impressive for a "nice little desktop
database". I am convinced it was so much better than expected that it
surprised even its developers.)

Larry
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:cg********************************@4ax.com...
On Tue, 18 Jan 2005 23:25:13 -0600, "Larry Linson" <bo*****@localhost.not> wrote:
There seem to be indications that Jet isn't quite "rusting away" despite ithaving once been declared "in maintenance mode" -- it has been updated sincethat declaration and Jet-ODBC-Server is now recommended over
ADP-ADODB-Server by knowledgeable Microsoft insiders.

In fact, one poster in CDMA said that Microsoft had told him that ADP-ADO
was being "deprecated" in the next version. But I have not personally seensuch a statement from Microsoft.

The old crystal ball is cloudy, at best, but it's clear from what Microsofthas publicly stated that there is going to be another release of MicrosoftOffice and Access is going to be part of it.

Larry Linson
Microsoft Access MVP
My concern is that since it seems clear that Access will lag the rest of

MS Office in terms of implementation as a .NET base app, it will get increasingly orphaned by developers migrating from VB/VBA development as advances in the .NET UI technology simultaneously narrow the functionality gap with Access in terms of things like easy reporting and continuous bound forms.

If that is the trend, it might be better to be leading it than following

it.
Nov 13 '05 #10

P: n/a
Br
Steve Jorgensen wrote:
On Wed, 19 Jan 2005 12:08:32 +1100, <Br@dley> wrote:
Steve Jorgensen wrote:
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.com wrote:

<snip>
The ADP looking behind your stored procedures and views also makes
it hard to do things that work fine in an MDB such as using a view
for security where the user has permissions granted via the view
that they don't haveon the tables directly. If you know the issue
and are -very- careful, you can work around this. This is in the
category of ADPs trying to think for you and thus making it very
hard to use what should be typical rule enforcement on the back-end.


I haven't had any problems implementing row level security (eg. a
manager can only see the employee they manage as defined in a table).
Can you elaborate what problems you encountered?

Br@dley


If you use the WITH VIEW METADATA option, then using views for
security works, but only assumning there is only one table behind the
view. If there is more than one table behind the veiw, then either
no key fields are reported, or too many key fields are reported. The
first case slows down the recordsets and ensures that they are not
editable even if you have an INSTEAD OF trigger that should make it
so. If too many key fields are reported, then rows can seem to
disappear after editing. Either way, it's screwey.

The work-around is to use only single-table views for security, then
make multi-table views that query from those views if necessary.
This still thwarts any attempts to use INSTEAD OF triggers on the
multi-table view because since that view does not have WITH VIEW
METADATA, the ADP will go around it and try to update the
single-table views directly.


Oh I agree it's a bit screwy... but I seemed to get it working for what
I needed... (after some hair pulling trying to work out what was going
on:)

Br@dley
Nov 13 '05 #11

P: n/a
Absolutely - Access has been an incredible tool, and still is.

On Thu, 20 Jan 2005 05:53:08 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
Well, Steve... for a "nice little desktop database", Access has given us a
"pretty good ride"... it was first released in October 1992, so it has
lasted over 12 years, so far, and it's not nearly dead yet.

But, even if it were... 12 years is a significant lifetime for a software
product.

(And, another thing, I made a good living just out of Access for most of
those 12 years. That, too, is impressive for a "nice little desktop
database". I am convinced it was so much better than expected that it
surprised even its developers.)

Larry
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:cg********************************@4ax.com.. .
On Tue, 18 Jan 2005 23:25:13 -0600, "Larry Linson"

<bo*****@localhost.not>
wrote:
>There seem to be indications that Jet isn't quite "rusting away" despiteit >having once been declared "in maintenance mode" -- it has been updatedsince >that declaration and Jet-ODBC-Server is now recommended over
>ADP-ADODB-Server by knowledgeable Microsoft insiders.
>
>In fact, one poster in CDMA said that Microsoft had told him that ADP-ADO
>was being "deprecated" in the next version. But I have not personallyseen >such a statement from Microsoft.
>
>The old crystal ball is cloudy, at best, but it's clear from whatMicrosoft >has publicly stated that there is going to be another release ofMicrosoft >Office and Access is going to be part of it.
>
> Larry Linson
> Microsoft Access MVP


My concern is that since it seems clear that Access will lag the rest of

MS
Office in terms of implementation as a .NET base app, it will get

increasingly
orphaned by developers migrating from VB/VBA development as advances in

the
.NET UI technology simultaneously narrow the functionality gap with Access

in
terms of things like easy reporting and continuous bound forms.

If that is the trend, it might be better to be leading it than following

it.


Nov 13 '05 #12

P: n/a
Thank you for the discussion about ADP because I was considering using it
but now realize that what I have is superior to both ADP and MDB's with
linked tables.

Think outside of the box. Linked tables across the network are bad, bad,
bad, bad.... So don't use them.

What is the fastest way to retrieve data using an Access MDB? A query pass
thru. Can you bind a form to a QPT? Yes. What happens if you try to update a
field in a form bound to a QPT? Hmm - not good - it's not updatable.

Let's see. Can you create a query (make table or append) that uses the QPT
as its source? Yes. Hmm. Now you have a way to get data from the backend to
a front-end local table very quickly. Can you bind a form to the local
table? Yes. What happens to the backend data when you update the front-end
table through the form? Nothing. That's not good. Oh but wait!

What if you delegate the AfterUpdate, AfterInsert, Delete and
AfterDelConfirm events to a common form handler that references a VB DLL
that repeats the updates, inserts and deletes to the backend? In the length
of time for the user to scroll from the updated row to the next row, the
backend is updated even though there is no link to the backend.

What you wind up with is a very very very fast 2 tier client server
solution. Access is used on the front end for programming and for managing
disconnected datasets (makes you think about ADO.NET). If your really
skillful, you can gracefully manage concurrency conflicts (hint: think
timestamps) and deal with autoincrement keys (stored procedures for inserts
that pass back @@IDENTITY). You could even create a reusable table driven
architecture that completely hides the complexity of the data marshalling.

Again, the discussions about ADP versus MDB's has been great -- particularly
the part about how ADP's manage connections.

Jim Rand

"Larry Linson" <bo*****@localhost.not> wrote in message
news:8HHHd.122$BL3.47@trnddc01...
Well, Steve... for a "nice little desktop database", Access has given us a
"pretty good ride"... it was first released in October 1992, so it has
lasted over 12 years, so far, and it's not nearly dead yet.

But, even if it were... 12 years is a significant lifetime for a software
product.

(And, another thing, I made a good living just out of Access for most of
those 12 years. That, too, is impressive for a "nice little desktop
database". I am convinced it was so much better than expected that it
surprised even its developers.)

Larry
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:cg********************************@4ax.com...
On Tue, 18 Jan 2005 23:25:13 -0600, "Larry Linson" <bo*****@localhost.not>
wrote:
There seem to be indications that Jet isn't quite "rusting away" despite it
having once been declared "in maintenance mode" -- it has been updated sincethat declaration and Jet-ODBC-Server is now recommended over
ADP-ADODB-Server by knowledgeable Microsoft insiders.

In fact, one poster in CDMA said that Microsoft had told him that
ADP-ADOwas being "deprecated" in the next version. But I have not personally
seensuch a statement from Microsoft.

The old crystal ball is cloudy, at best, but it's clear from what Microsofthas publicly stated that there is going to be another release of MicrosoftOffice and Access is going to be part of it.

Larry Linson
Microsoft Access MVP


My concern is that since it seems clear that Access will lag the rest of

MS
Office in terms of implementation as a .NET base app, it will get

increasingly
orphaned by developers migrating from VB/VBA development as advances in

the
.NET UI technology simultaneously narrow the functionality gap with

Access in
terms of things like easy reporting and continuous bound forms.

If that is the trend, it might be better to be leading it than following

it.

Nov 13 '05 #13

P: n/a
Hello Neil:
You wrote in conference
comp.databases.ms-access,microsoft.public.access.adp.sqlserver,micro soft.public.access.odbcclientsvr
on Tue, 18 Jan 2005 07:38:50 GMT:

NG> Any thoughts re. MDB vs. ADP or anything else related to this would be
NG> appreciated.

You will write the best code in the environment where you are more
proficient.

If you are equally proficient in MDB and ADP, probably ADP is better because
it's closer to today's approach.

If you choose ADP, one word of advise will be this: avoid using ADO
directly. Don't force Access into using ADO; rather, have Access do the job
with its own means - make everything bound, no "manual" manipulations with
the data; and don't ever touch form.recordset.
Vadim Rapp

Nov 13 '05 #14

P: n/a
On Thu, 20 Jan 2005 23:12:08 -0600, "Vadim Rapp" <vr@myrealbox.nospam.com>
wrote:
Hello Neil:
You wrote in conference
comp.databases.ms-access,microsoft.public.access.adp.sqlserver,micro soft.public.access.odbcclientsvr
on Tue, 18 Jan 2005 07:38:50 GMT:

NG> Any thoughts re. MDB vs. ADP or anything else related to this would be
NG> appreciated.

You will write the best code in the environment where you are more
proficient.

If you are equally proficient in MDB and ADP, probably ADP is better because
it's closer to today's approach.

If you choose ADP, one word of advise will be this: avoid using ADO
directly. Don't force Access into using ADO; rather, have Access do the job
with its own means - make everything bound, no "manual" manipulations with
the data; and don't ever touch form.recordset.
Your 2 statements seem to contradict each other. You say to avoid using the
API, but you say to use ADO because it's closer to today's approach (I assume
you mean because it uses ADO by default).

I can't agree with that. If you mostly avoid the API, it's better to use the
more mature and reliable Access project type, and that's the MDB hands-down.
Furthermore, I often use ADO in MDB applications, so the MDB doesn't get in
the way on that score either.

Furthermore, I have had notoriously poor luck with ADPs usaing purely bound
data designs because so many things that are not a problem in MDBs are so
touchy in ADPs. I have to add more complexity and write more code to work
around these things, so ADPs certainly don't help facilitate the "everything
bound approach".


Vadim Rapp


Nov 13 '05 #15

P: n/a
Hello Steve:
You wrote in conference
comp.databases.ms-access,microsoft.public.access.adp.sqlserver,micro soft.public.access.odbcclientsvr
on Thu, 20 Jan 2005 21:41:29 -0800:

SJ> On Thu, 20 Jan 2005 23:12:08 -0600, "Vadim Rapp"
<vr@myrealbox.nospam.com>
SJ> wrote:

SJ>> If you are equally proficient in MDB and ADP, probably ADP is better
SJ>> because it's closer to today's approach.
SJ>>
SJ>> If you choose ADP, one word of advise will be this: avoid using ADO
SJ>> directly. Don't force Access into using ADO; rather, have Access do
SJ>> the job with its own means - make everything bound, no "manual"
SJ>> manipulations with the data; and don't ever touch form.recordset.

SJ> Your 2 statements seem to contradict each other. You say to avoid
SJ> using the API, but you say to use ADO because it's closer to today's
SJ> approach (I assume you mean because it uses ADO by default).
Not at all. I did not even mention the word API, so I assume you meant ADO.

I say to use ADP because ADP is based on ADO, which is closer to today's
approach, also it's more "direct" way to work with sql server. But not to
use ADO directly. Use Access/ADP methods, and Access will use ADO as it
wants to.
SJ> Furthermore, I have had notoriously poor luck with ADPs usaing purely
SJ> bound data designs because so many things that are not a problem in
SJ> MDBs are so touchy in ADPs.

As I wrote, the key to success is using one tool that you are most
proficient with. If you are using two very related API's, you are doomed for
the problems, because you can't avoid applying one approach to another API.
I bet that if you started using _only_ ADP, without even touching MDB, in
several weeks you would suddenly find that you and Access have adjusted to
each other, and the problems mysteriously dissolved. Your sub-conscience
would learn to choose the good (for the ADP) methods and avoid the bad ones.
And then in several more weeks, given the opportunity, you would find that
so many things that are not a problem in ADPs are so touchy in MDBs.

Vadim

Nov 13 '05 #16

P: n/a
ADPs are ready for primetime.

but they're difficult to implement, especially things like this:

1) forms against views is difficult/unpredictable
2) stored procs bind well to form, but it isn't very well documented
3) sql server doesn't have a crosstab query (yet)

hth
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:sf********************************@4ax.com...
On Tue, 18 Jan 2005 07:38:50 GMT, "Neil Ginsberg" <nr*@nrgconsult.com> wrote:
A while back I posted a message re. using an ADP file with a SQL Server backend as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was that the ADP would be just
as, if not more, problematic; that ADO is far more limited than DAO,
requiring a lot of workarounds; and that it would be better to stay with aseasoned MDB file than to switch to an ADP.

The database in question was inherited by me from someone else who
implemented things in a less than ideal way, and he, himself, converted itfrom an existing database in another application. The database needs to berevamped, and we are looking to rebuild it from the ground up with
additional functionality. So now the question is not staying with a seasonedMDB as opposed to a new ADP; but, rather, which would be better for workingwith a SQL Server back end, an MDB or ADP file?

Obviously if we go with an MDB file we can reuse many of the DAO routines,as appropriate. But the main concern here is performance: which one will
provide the better performance, better data access, fewer strange anomalies,and so on. Development time isn't the main concern.
It's not just the develompment time, it's the unpredictability of runtime
behavior. It's just fastly harder to deliver verifiably high quality code
with ADPs. They're too much of a hodge podge of hastily patched together
kludges. ADPs and ADO also introduce at least as many performance

bottlenecks as performance improvments, and well-designed MDB client/server apps have been known to perform really well.
The database currently relies in part on DAO and in part on stored
procedures. Obviously moving more towards stored procedures would be better,but there will probably still be a significant amount of code in the frontend.
That's obvious, but not necessarily correct. Access works quite well

using forms bound to DAO recordsets on linked tables. if you deviate from that, you might as well scrap Access altogether and implement custom GUIs in something like C# or Java - of course, that might really be your best answer if time is not an issue.

With respect to stored procedures performing universally better than Access queries of linked tables, it's simply not the case. In most cases, an Access query generates a prepared statement on the server side which functions much like a stored procedure would. When the server sees the same prepared
statement signature again later on, it will realize it can use the same query plan it computed for the statement previously - just like a stored procedurem only without the headaches of being unable to update via an Access bound form.
Any thoughts re. MDB vs. ADP or anything else related to this would be
appreciated.
It's easier to get higher reliability and good performance with an MDB

than an ADP - period. If time is not an issue, scrap Access altogether and write
hand-optimized code in a "real" programming language.

Nov 13 '05 #17

P: n/a
MDB is dead.

ADP is alive and well.

ADP reports SLAUGHTER crystal reports in usability.

ADP is an awesome platform for data entry
<ly******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Neil Ginsberg wrote:
Any thoughts re. MDB vs. ADP or anything else related to this would

be
appreciated.


If you bind your forms then your users must have SQL table permissions.
If they have table permissions what is to prevent them from using these
permissions directly on the table, in your database or another?
One can prevent this by using application roles. Unfortunately ADPs and
application roles combined are a cruel joke, because ADPs have various
connections for various interfaces and one cannot predict what
connection will be used where.
And if you don't bind your forms why use Access, ... for its limited
forms, its bizarre idiosyncracies, its memory glut, its archaic
scripting language? Maybe for its reports? MAYBE!

Access is dead. It's fine with JET; but JET has been retired and is
rusting out slowly on one of those big California desert airfields. As
a front end for other db engines it's a joke, a bad joke.

What are those lines from "Red Rubber Ball"?
"I bought my ticket with my tears,
That's all I'm gonna spend."

Nov 13 '05 #18

P: n/a
views are a pain to update

so basically use base tables

and get this

1) only load a single record at a time and this should cut down on
complexity
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:n8********************************@4ax.com...
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.com wrote:
Steve Jorgensen wrote:
It's not just the develompment time, it's the unpredictability ofruntime
behavior.

[snip]
It's easier to get higher reliability and good performance with an

MDB than an
ADP - period.


Steve,

What are the issues you're talking about here? Or can you point me to a
link that talks about some of them (if they've been discussed
extensively)? I haven't been around the CDMA 'hood for a while; I know
there are plenty of people who argue, and I agree with them, that
learning ADO because it's new doesn't make any sense, but I don't
remember reading much that talks about ADPs being inherintly more prone
to falling over at run time, or inherintly requiring more development
time (other than learning ADO).

This is in no way a troll or a challenge. I've been working on a
project that uses a MS SQL Server back end and an ADP front for the
last seven months, and I've run into very few things that I hadn't seen
in an MDB. Or at least that's my impression--maybe I'm just seeing
trees, though.


Well, for one thing, ADPs have a habit of making certain multi-table

queries uneditable. The first version of a query may be editable, but after some
random and seemingly trivial change in the query itself or afger the next
Access service pack or MDAC upgrade, it becomes uneditable again, and the
tweaking process starts all over again. I've finally gotten to the point
where I don't even try to use multi-table queries on bound forms in an ADP. Either, I use combo boxes as lookups for data in related tables (uh yeah,
that'll help performance), or have to use separate forms for adding/editing which is more clicks and work hassle for the user as well as more duplication of form design elements.

For another thing, there is a bug that was supposedly fixed, but actually
isn't in which sometimes an ORDER BY clause in a subform or subreport causes an error. To fix it, it is necessary to remove the table name qualifier from the expression in the ORDER BY clause. Of course, the query builder always puts the table qualifiers on, and it's never obvious what's broken without
some digging.

In ADPs, any time you do anything slightly out of the ordinary in a
server-side query, the ADP gets confused. For instance, in my MDBs, sometimes I like to have a view that references a table in another database on the
server. I do this, for instance, to put tables frequently used to hold
temporary data in a database with TRUNCATE LOG ON CHECKPOINT so my backups
aren't filled mostly with irrelevant adding and deleting of temporary data. When you try to bind an ADP to this, it gets really confused beacause ADO is smart enough to try to look at the underlying tables, but not smart enough to look at them in another database on the server.

The ADP looking behind your stored procedures and views also makes it hard to do things that work fine in an MDB such as using a view for security where the user has permissions granted via the view that they don't haveon the tables directly. If you know the issue and are -very- careful, you can work around this. This is in the category of ADPs trying to think for you and thus making it very hard to use what should be typical rule enforcement on the back-end.
I can't remember all the other issues right now, but I remember that there are several more.

Nov 13 '05 #19

P: n/a
i believe that i am the person that made the depecrated statement... not
sure

ADP rocks.

Microsoft isn't taking this platform seriously; and they need to.

The bugginess of ADP is probably holding up a LOT of SQL Server sales, I
would assume.
"Larry Linson" <bo*****@localhost.not> wrote in message
news:#U**************@tk2msftngp13.phx.gbl...
There seem to be indications that Jet isn't quite "rusting away" despite it having once been declared "in maintenance mode" -- it has been updated since that declaration and Jet-ODBC-Server is now recommended over
ADP-ADODB-Server by knowledgeable Microsoft insiders.

In fact, one poster in CDMA said that Microsoft had told him that ADP-ADO
was being "deprecated" in the next version. But I have not personally seen
such a statement from Microsoft.

The old crystal ball is cloudy, at best, but it's clear from what Microsoft has publicly stated that there is going to be another release of Microsoft
Office and Access is going to be part of it.

Larry Linson
Microsoft Access MVP

<ly******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Neil Ginsberg wrote:
Any thoughts re. MDB vs. ADP or anything else related to this would

be
appreciated.


If you bind your forms then your users must have SQL table permissions.
If they have table permissions what is to prevent them from using these
permissions directly on the table, in your database or another?
One can prevent this by using application roles. Unfortunately ADPs and
application roles combined are a cruel joke, because ADPs have various
connections for various interfaces and one cannot predict what
connection will be used where.
And if you don't bind your forms why use Access, ... for its limited
forms, its bizarre idiosyncracies, its memory glut, its archaic
scripting language? Maybe for its reports? MAYBE!

Access is dead. It's fine with JET; but JET has been retired and is
rusting out slowly on one of those big California desert airfields. As
a front end for other db engines it's a joke, a bad joke.

What are those lines from "Red Rubber Ball"?
"I bought my ticket with my tears,
That's all I'm gonna spend."


Nov 13 '05 #20

P: n/a
So why were we using Access again? Being able to edit in continuous forms is
one way we make user interfaces easier to use, by not making user have to
switch contexts all the time. It's also a way we can use to keep from
duplicating the same controls on multiple forms, this keeping application
complexity down.

If I'm designing an application where you only update one row at a time, I
might as well use a more powerful programming language with weaker bound UI
capabilities.

On Wed, 26 Jan 2005 14:39:49 -0800, <aa*********@hotmail.com> wrote:
views are a pain to update

so basically use base tables

and get this

1) only load a single record at a time and this should cut down on
complexity
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:n8********************************@4ax.com.. .
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.com wrote:
>Steve Jorgensen wrote:
>> It's not just the develompment time, it's the unpredictability of
>runtime
>> behavior.
>[snip]
>> It's easier to get higher reliability and good performance with an
>MDB than an
>> ADP - period.
>
>Steve,
>
>What are the issues you're talking about here? Or can you point me to a
>link that talks about some of them (if they've been discussed
>extensively)? I haven't been around the CDMA 'hood for a while; I know
>there are plenty of people who argue, and I agree with them, that
>learning ADO because it's new doesn't make any sense, but I don't
>remember reading much that talks about ADPs being inherintly more prone
>to falling over at run time, or inherintly requiring more development
>time (other than learning ADO).
>
>This is in no way a troll or a challenge. I've been working on a
>project that uses a MS SQL Server back end and an ADP front for the
>last seven months, and I've run into very few things that I hadn't seen
>in an MDB. Or at least that's my impression--maybe I'm just seeing
>trees, though.


Well, for one thing, ADPs have a habit of making certain multi-table

queries
uneditable. The first version of a query may be editable, but after some
random and seemingly trivial change in the query itself or afger the next
Access service pack or MDAC upgrade, it becomes uneditable again, and the
tweaking process starts all over again. I've finally gotten to the point
where I don't even try to use multi-table queries on bound forms in an

ADP.
Either, I use combo boxes as lookups for data in related tables (uh yeah,
that'll help performance), or have to use separate forms for

adding/editing
which is more clicks and work hassle for the user as well as more

duplication
of form design elements.

For another thing, there is a bug that was supposedly fixed, but actually
isn't in which sometimes an ORDER BY clause in a subform or subreport

causes
an error. To fix it, it is necessary to remove the table name qualifier

from
the expression in the ORDER BY clause. Of course, the query builder

always
puts the table qualifiers on, and it's never obvious what's broken without
some digging.

In ADPs, any time you do anything slightly out of the ordinary in a
server-side query, the ADP gets confused. For instance, in my MDBs,

sometimes
I like to have a view that references a table in another database on the
server. I do this, for instance, to put tables frequently used to hold
temporary data in a database with TRUNCATE LOG ON CHECKPOINT so my backups
aren't filled mostly with irrelevant adding and deleting of temporary

data.
When you try to bind an ADP to this, it gets really confused beacause ADO

is
smart enough to try to look at the underlying tables, but not smart enough

to
look at them in another database on the server.

The ADP looking behind your stored procedures and views also makes it hard

to
do things that work fine in an MDB such as using a view for security where

the
user has permissions granted via the view that they don't haveon the

tables
directly. If you know the issue and are -very- careful, you can work

around
this. This is in the category of ADPs trying to think for you and thus

making
it very hard to use what should be typical rule enforcement on the

back-end.

I can't remember all the other issues right now, but I remember that there

are
several more.


Nov 13 '05 #21

P: n/a
So basically, ADPs are a way to do what MDBs can already do pretty well, only
with more things that don't work or are very flakey. OK, the ability to edit
server-side objects through the Access UI is great, but you can use an ADP for
server-side object development, and use the MDB for all the production
front-end forms, reports, and code.

On Wed, 26 Jan 2005 14:38:01 -0800, <aa*********@hotmail.com> wrote:
ADPs are ready for primetime.

but they're difficult to implement, especially things like this:

1) forms against views is difficult/unpredictable
2) stored procs bind well to form, but it isn't very well documented
3) sql server doesn't have a crosstab query (yet)

hth
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:sf********************************@4ax.com.. .
On Tue, 18 Jan 2005 07:38:50 GMT, "Neil Ginsberg" <nr*@nrgconsult.com>

wrote:
>A while back I posted a message re. using an ADP file with a SQL Serverback >end as opposed to MDB file with linked tables, thinking that the ADP file
>would be less problematic. The input I got was that the ADP would be just
>as, if not more, problematic; that ADO is far more limited than DAO,
>requiring a lot of workarounds; and that it would be better to stay witha >seasoned MDB file than to switch to an ADP.
>
>The database in question was inherited by me from someone else who
>implemented things in a less than ideal way, and he, himself, convertedit >from an existing database in another application. The database needs tobe >revamped, and we are looking to rebuild it from the ground up with
>additional functionality. So now the question is not staying with aseasoned >MDB as opposed to a new ADP; but, rather, which would be better forworking >with a SQL Server back end, an MDB or ADP file?
>
>Obviously if we go with an MDB file we can reuse many of the DAOroutines, >as appropriate. But the main concern here is performance: which one will
>provide the better performance, better data access, fewer strangeanomalies, >and so on. Development time isn't the main concern.


It's not just the develompment time, it's the unpredictability of runtime
behavior. It's just fastly harder to deliver verifiably high quality code
with ADPs. They're too much of a hodge podge of hastily patched together
kludges. ADPs and ADO also introduce at least as many performance

bottlenecks
as performance improvments, and well-designed MDB client/server apps have

been
known to perform really well.
>The database currently relies in part on DAO and in part on stored
>procedures. Obviously moving more towards stored procedures would bebetter, >but there will probably still be a significant amount of code in thefront >end.


That's obvious, but not necessarily correct. Access works quite well

using
forms bound to DAO recordsets on linked tables. if you deviate from that,

you
might as well scrap Access altogether and implement custom GUIs in

something
like C# or Java - of course, that might really be your best answer if time

is
not an issue.

With respect to stored procedures performing universally better than

Access
queries of linked tables, it's simply not the case. In most cases, an

Access
query generates a prepared statement on the server side which functions

much
like a stored procedure would. When the server sees the same prepared
statement signature again later on, it will realize it can use the same

query
plan it computed for the statement previously - just like a stored

procedurem
only without the headaches of being unable to update via an Access bound

form.
>Any thoughts re. MDB vs. ADP or anything else related to this would be
>appreciated.


It's easier to get higher reliability and good performance with an MDB

than an
ADP - period. If time is not an issue, scrap Access altogether and write
hand-optimized code in a "real" programming language.


Nov 13 '05 #22

P: n/a
On Wed, 26 Jan 2005 14:41:56 -0800, <aa*********@hotmail.com> wrote:
i believe that i am the person that made the depecrated statement... not
sure

ADP rocks.

Microsoft isn't taking this platform seriously; and they need to.


ADPs might rock if they did and fixed what's wrong with ADPs today, but MS
won't, so ADPs don't.

Nov 13 '05 #23

P: n/a
<aa*********@hotmail.com> wrote in news:#v0xzf$AFHA.2392
@TK2MSFTNGP14.phx.gbl:
1) only load a single record at a time and this should cut down on
complexity


Yes, I agree. ADPs are way better when one does not use the parts which don't
work consistently well. Throw out forms and reports and you have a
masterpiece.
This strategy also cuts down time spent in learning to almost nothing.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
Nov 13 '05 #24

P: n/a
"Lyle Fairfield" wrote
Yes, I agree. ADPs are way better
when one does not use the parts
which don't work consistently well.
Throw out forms and reports and
you have a masterpiece. This strategy
also cuts down time spent in learning
to almost nothing.


ROFL!
Nov 13 '05 #25

P: n/a
Lyle Fairfield <Lo******@FFDBA.Com> wrote in
news:Xn*******************@216.221.81.119:

Yes, I agree. ADPs are way better when one does not use the parts
which don't work consistently well. Throw out forms and reports and
you have a masterpiece.


What is left?

Tim F

Nov 13 '05 #26

This discussion thread is closed

Replies have been disabled for this discussion.