Is one better or worse and why? Aside from the fact that development is
halted on ADO. Can someone accomplish the same thing with both?

-David

I note CAREFULLY in your question, you are not actually asking about ADO vs
DAO...but ADO vs ODBC direct.

It likely needs pointing out to many readers here that JET/DAO support ODBC
direct.

This means that JET DOES NOT get loaded, nor does jet even touch your sql
that you pass to the server!!!

Here is a code example for ODBC direct: (this is DAO!!)


Dim strCon As String
Dim rstRecords As DAO.Recordset
Dim wrk1 As DAO.Workspace
Dim MyCon As DAO.Connection

Set wrk1 = DBEngine.CreateWorkspace("TestWorkSpace", "", "", dbUseODBC)

strCon = "ODBC;driver={SQL Server};DSN=;" _
& "SERVER=192.168.1.101;" _
& "DATABASE=RidesSql;" _
& "UID=SA;PWD=;OPTION=3;"

Set MyCon = wrk1.OpenConnection("mycon", dbDriverNoPrompt, False, strCon)

' now, you have a regular connection, and can build a recordset as
' normal...

Set rstRecords = MyCon.OpenRecordset("select * from tblJunk")

Note I used "dbUseODBC", and thus we are by passing JET directly. Use of
this keyword means that JET is not to be used, nor even loaded!

It would also mean that recordsets that are JOINS are NOT updateable like
they would be in JET (or with ADO.......golly..does ADO support updateable
joins? (or do you have
to used shaped recordsets (another feature of ado!!)??? (anyone??)...


Hum, so, why use ADO in place of ODBC direct? Well, my only arguments is
that ADO was more designed with type of use in mind, and not very many
people use ODBC direct anymore....

ADO has better support for stored procedures etc, but, when you are using a
pass-through query...it really is not much of an advantage anyway (just sent
the raw text to the server as a query....it don't actually have to be sql
anyway).

I can only say that ADO was designed from the ground up as a database
neutral object. DAO, and the ODBC direct were the "result" of this needed.
So, ADO tends to be a bit cleaner, and likely what most of your developers
would have used....

To be honest...my arguments are not that strong in favor of ADO here, but I
think by the time the industry started using server based database systems,
then ADO was in full swing. So, likely ADO is better to due to wide spread
use.

On the other hand...if you struck with ODBC direct...you can still using
it...and skipped ADO completely!!


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

Albert-

Thanks for the nice writeup. Right now we're using DAO with Jet, but are
switching over to Something and MS SQL 2005.

So, we can choose to go with either ADO or ODBCdirect or maybe something
I've not yet considered.

Does this impact your suggestions?

-David

"Albert D.Kallal" wrote:

> I note CAREFULLY in your question, you are not actually asking about ADO vs
> DAO...but ADO vs ODBC direct.
>
> It likely needs pointing out to many readers here that JET/DAO support ODBC
> direct.
>
> This means that JET DOES NOT get loaded, nor does jet even touch your sql
> that you pass to the server!!!
>
> Here is a code example for ODBC direct: (this is DAO!!)
>
>
> Dim strCon As String
> Dim rstRecords As DAO.Recordset
> Dim wrk1 As DAO.Workspace
> Dim MyCon As DAO.Connection
>
> Set wrk1 = DBEngine.CreateWorkspace("TestWorkSpace", "", "", dbUseODBC)
>
> strCon = "ODBC;driver={SQL Server};DSN=;" _
> & "SERVER=192.168.1.101;" _
> & "DATABASE=RidesSql;" _
> & "UID=SA;PWD=;OPTION=3;"
>
> Set MyCon = wrk1.OpenConnection("mycon", dbDriverNoPrompt, False, strCon)
>
> ' now, you have a regular connection, and can build a recordset as
> ' normal...
>
> Set rstRecords = MyCon.OpenRecordset("select * from tblJunk")
>
> Note I used "dbUseODBC", and thus we are by passing JET directly. Use of
> this keyword means that JET is not to be used, nor even loaded!
>
> It would also mean that recordsets that are JOINS are NOT updateable like
> they would be in JET (or with ADO.......golly..does ADO support updateable
> joins? (or do you have
> to used shaped recordsets (another feature of ado!!)??? (anyone??)...
>
>
> Hum, so, why use ADO in place of ODBC direct? Well, my only arguments is
> that ADO was more designed with type of use in mind, and not very many
> people use ODBC direct anymore....
>
> ADO has better support for stored procedures etc, but, when you are using a
> pass-through query...it really is not much of an advantage anyway (just sent
> the raw text to the server as a query....it don't actually have to be sql
> anyway).
>
> I can only say that ADO was designed from the ground up as a database
> neutral object. DAO, and the ODBC direct were the "result" of this needed.
> So, ADO tends to be a bit cleaner, and likely what most of your developers
> would have used....
>
> To be honest...my arguments are not that strong in favor of ADO here, but I
> think by the time the industry started using server based database systems,
> then ADO was in full swing. So, likely ADO is better to due to wide spread
> use.
>
> On the other hand...if you struck with ODBC direct...you can still using
> it...and skipped ADO completely!!
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
> http://www.members.shaw.ca/AlbertKallal
>
>
>

"DBG" <(E-Mail Removed)> wrote in message
news:2B8395C6-FCCE-42D6-AE1E-(E-Mail Removed)...
> Albert-
>
> Thanks for the nice writeup. Right now we're using DAO with Jet, but are
> switching over to Something and MS SQL 2005.
>
> So, we can choose to go with either ADO or ODBCdirect or maybe something
> I've not yet considered.
>
> Does this impact your suggestions?

no, not really. Remember, if you are talking about developing software in
ms-access, but using sql server for the back end, then you can WELL continue
to use linked tables. For the most part, this will mean DAO. However, if in
your code you declare your reocrdset objects as ADO reocrdsets, then
ms-access will use that (it actually returns the DAO, or ADO recrodset based
on the data type you choose when working with a form. To be fair, likely
forms pull data via DAO...but it really don't matter!!).

The ODBCdirect is not really much of and advantage over using pass-through
quires from ms-access to sql server anyway. It is not clear for what reason
you need ODBCdirect here? (or, perhaps you are asking why using it??).

I would say that when I use ms-access + sql server, a lot of my existing DAO
code is used. There is really little, if any performance difference here
anyway.

ms-access works well with bound forms to sql server as long as you restrict
records loaded to the form (but, most developers do that for any application
anyway...right).

If I have already a linked table to sql server...then to grab one
record..the following dao code with a standard linked table works just fine


dim rstRec as dao.ReocrdSet
dim strSql as string

strSql = "select * from tblCusotmers where id = 123"
set rstRec = currentdb.OpenrecordSet(strSql)

The above will only pull one record over the network when using sql server
(in fact, it will only pull the one record even when NOT using sql
server...and just using a plain mdb shared on the network). So, there is
going to be NO advantage to setting up a connection object, and using ADO.
The above code is simple, easy....and as above with a linked table will
still perform just fine. Sql server in the above case will only disk out one
reocrd. So, why go to such hoops to use ADO, or ODBCdirect, when a simple
linked table will suffice?

So, there is no special need to use ADO, or ODBCdirect when using sql
server. For the most part, linked odbc tables work just fine.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal