accdb vs adp

[briank]

I have inherited an A2007 adb with a connection to SS2K5. I have recently
begun trying to follow the various VBA code compiled by four different
consultants which is a bit of a challenge in itself. Since my company's
needs have changed over the past months I feel that it would be cost
effective to simply create a new db from scratch. However, I am not sure of
the advantages of ADP or simply sticking with ACCDB. I would appreciate
feedback on this scenario. TY.

 

[GBA]

I seem to be in a minority in believing that the ever increasing power of the
PC, plus that LANs generally are not bandwidth constrained - means that the
requirement to move to sqlserver is pushed further and further away each year.

There seems to be an overwhelming fixed opinion that if any Access problem
exists it is viewed that the solution is to 'up size it' to sqlserver....
Which is to say the opinion is to move to sqlserver and not move away from
sqlserver as most presume growth.

There are several good & valid reasons to move to sqlserver - it is a very
solid product - - so at the same time there would need to be solid reasons to
move away from it. If your organization is running sqlserver routinely for
several other db apps then one would tend to think you should stay with it.

On the otherhand you kind of imply your company is downsizing in various
dimensions - and going strictly PC/LAN environment is certainly more frugal
than managing sqlserver. Fundamentally can Access do the job? that's kind
of yes/no...and then which is less/more painful? managing the exiting
environment vs the effort to downsize it off sqlserver....

Am not sure any generic advice can really assess your situation from a
forum...

 

[briank]

Thank you for your thoughts on this situation. Currently my employee seems
to be firmly in place with using SQL 2005. With this in mind I am now trying
to determine which is best with the SQL connection, either accdb or adp. I
hear opinions supporting each way but I'm trying to determine what factual
evidence is the most swaying. I appreciate any thoughts on this.

 

[Sylvain Lafontaine]

There is no factual evidence. You have said it yourself: you are trying to
follow the code written by four different peoples and above that, maybe that
you'll soon add the code of a fifth person.

Unless the employees at your company are working for free, these kinds of
parameters are much more important than speaking about increasing PC powers
and LAN bandwidth.

There are other considerations as well such as the security and
confidentialiy of your data. Only you can know about how much these other
considerations might be important to your company and they will/might a
great impact on the decision of chosing between either ACCDB or ADP or a
third solution or even a mix of multiple solutions.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Larry Linson]

I have inherited an A2007 adb with a connection to SS2K5. I have recently
begun trying to follow the various VBA code compiled by four different
consultants which is a bit of a challenge in itself. Since my company's
needs have changed over the past months I feel that it would be cost
effective to simply create a new db from scratch. However, I am not sure of
the advantages of ADP or simply sticking with ACCDB. I would appreciate
feedback on this scenario. TY.

At one time, the Access Product Group at Microsoft recommended ADP for
direct connection to SQL Server. That is no longer the case (partly because
the OLEdb technology on which ADP and ADO are based has been supplanted by
successor technology based on ADO.NET -- which shares with classic ADO only
the three initials in its name). The product group now recommends ACCDB or
MDB to link, via ODBC to SQL Server (or other ODBC-compliant server) not
ADP.

Some have liked ADP in the past, but I wouldn't recommend it under the
current set of circumstances.

Larry Linson
Microsoft Office Access MVP

 

[briank]

Thank you everyone for your feedback.

 

[David W. Fenton]

I have inherited an A2007 adb with a connection to SS2K5. I have
recently begun trying to follow the various VBA code compiled by
four different consultants which is a bit of a challenge in
itself. Since my company's needs have changed over the past
months I feel that it would be cost effective to simply create a
new db from scratch. However, I am not sure of the advantages of
ADP or simply sticking with ACCDB. I would appreciate feedback on
this scenario.

For what it's worth, Microsoft has been deprecating ADPs in favor of
MDB/ODBC for the last several years. This is likely to change with
the next version of Access (i.e., the version *after* 2010) because
the Access team has been doing a special project in trying to seek
out users of SQL Server and make Access work better with it. So, I'd
expect ADPs to get new life in Access 15 (ADPs have been basically
ignored in A2007 and A2010, which should tell you something about
how well-implemented they were).

