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

Fastest database connection

P: n/a
Hi Folks,

I'm building an Access XP database that will have a couple connections to an
SQL Server database over a WAN. What's the best / fastest way to connect?
ODBC? OLEDB? Something else?

Any direction would be greatly appreciated.

Thanks!
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
>From ADO 2.8 help:

**** Begin Quote ****
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.
....

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6 and later. 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.
**** end quote

Robert Vieira
Professional SQL Server 2000 Programming p. 25
(Wrox)
**** begin quote ****
DAO: Data Access Objects. This is actually native to Microsoft Access
(more specifically, the Jet database that is at the heart of Access).
There are a lot of applications written in VB and Access that use this
technology. Too bad! This object model can be considered clunky, slow,
and just plain outdated (believe me, I'm being nice and not saying what
I really think). It's still the fastest way to access things if you're
using a Jet (Access) database but, if you're using this technology to
access SQL Server, I would suggest putting some serious effort into
migrating away from it as soon as possible. Microsoft was calling DAO a
"legacy" model more than a year before the end of the Office 97
lifecycle. They want people to stop using it, and I have to agree with
them.
**** end quote ****

Nov 13 '05 #2

P: n/a
Thanks Lyle, but still unclear on the best route to take. What about ADODB
connection vs. OLEDB?

Thanks again!

<ly******@yahoo.ca> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
From ADO 2.8 help:


**** Begin Quote ****
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.
....

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6 and later. 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.
**** end quote

Robert Vieira
Professional SQL Server 2000 Programming p. 25
(Wrox)
**** begin quote ****
DAO: Data Access Objects. This is actually native to Microsoft Access
(more specifically, the Jet database that is at the heart of Access).
There are a lot of applications written in VB and Access that use this
technology. Too bad! This object model can be considered clunky, slow,
and just plain outdated (believe me, I'm being nice and not saying what
I really think). It's still the fastest way to access things if you're
using a Jet (Access) database but, if you're using this technology to
access SQL Server, I would suggest putting some serious effort into
migrating away from it as soon as possible. Microsoft was calling DAO a
"legacy" model more than a year before the end of the Office 97
lifecycle. They want people to stop using it, and I have to agree with
them.
**** end quote ****
Nov 13 '05 #3

P: n/a
Again quotes form MDAC help files: (they show that ADO uses OLEDB to
expose data).
*******
ADO
Microsoft® ActiveX® Data Objects (ADO) enable your client
applications to access and manipulate data from a variety of sources
through an OLE DB provider. Its primary benefits are ease of use, high
speed, low memory overhead, and a small disk footprint. ADO supports
key features for building client/server and Web-based applications.

OLE DB is a set of COM-based interfaces that expose data from a variety
of sources. OLE DB interfaces provide applications with uniform access
to data stored in diverse information sources, or data stores. These
interfaces support the amount of DBMS functionality appropriate to the
data store, enabling the data store to share its data.
******

Nov 13 '05 #4

P: n/a
ly******@yahoo.ca wrote:
Again quotes form MDAC help files: (they show that ADO uses OLEDB to
expose data).
*******
ADO
Microsoft® ActiveX® Data Objects (ADO) enable your client
applications to access and manipulate data from a variety of sources
through an OLE DB provider. Its primary benefits are ease of use, high
speed, low memory overhead, and a small disk footprint. ADO supports
key features for building client/server and Web-based applications.

OLE DB is a set of COM-based interfaces that expose data from a variety
of sources. OLE DB interfaces provide applications with uniform access
to data stored in diverse information sources, or data stores. These
interfaces support the amount of DBMS functionality appropriate to the
data store, enabling the data store to share its data.
******


Use the SQL Server OLEDB driver (SQLOLEDB) with ADO. I think it would be
masochistic to try and use the OleDB interface directly, unless you were
already familiar with it.

Plus, you would have to do something to map data from the OleDB
structures into recordsets for forms, etc. to work with, because Access
doesn't "talk" to OLEDB datasets (except maybe in an ADP).

Nov 13 '05 #5

P: n/a

"Jozef" <SP**********@telus.net> wrote in message
news:byOse.1723857$Xk.1168966@pd7tw3no...
Hi Folks,

I'm building an Access XP database that will have a couple connections to an SQL Server database over a WAN. What's the best / fastest way to connect?
ODBC? OLEDB? Something else?


"Best", like "beauty" tends to be "in the eye of the beholder".

Despite the quoted writeups re: "deprecation", there have been recent
indications that DAO and Jet are still considered a better approach than any
other for connecting an Access client to SQL Server (by Microsofties
intimately familiar with the products).

I've had good luck, and the users were satisfied with performance, using
Access - Jet -ODBC -server databases with from 25 - 175+ users.

"Classic ADO" has already been superceded by ADO.NET, so I would not expect
any significant enhancements, and, would not be surprised if it were the
technology "deprecated" in the next release. On the other hand, Jet and DAO
appear to be "hard to kill" at best, and perhaps "on the road to
resurrection".

Larry Linson
Microsoft Access MVP
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.