The best data connection method

[xinyin]

Hi all,
I started learning VB recently. I am now creating a VB form which displays data from an Access database (2000 format). However I am quite confused with the database connection methods provided by VB. According to the books I read, there are at least the following:
1) ADODC
2) ADO (Recordset)
3) Data Environment
4) SQL
My concepts on these methods are not very clear. These methods seems overlaping in some places, for example, in Data Environment there is recordset involved.

I would like to know which method most of you are using? I want to have some reference for my direction of learning - if I try to learn all of them I will be confused so I think I should just concentrate on practicing one method -the one which supports more functions.

 

[fredericofonseca]

SQL is not a connection method, but it is/can be used with any of the other methods.

I personally don't use ADODC unless absolutelly required to do so, and I prefer to use ADO coding, this because ADOCD has plenty of problems and is also because is requires bound controls which I also tend to completely avoid (in fact I only use then on listboxes).

I use normally Stored Procedures, so the only SQL on my VB code is "call my_sp".

If you search these forums and FAQ's from ADO you will find plenty of code samples and ideas.








Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[strongm]

I tend to use ADO - but DAO is faster with an Access/JET backend ...

 

[dglienna]

ADO is the way to go. The speed difference is minimal, and DAO is considered old-fashioned today.

David

 

[Sheco]

Another vote for ADO.

 

[dilettante]

Microsoft seems to list elements of its MDAC stack as current, deprecated, or obsolete.

Data Access Technologies Road Map

DAO, while still useful, is considered obsolete. It was never intended to be used with anything but relational data while ADO handles pretty much any structured data source.

ODBC is a creaky old technology, but still on the roadmap and it still has uses in some scenarios. One should almost always use OLEDB where available instead though, because most programmers are using high-level connector technologies such as ADO and these require additional "adapter" layers to talk to ODBC drivers.

For most general use of a Jet (Access) database the clear path is:

ADO/ADOX/JRO --> Jet OLEDB Provider --> MDB/MDW

ADODC isn't on the MDAC Roadmap because it isn't "mainstream." It is a sort of "ADO friendly" upgrade of the old RDO, built as a layer on top of ADO. I don't use it, and I try not to have an opinion about it.

 

[LazyMe]

>>ODBC is a creaky old technology, but still on the roadmap and it still has uses in some scenarios. One should almost always use OLEDB where available instead though, because most programmers are using high-level connector technologies such as ADO and these require additional "adapter" layers to talk to ODBC drivers.

Although the requested connection is to an Access database, if you read the white papers fo SQL server 2000, ODBC is far from a creaky old technology that happens to still be on the roadmap. It just offers a different (non object orientated) way to access a relational database. And as far as adapter layers go; OLEDB is not suitable to be used from within VB directly, so direct access to the native driver is out of the question. You'll still be using ADO, which uses an adapter layer to access OLEDB as well as ODBC.....

