Book Review: Beginning Visual Basic 6 Databases
If you haven't already done so, right click on the ADODC control and select ADODC
We are presented with the property page for the ADODC control. Since setting up the ConnectionString must contain just about every piece of information required to connect to our data source, this comes in very handy indeed! Recall that the connection string needs to know things like the location and name of the database, any passwords that might be required, and the OLE DB data provider.
Click the Build button and let's step through the process.
We are presented with another set of property pages for the Data Link. Notice the list of OLE DB Providers that are shipped with VB6.0. If we wanted to connect to a generic ODBC source, we have a provider for ODBC Drivers. Notice that we have providers for Oracle and SQL Server. And as time goes on, all of the major database providers will ship their own OLE DB providers. This way, ADO can talk directly to the specific provider, just as DAO can now talk to ODBC.
Select the Microsoft Jet 3.51 OLE DB Provider.
Click the Next>> button. This brings up the Connection tab. Here is where we must tell VB the location and name of the database we will be using. Click the button with the ellipsis and locate the usual BegDBBiblio.mdb database. Since the database does not require a password, don't change the entries for logging on to the database. As you'll recall, Admin is the default user name for Access databases:
always is a good idea to use the Test Connection option. This way, if there was something wrong with the location or name of the database, we would get an error advising us of this. Let's say that you entered the name of the database but forgot to add the .mdb extension. By testing the Data Link, we would know immediately:
We can then correct the error in the name and location of the database and press Test Connection once again.
There, that's better. Now click the Advanced tab just to see what options are available to us. Leave the default Share Deny None (if you needed to open the database in a read-only, exclusive mode you would check the Read box):
Now click the All tab. Here you can see all of the information the Data Link property box garnered for us:
This is all of the information that will be used to create the connection string. If you need to modify any of the properties, simply click the Edit Value button.Highlight any value you wish to edit and press the Edit Value button. This will give you a chance to modify any value in the connect string prior to clicking the OK button.
After the connection string is built, click OK to dismiss the property pages for the Data Link. Now the control has the information it needs to connect to the data source. However, we still need to inform the data control which table(s) we wish to access. Right click on the ADODC data control again and select ADODC Properties. Notice that the connection string text box is now filled in:
Click on the RecordSource tab and click the drop down list box for the Command Type:
Select 2 - adCmdTable. Now the control knows we want to access records from a table
directly. If the DataSource is not known in advance, then adCmdUnknown is selected. If we
were going to issue a SQL command, then adCmdText would be selected, and the bottom text
box, Command Text (SQL), would become enabled. Finally, if we have stored, pre-compiled
procedures, we would choose adCmdStoredProc. This time, be sure to select choice 2 -
Now the control knows that we want to access records from a table, and it knows the name of database from when we set up the Data Link. Now, the Table or Stored Procedure Name listbox becomes enabled. Click the listbox and all of the tables in the database are shown:
Select the Publishers table and click OK.
The data control now has the connection string built, and will be able to retrieve a recordset for us from the data source. Double-click on the data control to bring up the code window. You might notice that the Adodc1 data control has a few new event procedures. And many more parameters are passed in by VB so we can really know what is going on.
3 Now that the ADODC data control has been set up, let's bind the Text1 textbox. Bring up the property dialog box for Text1. Set the DataSource property to Adodc1.
Now click the drop down box for the DataField. Notice that just like the DAO data control, all of the valid fields are displayed:
Select the Name field.
4 Now double click on the ADODC data control to bring up the code window. In the MoveComplete event procedure of the control, add the following highlighted code:
Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) Label1 = "Record " & Adodc1.Recordset.AbsolutePosition _ & " of " & Adodc1.Recordset.RecordCount End Sub
The MoveComplete event of the ADODC control will fire when the control navigates to a new record. So this is the perfect place to update our label, lablel1, to show what record is the current record in the recordset managed by the ADODC control. Of course, we read the .AbsolutePostion property to tell us what record we are on and then read the .RecordCount to inform us how many records are in the recordset.
Remember way back when we noted that when the form's Activate event procedure is fired, we can be sure that all of the visible components (like the ADO data control) are fully initialized and displayed? Let's place a line of code that will display the current record when the form is fully loaded.
5 Add the following code to the frmADO form's Form_Activate event. When this event fires, we know the ADODC data control has been completely initialized so it is safe to read the properties.
Private Sub Form_Activate() Label1 = "Record " & Adodc1.Recordset.AbsolutePosition _ & " of " & Adodc1.Recordset.RecordCount End Sub
6 Go ahead and run the program. You can see that it works as advertised:
To the user, there is absolutely no difference between the intrinsic data control and our new ADO data control. See - I told you that the transition would be painless.
How It Works
Well, no magic here. We just used the same techniques we used earlier for the DAO data control. The only twist here was setting up the connection string. As you can see, this is a bit more involved. But the connection string provides a generalized method to encapsulate all of the information required to talk to the OLE DB provider, locate the database, and provide password information. But once the string was built, the rest of the code was pretty much the same. As the user navigates the recordset with the ADODC control, we update the label.
Page 7 of 23