473,320 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Fastest database connection

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
5 3451
>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
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
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
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: martijn | last post by:
H!, I'm testing things with Python with databases. But I have one big question. What is the 'fastest' database for the internet in combination with Python ? - with +/- 15 GB data. - fast...
6
by: serge | last post by:
What is the fastest way to generate an SQL 2000 database out of SQL scripts. The SQL scripts contain the create tables, views, stored procedures, triggers, constraints, and the tables DATA...
7
by: adm | last post by:
There are a few ways to go about this, but what is the fastest when called from VBA? This if for an ADP with a SQL Server back-end.
3
by: Harry Haller | last post by:
What is the fastest way to search a client-side database? I have about 60-65 kb of data downloaded to the client which is present in 3 dynamically created list boxes. The boxes are filled from 3...
1
by: Harry Haller | last post by:
What is the fastest way to search a client-side database? I have about 60-65 kb of data downloaded to the client which is present in 3 dynamically created list boxes. The boxes are filled from 3...
3
by: Sehboo | last post by:
I have to call a stored procedure which returns about 1000 rows with about 5 fields (most of them are int). What is the fastest way to load this data? if I just do DataTable.Load, then is it...
3
by: Code Monkey | last post by:
DataTable loading a SqlDataReader (dt.Load())? Vanilla DataTable using SqlDataAdapter or SqlDataReader ? or is there a better way using Generics? If so, how? Essentially I want to get about...
3
by: Richard | last post by:
Can someone give me some insight in what type of connection is the fastest to connect to SQL Server. Thx in Advance
3
by: Mohamed Yousef | last post by:
Thanks all , but there is still something i forget to state -sorry - all communication will be via Http with a server so data is received via Http so local network solutions won't work the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.