Also; bear in mind that most of the bencharks published regarding SQL server have been performed using the ODBC library and NOT the OLE DB library (at least that's what they say.....)

Greetings,
Rick

 

[xinyin]

Thanks for the suggestions. I think I will go with ADO since it is the trend. But before that I want to make sure of several things:

My main problem is connection between controls and database. First I tried using SQL only but can't - I typed the SQL statement in the "data source" property of each control and it seems not working. Later I use SQL in ADO (I already do the Dim conn as new ADODB.Connection... thing) but still the controls didn't detect the ADO - the data source property list is still empty. Only when I put an ADODC (even when the ADODC is blank) to the form the controls can detect it and allow me to choose it in the data source property list.

"I personally don't use ADODC unless absolutelly required to do so... ADOCD... has plenty of problems and is also because is requires bound controls(in fact I only use them on listboxes)."
Does this mean there is something that ADO cannot handle by itself? "Unless absolutely required" implies that ADODC MUST be involved when dealing with controls?

"SQL is not a connection method, but it is/can be used with any of the other methods." One of my books said SQL can work by itself replacing ADO and is even better, since ADO deals with recordset only one by one but SQL is not... but it did not talk about it any further. I wish to know if what it said is true.

"I use normally Stored Procedures, so the only SQL on my VB code is "call my_sp".
Stored procedures = commands? What is "call my_sp"?

No one uses data environment? Someone told me that not using data environment = cannot use data report designer = cannot print reports? Thank you.

 

[LazyMe]

>>"SQL is not a connection method, but it is/can be used with any of the other methods." One of my books said SQL can work by itself replacing ADO and is even better, since ADO deals with recordset only one by one but SQL is not... but it did not talk about it any further. I wish to know if what it said is true.

SQL is not a connection method; that's true. SQL is the language you would use to access your database. The SQL commands you'll be sending have to be transmitted to the database somehow; that's where your connection method comes in.

SQL can NOT replace ADO; you need ADO (or whatever connection method you prefer; ADO is recommenden by many, but as strongm says; DAO is faster against a jet engine, since it's natve) to transmit your SQL commands. I think that what your book is trying to explain is something like this:

in stead of set rs =con.execute"SELECT blah blah WHERE 0=1"
rs.addnew
etc.
etc.

you should use con.execute"INSERT blah blah INTO...."

But, as you can see; you need the connection object to execute these SQL commands.


What frederico is trying to explain is that he uses normal, unbound controls, gets the data from his database and fills in the controls "by hand". Meaning more work, but more control and as he says less bug prone (i have no experience with ADODC, since I always take his way; never have used the bound controls, so I don't know if they're buggy).



The main thing for you to understand from all the answers is:
SQL is NOT a connection method. It is the language in which to talk to your database; you'll need a connection method to be able to transmit these commands and receive the answers.

Greetings,
Rick

 

[dilettante]

Are people actually coding to the ODBC API in VB much? I don't think I've seen one program that did so. Normally a VB program will use ADO today, implying the extra layer of the OLEDB Provider for ODBC to host the actual ODBC calls.

Normally an OLEDB Provider is an access layer, not an adapter like the ODBC Provider. ADO offers a set of client-cursor and other capabilities like client sorting and filtering you don't have if you used ODBC directly.

SQL Server and ODBC for benchmarks? Nothing would surprise me, but I assume the applications were all written in C then too? The future performance path for SQL Server is supposed to be SQLNCLI (native client) that both supports OLEDB and ODBC and offers a lower-level API that bypasses the MDAC stack.

One thing ODBC can still offer though is a connection-pooling strategy embedded in the data-access stack rather than in a COM-management layer like MTS/COM+.

How To Enable ODBC Connection Pooling in a Visual Basic ADO Application

...useless in Windows script because of the API call required, but valuable in many VB scenarios.

As far as ADO vs. DAO goes, there are some good tips in:

INFO: Issues Migrating from DAO/Jet to ADO/Jet

It also points out that ADO requires more care than DAO to avoid opening a ton of connections, whereas DAO hides this by keeping "connections" by a user (program) consolidated.


What would be nice is some authoritative article on all of this from Microsoft, written for VB programmers. All of the options have pros and cons, there is no single "right" answer. Too bad we'll never see one.

 

[LazyMe]

>>Are people actually coding to the ODBC API in VB much? I don't think I've seen one program that did so. Normally a VB program will use ADO today, implying the extra layer of the OLEDB Provider for ODBC to host the actual ODBC calls.

I never said that people use ODBC much in VB; just said that it's far from a creaky old technology.


Normally an OLEDB Provider is an access layer, not an adapter like the ODBC Provider.

>>In the case of SQL server; ODBC is as native an access layer as the DB library (which is SQL server specific), not an adapter. This might be true for more database engines (have no idea, to be honest....)


>>What would be nice is some authoritative article on all of this from Microsoft, written for VB programmers. All of the options have pros and cons, there is no single "right" answer. Too bad we'll never see one.

I think that would be a hard one for the MS people, since much of this will depend on the type of database used, since each vendor has its own OLEDB /ODBC implementations of their database drivers, which probably will differ suficient enough not to be able to say which one performs better in which situation.


The general perception (at least for the VB world) however, is ADO. Not for performance reasons, simply because it's the easiest way to access any data source from within VB. OLEDB doesn't make life easier for a programmer than using a call level interface, such as ODBC (in fact, it can't even be used from within VB). ADO does. However, being new, easier to use etc. does NOT rule out other access methods to a data source and does NOT imply that they're creaky old technologies. In some cases these methods might outperform ADO, in some cases they might not. The big advantage in ADO is that it's relatively easy to use. It's advantage over DAO will be the easy upgrade (or downgrade if you will...) to another database system; just change the connection string and you're done (if you have been carefull with you're SQL statements.....)


Greetings,
Rick

 

[fredericofonseca]

My main problem is connection between controls and database. First I tried using SQL only but can't - I typed the SQL statement in the "data source" property of each control and it seems not working. Later I use SQL in ADO (I already do the Dim conn as new ADODB.Connection... thing) but still the controls didn't detect the ADO - the data source property list is still empty. Only when I put an ADODC (even when the ADODC is blank) to the form the controls can detect it and allow me to choose it in the data source property list.

This is one of the problems with bound controls. They only work in design mode if you have a DataControl (ADODC or similar). And the datasource is NOT a SQL statement, but a data provider (in most cases).
Furthermore some controls will only allow the datasource to be a DataControl but most still allow the source to be any recordset provider.

The datasource of any bound control object is normally a recordset provider.

Example:
one form with a ADODC control, a ADODB reference (code wise) and any bound control.
In design mode if you wish to "see" the fields you need to assign ADOCD to the datasource, and then you can select the field.

If you use direct coding instead you could do like this.

Set adoSelectComm = New ADODB.Command
adoSelectComm.ActiveConnection = DBConn
adoSelectComm.CommandType = adCmdStoredProc
adoSelectComm.CommandText = "SP_SELECT_ALL_cities"
RS.Open adoSelectComm, , adOpenForwardOnly, adLockReadOnly
set myboundcontrol.datasource = RS

So the above code will create a new adodb command object, will execute it (on this case it will execute a stored procedure called "SP_SELECT_ALL_cities") and then it will tell the bound control that it's datasource object is the recordsetobject "RS"

where RS = ADODB.recordset, dbconn = ADODB.connection.

"I personally don't use ADODC unless absolutelly required to do so... ADOCD... has plenty of problems and is also because is requires bound controls(in fact I only use them on listboxes)."
Does this mean there is something that ADO cannot handle by itself? "Unless absolutely required" implies that ADODC MUST be involved when dealing with controls?

ADO can handle anything, the problem is that some controls need a Data control being it ADO/RDO/DAO, to work.

"SQL is not a connection method, but it is/can be used with any of the other methods." One of my books said SQL can work by itself replacing ADO and is even better, since ADO deals with recordset only one by one but SQL is not... but it did not talk about it any further. I wish to know if what it said is true.

If you use a ADO recordset you will be restricted somehow on how you deal with the recordset. e.g. you can only update one at the time. But with plain SQL you can do "update my_table set fld1=a, fld2=b where my_fild = "abc" and this can potentially update thousands of rows in one statement.

But you can use the same SQL with ADO e.g. you could execute the above with either the connection or the command object.
e.g. my_adodb_connection.open or my_adodb_command.execute

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[xinyin]

Dear Frederico,

"...But with plain SQL you can do "update my_table set fld1=a, fld2=b where my_fild = "abc" and this can potentially update thousands of rows in one statement."
You have point out exactly what I am interested in. "Plain SQL" - do you mean there is a way I can use SQL only in the coding without ADO? Doing this "Plain SQL" is better than combining SQL with ADO? What is its syntax/coding look like?

Thank you.

 

[LazyMe]

xinyin;

You don't seem to understand exactly what is ADO (or whatever other method of connecting to a database).

Tell me: How would you go sending them plain SQL statements to your database? Wouldn't you need some sort of interface to communicate with the database engine? Well; that's what ADO can do for you, or any other connection method you would prefer.

In the case of ADO you would:

1. Open a connection (say objConn, for instance).
2. Execute your plain SQL statements over this connection:

objConn.Execute "UPDATE tblX SET columY='updated' WHERE columnZ='i want to be updated'"

That's all it.......

Greetings,
Rick