Visual Basic Database Tutorial - Part 6, Page 3
In previous tutorials, we've connected to databases using the bog standard Data control.
Now this seemingly innocent control accesses your Access database via a technology known as DAO, or Data Access Objects.
Since those halcyon days of prehistoric data access, Microsoft has released another two ways of getting at databases Remote Data Objects (RDO) and ActiveX Data Objects (ADO).
Remember we programmed in database code a few instalments back? 'Dim db as Database' and all that? Well, we were using Data Access Objects to do that.
But RDO and ADO offer us new ways of accessing such information. How come? Well, we'll skip over RDO for the moment. But ADO supposedly allows us to access all sorts of information, above and beyond your typical Access database.
In essence, you should be able to simply connect with any data source from SQL Server to Access to Oracle to an e-mail application to old Mainframe information all using the same wad of ADO code.
And that code is a little different to the stuff we wrote earlier. Not too much, but there are unique distinctions.
Why am I telling you all this? Because those special control properties I was talking about on the previous page the ones that'll help you design a great VB application around an ever better database design - are only available when you access databases through ADO.
So first off, start a new project and let's add the ADO equivalent of the Data control to our project:
- Select Project, Components
- Scroll down the list and check 'Microsoft ADO Data Control 6.0'
- Click OK
You'll notice a new control appear in your toolbox -
- Double click on the ADODC control to add it to your form
- Change its Name property to 'datPets'
We now need to tell this control we want it to connect with the Vetinary database we created last week. This is done slightly differently using ADO via 'connection strings':
- Click on the ellipsis beside the ConnectionString property
- Click the Build option
- On the screen that appears, select 'Microsoft Jet 3.51 OLE DB Provider' if you created the database in Access 97 or 4.0 if you created it in Access 2000
- Click Next
- Type in the path to your database (eg, c:\surgery.mdb)
- Click OK
- Click OK on the Property Page form
You will notice the builder inserted something similar to the following in the ConnectionString property:
- Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Surgery.mdb;Persist Security Info=False
This string tells ADO what type of database it is looking at and where it can be found. Now, as with the DAO control, we need to tell it what information to retrieve:
- Click the ellipsis next to the RecordSource property
- Select Command Type '2 adCmdTable'
- Select the 'Pets' table from the available list
- Click OK
Now we're going to add another control to our program:
- Click Project, Components
- Scroll down, select 'Microsoft DataList Controls 6.0'
- Click OK
You should see two extra widgets appear in your toolbox -
Add the one titled 'DataCombo' to your project and change its Name property to 'dbcOwner' and its Style to '2 dbcDropdownList'. This is the special control that will allow the user to select an owner name, but still put the OwnerID number into the database.
But before we can use that DataCombo control, we need to add another ADODC control.
- Add another control to your project
- Change the Name property to 'datOwners'
- Set the Visible property to False
- Use the same ConnectionString in this control as you did the previous datPets
- Change its RecordSource to the Owners table
So that control is now setup to retrieve information direct from the Owners table. Now we plan to send those details to the DataCombo control and tell it to display the owner name but when inserting into a database, use the OwnerID field.
We do this using a few supercool properties:
- RowSource the data control from which the combo box gets its initial wad of information
- ListField the field that will be listed in the combo box
- BoundColumn the underlying value or 'Key' field (eg, OwnerID)
So let's change our Data Combo properties to:
- RowSource datOwners
- ListField OwnerName
- BoundColumn OwnerID
Now add a couple of other text boxes to store the pet name and pet breed values.
Link all three of your controls up with the datPets ADODC control by changing the DataSource and DataField properties.
My completed application looks a little something like this:
If yours doesn't look as good as mine, well... it takes years of practice.
If it looks better than mine... well, I don't want to discuss it. I'll start getting grumpy and upset. I may even go for a sulk somewhere in the corner of cyberspace.
Anyway, hit F5 to run your application. Does it work?
Err, as this is a kinda one-way thing... I'll presume you answered with a resounding "yes!"
You should be able to scroll backwards and forwards through your Recordset using the ADODC control. You should also be able to change the owner of a pet very easily using the Data Combo box whilst at the same time keeping physical numbers in the database, not text values.
So there we have it a basic application built on a solid database design with absolutely no code.
Why not try creating another table to hold all the pet breeds and linking into those with another key? Go on, I dare you!