In our last tutorials we learned to use VB's menu editor and some of the predefined VB objects to create general
Cut, Copy, Paste and Delete commands. In this tutorial we stick with visual programming, designing a form to display
database table data. At the sametime we learn how to access the dozens of extra VB controls that are available
to VB programmers - and many of these control come "free of charge' as popular programs like Word, WordPerfect,
Excel, Visio and others expose their underlying components for use by VBA, VB, Delphi and any other COM enabled
development tools. This is one of the major attractions of Microsoft's ActiveX components - the downside is that
unlike Java's JavaBean components which run anywhere - ActiveX only work in Windows.
Now before I get inundated with e-mails telling me that ActiveX and COM components are available in various flavors
of Unix using software supplied by Bristol, Chilisoft, Mainsoft, Microsoft, Software AG and others - let me warn
you, dear readers, this about a 1/3 truth; particularly for many of the popular GUI based ActiveX controls. If
you need to make an ActiveX component work in AIX, Linux, HP/UX, AS/400, SCO-Unix, Solaris or even WinCE and Win2000,
be sure to check with the vendor and make certain the ActiveX works in the OS environs you need to serve.
Figure 1 - VB Components Dialog
|Activating Components in VB
Figure 1 shows the Component dialog used to add a new control to VB's toolbar. Click on the Projects | Components
menuitem to bring up this dialog. We have just clicked on a commercial ActiveX component - the Videosoft vsOCX
6 control which costs about $149US. (Note we use ActiveX, OCX, component and control interchangeably, they mean
essentially the same thing.) This component will allow us to control the spacing and resizing of other controls
put inside it - vsOCX is called an elastic control, and with good reason as we shall shortly see. The other control
needed is the Microsoft DataBound Grid Control 5.0. which will allow a database to be displayed in a grid or spreadsheet
format. Again we click on the component's checkbox and then click
|on the Apply button. Then the icon for the controls appear in VB's Toolbar. The vsOCX control works as a smart
container. We put three vsOCX elastic controls on our new form. The first is aligned along the top of the form,
the second aligned along the left side and the third fills in the lower bottom right of the form and is set to
fill for its alignment. The fact that most readers cannot duplicate these actions except by downloading a trial
version of the vsOCX control from www.videosoft.com does not matter because
we will be doing a before and after comparison of the effects of using the vsOCX control in our database program.
In short, don't worry if you don't use the vsOCX control, your program will be the before case yet still quite
Finally, a last word on using ActiveX. In the Component dialog users can find out how much using an ActiveX is
going to add to the runtime or compiled version of their final program. In Figure 1 above, the vsOCX6 control is
checked off and its location is shown as C:\WINDOWS\SYSTEMS\VSOCX6.OCX. If you click the Browse button to the right
, you can immediately check the size of the OCX in the popup file dialog. It turns out that vsOCX6 is 247K and
the Microsoft Grid Control OCX (DBGRID32.OCX) is 514K. So 761K will be added to the size of the program. In sum,
be judicious in your use of OCX, adding a dozen or so can quickly bloat up program size. Always check the runtime
size of a new OCX
At last we get to do database work in VB. Database capabilities in VB are good, bad and ugly. Later in the course
we will be recommending Roger Jenning's Database Developers Guide to VB6 which is 1200 pages devoted to VB and
databases. The good about database in VB is that it is so easy to setup and use (as we shall see shortly). The
bad is that there are so many different competing methods to access databases in VB (Jet, ODBC, RDO, ADO are 4
of the most important out of about a dozen methods). The ugly is that choosing among even the four most popular
methods can be a nightmare. For example, Jet is the most popular for its ease of programming; ODBC connects to
just about everything and is fast but more difficult to program; RDO is the former standard; and ADO is the new
Microsoft standard - it is very powerful, relatively easy to program, but complex to the point of being hazardous
in terms of runtime performance or reliability if not coded properly. Microsoft is pushing users to the OLE/DB
based ADO - but some performance problems and availability of well tuned OLE/DB drivers plus the newness of ADO
have slowed the migration somewhat. So we will take a gradual approach; using Jet methods to get acquainted with
VB database concepts and then graduating up to ADO later in the tutorials.
Figure 2 below shows the four controls we are going to use in our database sample program - two texboxes, a navigation
bar, and the newly loaded database grid control. The whole trick is how to wire these controls up properly. So
Figure 2 - Elastic Database Program before Resizing
Figure 3 - Elastic Database Program after Resizing
|four controls to a new form, call it DBeasy.
First add the data control or navigation
bar (its icon is shown just above) to the form. We will need to set three important properties in the data control.
The first property, Connect, tells what type of database we are connecting to. Set Connect to Access. The second
property, DatabaseName identifies the specific Access database to be used. When you highlight the DatabaseName
in the property sheet a button with three dots in it will appear. Click on this button to browse for the NWind.mdb
Access database. (you may need to use Windows Explorer's Tools | Find | File command to locate the database beforehand).
The third property you want to set is Record Source. Again, when you highlight RecordSource in the properly sheet
a combobox appears. Pull it down and click on the Product table. With these simple three steps you have let VB
know what type and the name of the database to use and then what specific table from that database will data be
extracted from. We now have wired up the data control ready for database access.
|Next add the database grid control (its
icon is shown at the left) to the form in the lower portion as shown in Figure 2. To wire up the database grid
control, we have to set two properties. The first, DataSource, can be set in the property sheet. Just choose Data1
from DataSource's combobox. Next, click anywhere on the grid control with the right mouse button and a popup
menu will appear. Click on RetrieveFields and the column headers will appear in the database grid control. The
last step will be to add two textboxes in which the ProductName and UnitPrice fields from the Product table
will be displayed.
Add two textboxes as shown in Figure 1. The second textbox is deliberately being made oversized to display the
elastic effects - UnitPrice will easily fit inside it. Here is how to wire up the textboxes for displaying database
fields. For the first textbox, set its DataSource property to Data1. Then highlight the DataField property
and then click the ProductName field in the pulldown combobox. To set up the Text2 field again, set the DataSource
property to Data1 and then the DataField property to UnitPrice. Voila, we are ready to roll.
Run the program and click on the navigation bar right arrow. The highlight move down in the database grid control
while the ProductName and UnitPrice change in the textboxes. This show how easy it is in VB to get at and display
data from a database. Now lets try one more thing. Resize the form using the maximum button or pulling with the
mouse on the edge of the form. Note what happens - the contents of the form do not resize intelligently unless
you have put in the elastic controls. Figure 3 shows the form with elastic controls after they have been resized.
The lower textbox and data grid have grown proportionally. In the case of the textbox this does not matter because
it contains UnitPrice; but if it had a Note or Description this "intelligent" resizing would really help.
In sum, VB will not automatically resize the controls you put on a form - you must handle it yourself (the programming
is tricky) or buy a component like vsOCX to handle it for you. In future tutorials we shall highlight some other
useful third party components.
In this tutorial we have shown how to add new components to the VB toolbar while developing a simple database
display program. In developing this program, there was absolutely no code to write - it all involved "wiring
up" and setting the right properties for the controls used. Later on when we explore VB database development
in more detail, the situation will be reversed - there will be plenty of coding to do.
The book Beginning VB6/Wrox covers VB components on pages 589-620 and starting
database on pages 431-451 ; VB6 Black Book/Coriolis touches on some of these topics
on pages 970-975 and scattered throughout the book. VB6 BlackBook
covers database concepts on pages 821-835.