ODBC really is old and creaky, and I really wish ODBC2 would be
created and incorporate the best aspects of ADO. That wouldn't be
..NET-compliant, but it would cover a whole host of issues that come
with using ODBC access to modern databases.

For now, I would not contemplate using an ADP for any purpose. If
the Access team does as stellar a job on improving ADPs as they have
with the A2010 Sharepoint integration, it should be a big hit, and
then become the de facto best choice for development against SQL
Server back ends.

The main flaw for me with that, though, is that it's not back-end
agnostic, which is something I consider important. I'd love to have
the capabilities of an ADP when connecting to MySQL, for instance.
But that would be MS giving up a certain amount of proprietary
advantage. On the other hand, MS has done that with real browser
agnosticism with Sharepoint 2010, so it's possible. But I wouldn't
hold my breath on that one!

 

[David W. Fenton]

There seems to be an overwhelming fixed opinion that if any Access
problem exists it is viewed that the solution is to 'up size it'
to sqlserver.... Which is to say the opinion is to move to
sqlserver and not move away from sqlserver as most presume growth.

I think most often the people recommending SQL Server at the drop of
a hat are Access bigots, i.e., people who really haven't a clue what
they are talking about.

That said, you can find some real Access gurus who don't do
development against anything but SQL Server. However, that may have
more to do with the client base that they work with, i.e., their
clients are in a position to easily administer SQL Server, and have
requirements that make it a no-brainer. I have only a handful of
clients for whom SQL Server is appropriate -- most "microbusinesses"
(as I call small businesses in the 5-10 employees range) have very
little need for what SQL Server offers (though some really small
businesses *do* need the security and reliability and scalability,
because of the nature of their apps and the amount of data being
processes).

SQL Server is certainly no magic bullet. Like any upgrade, it makes
sense to upgrade not just because there's a new version to upgrade
to, but because you have existing problems that the upgrade will
ameliorate or completely resolve. If you can't identify exactly what
problem(s) upsizing will resolve, then there is actually no reason
to upsize.

 

[David W. Fenton]

Thank you for your thoughts on this situation. Currently my
employee seems to be firmly in place with using SQL 2005. With
this in mind I am now trying to determine which is best with the
SQL connection, either accdb or adp. I hear opinions supporting
each way but I'm trying to determine what factual evidence is the
most swaying. I appreciate any thoughts on this.

If you have an existing MDB application, no matter how confusingly
architected, you should stick with it and just fix the problems.
Likely when you understand the code you'll realize it didn't get
convoluted by accident. That is, very often convoluted solutions
reflect a previous developer's struggle and resolution of a complex
problem, and the result will encode a lot of factual information
about the problem being solved. Trashing it and starting over with
an ADP will mean you lose all the knowledge reflected in the code
that's been implemented.

That said, yes, sometimes convoluted code comes about because of a
programmer who simply isn't aware of the better methods. But that
kind of code is usually quite easy to spot and therefore pretty easy
to upgrade to better methods.

If you were starting with new development, I'd say 40/60 likelihood
that ADP is the best choice. After all, MS has been deprecating ADPs
for the last several years, even for new development, with the
exception of reporting apps (which MS says do have certain
performance advantages in ADPs).

But all of this will change 2-3 years from now, with the release of
Access 15, which apparently is going to address the neglect of ADPs
in the last two releases. That isn't enough to convince me that it's
wise to trash an MDB app and replace it with an ADP even then, but
it's worth keeping in mind (i.e., the investment in moving to ADP
could pay off 2 or 3 years down the road; on the other hand, the
Access 15 ADPs could be sufficiently different to make current ADPs
problematic).

 

[Larry Linson]

For what it's worth, Microsoft has been deprecating ADPs in favor of
MDB/ODBC for the last several years. This is likely to change with
the next version of Access (i.e., the version *after* 2010) because
the Access team has been doing a special project in trying to seek
out users of SQL Server and make Access work better with it. So, I'd
expect ADPs to get new life in Access 15 (ADPs have been basically
ignored in A2007 and A2010, which should tell you something about
how well-implemented they were).

