What is the difference between ADO and ODBC db connection? which one is 'better'?

[rayxu]

What is the difference between ADO and ODBC db connection? which one is 'better'?

[Cakkie]

Well, to begin, ADO isn't a provider. ADO are just the objects you use to get the data. ADO can use ODBC, plus a whole bunch of others. I guess you mean the difference between OLEDB and ODBC.

Generally, OLEDB is faster, but in some circomstances, ODBC does the job faster. If I were you, I would use OLEDB, first of all, because it does a better job, second of all, because this is the provider Microsoft advices to use. I don't think there will be much changes being done to ODBC, so it is what it is, and will be that way until it dies a silent death. OLEDB on the other hand is just born (to put it in nice words), and will still evolve a lot, following the latest technologies (take XML for example) to the minute.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[rayxu]

thanks for your reply.

I can use ADO (set reference) without going to control panel->ODBC data source to setup the connection. my question is why bother to setup ODBC connection at the control panel?

someone gave me the following instructions for Oracle connection. I don't know why ODBC setup doesn't work. I got connection error msg. It worked when I used ADO.

****

1.go to ODBC DATASOURCES IN CONTORL PANEL
2.CLICK FILE DSN TAB AND CLICK ADD BUTTON AND SELECT MICROSOFT ODBC FOR ORACLE, THEN CLICK NEXT GIVE THE USER NAME,PASSWORD, SEVEVIC NAME LIKE
ORACLE.PSPDEV

THEN CLOSE.

COME TO VB FORM

Set db = New ADODB.Connection
db.Open "filedsn=bala_ora", "scott", "tiger"
Set rs = New ADODB.Recordset
rs.Open "select * from emp", db, adOpenKeyset, adLockOptimistic
MsgBox rs.Fields(0).Value

***

By the way, do have ODBC conntection code for orale or sql server?

[Cakkie]

I don't know the exact code for an oracle connection, but if you created a DSN, it should look something like this:

' oracle
db.open "provider=MSDAORA;datasource=bala_ora;UID=scott;PWD=tiger"
' SQL server
db.open "provider=SQLOLEDB;datasource=bala_sql;UID=scott;PWD=tiger"
' or without DSN
db.open "provider=SQLOLEDB;server=myserver;database=somedatabase;UID=scott;PWD=tiger"




Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook