We are already familiar with programming DAO. But you can access more data sources with ADO, and I think you will be pleasantly surprised to find out that programming with ADO is actually simpler! You don't believe me? Let's give it a try. We'll start off with a simple ADO program that just fills a listbox with database data for us.
In this exercise, we will open an ADODB connection and an ADO recordset. When these tasks have been completed, we will populate the listbox with the names in the Publishers table.
1. Add a new form to your project and name it frmADOPublishers. Be sure that you have selected Project | References and include the ActiveX Data Objects 2.0 Library in your project.
Then add a listbox and a command button as shown below:
2. Add the following code to the command button's Click event:
Private Sub Command1_Click()
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String
'—Create a new connection --
Set adoConnection = New ADODB.Connection
'—Create a new recordset --
Set adoRecordset = New ADODB.Recordset
'—Build our connection string to use when we open the connection --
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\Begdb\Biblio.mdb"
adoConnection.Open connectString
adoRecordset.Open "Publishers", adoConnection
Do Until adoRecordset.EOF
List1.AddItem adoRecordset!Name
adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close
set adoRecordset = nothing
set adoConnection = nothing
End Sub
Select Project | Project1.Properties… and add frmADOPublishers as the Startup Object.
Run the program, and click the Fill List button
By pressing the Fill List button, we call upon ADO to open a data source and then use code to iterate through the recordset and fill up the listbox. Pretty straightforward, eh?
Let's take a closer look at what we have just done.
Notice that we need to dim an ADODB.Connection object.
Dim adoConnection As ADODB.ConnectionThe connection object allows you to establish connection sessions with data sources, and this object provides a mechanism for initializing and establishing the connection to our data source. Remember the connection string we created with our ADO data control? We will pass the connection object the same kind of string.
Once our connection is open, we want to create a recordset. Just like our DAO counterpart, we dim an ADODB recordset.
Dim adoRecordset As ADODB.RecordsetFirst of all, since our VB 6.0 supports both DAO and ADO, we must be careful to fully qualify the type of recordset we need. If we forget to specify that the recordset is of type ADODB, the environment will gladly provide a recordset - except that it will be for DAO, not ADO. So take a look at what would happen if you tried to create a recordset:
The problem is that the Recordset highlighted by IntelliSense here would be a DAO type recordset – not what we want. So please be careful to specify an ADO recordset. This is done by first specifying ADODB. As soon as you press the "." dot after ADODB, you will see the various methods, constants, and events that are available to an ADODB. Of course we want the recordset of the ADODB object. Be sure that you select the correct type of recordset as shown below:
Since you remembered to add the ActiveX references to your project, VB 6.0 now is able to provide the Intelli-help choices in the drop down box.
After we dim our connection and recordset object variables, we want to set them to a new connection and a new recordset.
Remember that in DAO we would set an object variable to a database, then another to a recordset that was built on the database object variable. Opening the DAO database required that we pass in the fully qualified name and location of the database.
In ADO, however, we will pass that information in as part of the connection string when we actually open the connection.
Here we are creating a new connection and recordset object. By using the Set keyword, we set a reference to our object variables, adoConnection and adoRecordset:
'—Create a new connection --
Set adoConnection = New ADODB.Connection'—Create a new recordset --
Set adoRecordset = New ADODB.Recordset'—Build our connection string to use when we open the connection --
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\BegDB\Biblio.mdb"
Of course, we could just as easily have added this last line directly to the Open method of the adoConnection object. But placing it in a string is a good way to get comfortable with the actual string of parameters.
Before establishing a connection, our application must set up a connection string, as well as connection time-out, default database, and connection attributes. The connection object also allows you to set up the CommandTimeout property for all the command objects associated with this particular connection. We will just use the default for now, which is 15 seconds.
We are ready to open the connection with the string we defined above. The string provides the connection object with enough information on the OLE DB provider and the database to establish the link.
The easiest way to open a connection is simply pass the connection string to the Open method of the connection object:
adoConnection.Open connectString
To see if the connection was successful, you could check out the State property of the connection object. State will return adStateOpen if the connection is open and (surprise!) adStateClosed if it isn't. If you wanted to test the connection, you could simply add something like:
'We can test to see if the attempt to connect worked.
If adoConnection.State = adStateOpen Then
MsgBox "The Connection is now open!"
Else
MsgBox "Sorry. The connection could not be opened."
End IfOnce our connection object is linked to the database by using the open method, we can now use this connection to open a recordset or perform some action on the data source.
If there is an error in the connection string, VB will not know it until we try to open the connection object, adoConnection. By simply assigning the connect string to the connectString variable (or even directly to the connection object), any errors won't show up until we try to actually use the string by connecting. So if an error does crop up, you can be sure that the connection string is the culprit.
Now we are ready to open the recordset by using its .Open method. We are using only two parameters here - the table we want opened and the connection to use.
adoRecordset.Open "Publishers", adoConnection
There are several additional parameters we can pass to more granularly define the recordset we want opened. The syntax of the .Open method for a fully qualified recordset looks like this:
RecordSet.Open Source, ActiveConnection, CursorType, LockType, OptionsAs you can see, in our program we are only passing in the source of the data and the active connection. We will soon cover these additional parameters when we write some additional programs. But for now, we will simply rely on their default values.
Now that we have an open connection and a recordset, it is very straightforward to update our list box. Notice that they syntax is the same as we used for DAO: we use the same "!" 'bang' operator to access a specific field from the recordset:
Do Until adoRecordset.EOF
List1.AddItem adoRecordset!Name
adoRecordset.MoveNext
LoopFinally, we close the recordset and the connection. Since these are object variables are dim'ed locally, they would go 'out of scope' as the code exited the procedure. But it is good programming practice to always close these items when they are no longer needed:
adoRecordset.Close
adoConnection.Close
set adoRecordset = nothing
set adoConnection = nothingThere, we have just opened an ADO connection, created a recordset, and accessed the Name field. Not too bad.