ODBC really is old and creaky, and I really wish ODBC2 would be
created and incorporate the best aspects of ADO. That wouldn't be
.NET-compliant, but it would cover a whole host of issues that come
with using ODBC access to modern databases.

For now, I would not contemplate using an ADP for any purpose. If
the Access team does as stellar a job on improving ADPs as they have
with the A2010 Sharepoint integration, it should be a big hit, and
then become the de facto best choice for development against SQL
Server back ends.

The main flaw for me with that, though, is that it's not back-end
agnostic, which is something I consider important. I'd love to have
the capabilities of an ADP when connecting to MySQL, for instance.
But that would be MS giving up a certain amount of proprietary
advantage. On the other hand, MS has done that with real browser
agnosticism with Sharepoint 2010, so it's possible. But I wouldn't
hold my breath on that one!

There may be something "like" an ADP, but ADPs rely on OLEdb and the current
access mechanisms in "real development" (the bigots name for Dot Net stuff)
use something other than OLEdb.

I've worked in very few shops where they had a server back end, in which the
DBA would allow "mere developers" to do design-side work on "the DBA's
server". Yes, they had a very proprietary attitude. No, it wasn't likely to
change.

With small clients, you probably won't face that problem... I suspect that
the fact that Microsoft targets enterprise customers may have something to
do with their "neglect" of ADPs... they found out that many of the wonderful
features they had included were simply prohibited to developers in their
target audience.

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

developing ADP on the desktop, and then upsizing it to the server is
the preferred method

 

[a a r o n . k e m p f]

David;

Just because you're not using the worlds most popular database (SQL
Server) doesn't mean it's in anyone's best interests to use an
obsolete database

-Aaron

 

[a a r o n . k e m p f]

ADP is the best answer.

Do you really think that it's efficient to manually copy queries in
and out of every front end every time you open the DB?

keep your data, your logic, and your queries in one place- where they
belong.
SQL Server and ADP.

-Aaron

 

[a a r o n . k e m p f]

ADP means you never have to deal with corruption or performance
problems

 

[a a r o n . k e m p f]

Can ACCDB work over wireless?

I dont' think so!

 

[Arvin Meyer [MVP]]

message

Just because you're not using the worlds most popular database
(SQL Server)

At last count, Access/JET had more users than all other databases combined.
While SQL-Server is definitely a great choice in many cases, your statement
is factually incorrect (as usual)

 

[Tony Toews [MVP]]

But all of this will change 2-3 years from now, with the release of
Access 15, which apparently is going to address the neglect of ADPs
in the last two releases. That isn't enough to convince me that it's
wise to trash an MDB app and replace it with an ADP even then, but
it's worth keeping in mind (i.e., the investment in moving to ADP
could pay off 2 or 3 years down the road; on the other hand, the
Access 15 ADPs could be sufficiently different to make current ADPs
problematic).

I hadn't heard this. Any URLs?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tony Toews [MVP]]

ADP is the best answer.

In a few scenarios sure.

Do you really think that it's efficient to manually copy queries in
and out of every front end every time you open the DB?

I don't see how this works.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Albert D. Kallal]

message

Can ACCDB work over wireless?

I dont' think so!

Yes it does. We have a new off line mode, and if the connection breaks, you
can continue using the local data store. When the connection is
restored...data again beings to sync. This feature was added to the new ACE
for use with disconnected SharePoint tables.

So, we talking about cached disconnected data.

And, over an intermitting connection that is constantly breaking and
re-connecting and disconnecting the application will continue to run. Data
is syncing much like a email send/receive type of model. You don't get this
ability with ADP, but you sure do with ACCDB over wireless when using
SharePoint as your data store.

This ability is a result of changes made to ACE (the new JET) engine. Users
of ADP can only dream about such functionally here.

It is possbile you not heard of this new thing called the internet.

With a future world of Smartphone's, Wi-Fi hotspots then it is a no brain
that we need technology that can work in these environments. This is clearly
one big future of our industry. Access has joined this new party, but this
feature is not available by using ADP's. It is with accDB

 

[Albert D. Kallal]

I hadn't heard this. Any URLs?

Tony
--

Neither have I, and I just came back from the summit with some intense
vision sessions on access 15...