Database Programming in VB, part 2

This example is similar to the first one, except that we will be using the newer, ActiveX Data Objects (ADO) rather than Data Controls. Like the Data control, the ADO provides the layer between your code and the database. There is another layer yet between the ADO and the actual data source. This layer is called the OLE DB provider. The OLE DB knows how to talk to traditional database as well as other data sources like email.

We can use the ADO version of the Data control or we can do all the database access using code and objects provided by the ADO. First we will do the data control version and then the code version.

We have to add an ADO control to the form, like we did with the standard control. The ADO control is not part of the standard set so we have to add it from the Components library. The thing we are looking for is the Microsoft ADO Data Control 6.0. Once we have added it to the Toolbox, we can put it on the form.

Then we have to alter it's properties to attach it to a data source. The properties screen looks like: ADO Properties Panel The ADO control represents the results of running a query against the database. In the earlier example, we changed the query to get subsets of the data. This is similar.

We first have to connect to the database. We can do this by using an ODBC connection, usually used to access a remote database. Or we can set up a connection string. This is pretty simple. There are two steps. First we select the OLE DB provider we want. In our case, this is the MS Access 4.0 provider. The connection panel asks us to select a database. Click on the browse button and find the database. Then click on the test button and if it works, we are done. The properties screen now looks like: ADO Properties Panel with Connection String

Now we have to set up the record source. This is the query that will generate a set of rows or tuples from the database. The two methods we are interested in are adCmdText where we specify an SQL query or the adCmdTable where we select an entire table. We are going to pick the query we created in the database to display all data.

SELECT Asgs.Title, Students.lastname, grades.grade, Asgs.maxpoints
FROM Students 
INNER JOIN 
	(Asgs INNER JOIN grades ON Asgs.asgid = grades.asgid) 
ON Students.studentid = grades.studentid;

Now we just add controls and bind them like before.

Data Grid

The Data Grid control allows us to present a spreadsheet like view of the data. You have to add the Microsoft DataGrid Control 6.0