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

Recommended data access model

P: n/a
Some time a ago, on this newsgroup the following comments were made in
recommending
good references for Access (2003)
I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for
people moving from power user to developer, but now I suggest you browse
it,
too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no
longer recommend, and the Access ADP client to SQL Server. He writes well,
and is a good teacher, but the subjects may not be what you are looking
for.
The Access 2000 version may be more useful than later ones.


Can anyone expand on the statement about ADO being no longer recommended
and what is currently recommended?
I am working through Dr. Dobson's "Programming Access 2003...".

Thanks
Tony
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Tony Lee" <To**@myaddress.com> wrote in message
news:AM*******************@news.xtra.co.nz...
Some time a ago, on this newsgroup the following comments were made in
recommending
good references for Access (2003)
I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for
people moving from power user to developer, but now I suggest you browse
it,
too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no
longer recommend, and the Access ADP client to SQL Server. He writes well,
and is a good teacher, but the subjects may not be what you are looking
for.
The Access 2000 version may be more useful than later ones.


Can anyone expand on the statement about ADO being no longer recommended
and what is currently recommended?
I am working through Dr. Dobson's "Programming Access 2003...".


ADO has been replaced by ADO.Net which, despite the similar name, has very
little in common with its predecessor.

DAO was developed specifically for use with Jet databases (i.e. MDB files),
and is still part of Access.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Nov 13 '05 #2

P: n/a
Can anyone recommend a comprehensive refernece for using ADO.net in Access
2003?
Nov 13 '05 #3

P: n/a
AFAIK, Access 2003 doesn't support ADO.Net

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Tony Lee" <To**@myaddress.com> wrote in message
news:K0*******************@news.xtra.co.nz...
Can anyone recommend a comprehensive refernece for using ADO.net in Access
2003?

Nov 13 '05 #4

P: n/a
This is from MDAC 2.8 SDK.

"The Role of ADO in MDAC
The Microsoft Data Access Components (MDAC) provide data access that is
independent of data stores, tools, and languages. It provides a
high-level, easy-to-use interface and a low-level, high-performance
interface to practically any data store available. You can use this
flexibility to integrate diverse data stores and use your choice of
tools, applications, and platform services to create the right
solutions for your needs. These technologies provide the basic
framework for general-purpose data access in Microsoft® Windows®
operating systems.

There are three primary technologies in MDAC. ActiveX® Data Objects
(ADO) is a high-level, easy-to-use interface to OLE DB. OLE DB is a
low-level, high-performance interface to a variety of data stores. Both
ADO and OLE DB can work with relational (tabular) and nonrelational
(hierarchical or stream) data. Finally, Open Database Connectivity
(ODBC) is another low-level, high-performance interface that is
designed specifically for relational data stores.

ADO provides a layer of abstraction between your client or middle-tier
application and the low-level OLE DB interfaces. ADO uses a small set
of Automation objects to provide a simple and efficient interface to
OLE DB. This interface makes ADO the perfect choice for developers in
higher level languages, such as Visual Basic and VBScript, who want to
access data without having to learn the intricacies of COM and OLE DB."

Do you see where ADO is "not recommended"? No? Read it again! Still
not? Guess you'll never get to be a knowledgeable MS insider then.

Still can't find it? Read on:

"Microsoft Data Access Components (MDAC)
The Microsoft® Data Access Components (MDAC) SDK documents the key
technologies that are part of Microsoft's strategy for providing access
to information across the enterprise.

Microsoft Data Access Components include ActiveX® Data Objects (ADO),
OLE DB, and Open Database Connectivity (ODBC). Data-driven
client/server applications deployed over the Web or a LAN can use these
components to easily integrate information from a variety of sources,
both relational (SQL) and non-relational.

If you have questions or need detailed information about properly
redistributing MDAC, see Redistributing MDAC for a description of the
distribution requirements for MDAC.

ActiveX Data Objects (ADO)
Microsoft ActiveX Data Objects (ADO) provides consistent,
high-performance access to data and supports a variety of development
needs, including the creation of front-end database clients and
middle-tier business objects that use applications, tools, languages,
or Internet browsers. The primary benefits of ADO are ease of use, high
speed, low memory overhead, and a small disk footprint.

ADO provides an easy-to-use interface to OLE DB, which provides the
underlying access to data. It uses a familiar metaphor - the COM
Automation interface - available from all leading Rapid Application
Development (RAD) tools, database tools, and languages.

OLE DB
Microsoft OLE DB is a set of interfaces that expose data from a variety
of relational and nonrelational sources by using the Component Object
Model (COM). OLE DB interfaces provide applications with uniform access
to data stored in diverse information sources. These interfaces support
the amount of DBMS functionality appropriate to the data store,
enabling the data store to share its data.

OLE DB comprises a programmatic model consisting of data providers,
which contain and expose data; data consumers, which use data; and
service components, which process and transport data (such as query
processors and cursor engines). In addition, OLE DB includes a bridge
to ODBC to enable continued support for the broad range of ODBC
relational database drivers."

Did you get it that time? NO? Geeesh. Well, see the part where it says,
". The primary benefits of ADO are ease of use, high speed, low
memory overhead, and a small disk footprint." Now tell me, who would
want THAT!
OK, maybe ADO is deprecated. Let's check that:

"Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases."

Did you see where ADO is deprecated? No? What can I say?

Doesn't this all explain about "knowledgeable MS insiders not
recommending DAO" ?

Still not convinced? I don't know what to say:
That ADO is crisp, clean and has a million (well maybe only a hundred
or so) capabilities that DAO never even thought of?
That Access and ADO work extremely well together?
That speed advantages of DAO over ADO are in nanoseconds?
That Lyle has written many successful applications (Access included)
over the last few years without any (except the behind the scenes
stuff) DAO at all.
That ADO, currently, is the only well known and popular technology one
can use in almost exactly the same way within VBA, VB, VBS, JavaScript
and ASP to interface with Databases?

Nov 13 '05 #5

P: n/a
"ly******@yahoo.ca" <ly******@yahoo.ca> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

[quoting MS documentation]
"Deprecated Components
[]
Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components
were removed from MDAC 2.6. Microsoft has deprecated the Microsoft
Jet Engine, and plans no new releases or service packs for this
component. As a result, the Jet and Replication Objects (JRO) is
being deprecated in this release and will not be available in any
future MDAC releases."


All of this makes NO SENSE whatsoever in the context of writing
applications that interface with JET DATA.

Take Jet replication, for instance, There is no MS-provided
technology other than JRO for managing certain aspects of Jet
replication (much of it can be done via DAO, and the rest can be
done with the TSI Synchronizer that MichKa created, but that's not
from MS, and it's unsupported, even by MichKa).

If you're going to use Jet as your data store, you've already using
a deprecated technology in the first place, so it makes no sense to
me to then choose non-deprecated, non-native tools for that data
store when THE DATA STORE ITSELF IS DEPRECATED.

This is, perhaps, why in recent releases of Access, ADO has been
relegated to the second-class status it always should have had in
regard to applications run against Jet data.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6

P: n/a
Good arguments, Lyle, and good quotes.

But, as far as I know, the former Product Manager for ADP and ADO has not
changed his statement that, currently, MDB-Jet-ODBC is preferrable for
accessing MS SQL databases over ADP-ADO-OleDB.

I can't determine how much of the documents you quote is real technical
information and how much is marketing hype. I do know that ADO was very
highly hyped even in its first, buggier, releases.

It was a complaining user in one of the newsgroups who reported that someone
in product support had told him that ADP was going to be deprecated in the
next release -- maybe he said the same about ADO. I believe he later said he
was disregarding those statements. If _I_ wrote that ADO is or was to be
deprecated, it was a typo on my part, because I haven't read nor heard that
from any authoritative source.

I don't need to use the same data access method in all the languages you
list and I can't recall anyone who does serious development in all those
languages. I suspect ADO could also be used, with some care, in VB.NET, C#,
and ASP.NET. BUT, all the .NET developers I know are using the successor
access method, ADO.NET.

Didn't you write that you had decided, for various reasons, not to use ADPs?
Are you using ADO in MDBs? Would that be in code behind the scenes?

Larry Linson
Microsoft Access MVP
Nov 13 '05 #7

P: n/a
Larry

ADO <> ADP

ADO != ADP

I use ADO extensively; I have "deprecated" the use of ADPs in my own
work.

If I HAD to use MS-SQL and Access together (I don't) I would probably
at least try doing it unbound or "half-bound" (my term) with
disconnected ADO recordsets set as the recordsets for forms
(updatebatch on close) and reports and getstring()s used for
pull-downs. Would this be a lot of work? I think so. Would it be better
than the ADPs or ODBC MS has made available to us? I hope so. I'm not
sure if ADPs would have any advantage over MDBs, or vice versa in this
scenario.

Nov 13 '05 #8

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in
news:6PyDe.9135$JJ.3528@trnddc09:
I can't determine how much of the documents you quote is real
technical information and how much is marketing hype. I do know
that ADO was very highly hyped even in its first, buggier,
releases.


There is plenty of contradictory MS documentation. The replication
documents from MS are a mix of very old stuff, written back before
too many people understood all the implications of how Jet
replication worked, and things that have been kept up-to-date.
You'll find things in the MS replication documentation that are now
known to be bad ideas, but which are nowhere deprecated, mostly for
marketing reasons.

I know of no MS replicaton that explains that replicating Access
objects is a bad idea that is guaranteed over time to corrupt your
whole replicated database beyond recovery, but that's the sad
reality of it. The last time I looked, there were still MS
replication docs recommending replication for pushing out front-end
revisions.

So, even if it *does* come from official MS documentation, I
wouldn't trust it when it is contradicting both common sense and my
own experience.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9

P: n/a
<ly******@yahoo.ca> wrote
ADO <> ADP

ADO != ADP
Yes, of course, and I did not intend to imply otherwise. On the other hand,
the only way that "classic ADO" is useful outside an ADP/ADE is "in the
background" via VBA code, and the successor to classic ADO, the already
available ADO.NET is dissimilar in many respects.
If I HAD to use MS-SQL and
Access together (I don't) I would
probably at least try doing it unbound
or "half-bound" (my term) with
disconnected ADO recordsets set as
the recordsets for forms (updatebatch
on close) and reports and getstring()s
used for pull-downs. Would this be a
lot of work? I think so. Would it be
better than the ADPs or ODBC MS
has made available to us? I hope so.
I'm not sure if ADPs would have any
advantage over MDBs, or vice versa
in this scenario.


I'd be interested to hear your evaluation of using ADO's disconnected
recordsets with unbound or "half-bound" forms/reports. My experience with
ADPs was on a system with unbound forms, using ADO to retrieve the data and
ADO to execute SQL to update the DB. The original author clearly knew little
about ADP, ADO, or SQL Server for that matter -- not one of the SQL Server
tables had a PK or unique index. And, yes, it was clumsy. I strongly suspect
that your design would not be so clumsy.

(The client was unwilling to pay for a major overhaul, as
they only wanted to "nurse along" this application until they could fold the
functionality into their corporate ERP system. I hope their ERP
implementation moves along a lot more rapidly than most I have seen, or
they'll be trying to put lipstick on that pig for a long time. <G>)

Larry
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.