Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.


Channels ▼
RSS

Database

Database Development and Visual Basic 3.0


MAR94: Database Development and Visual Basic 3.0

Putting a pretty face on client/server architectures

Ken has developed DBMS projects for mainframe, mini, PC, and client/server systems and is currently writing a book about Windows multi-DBMS programming for Wiley & Sons. Contact him at Resource Group Inc., 2604B El Camino Real, #351, Carlsbad, CA 92008, or on CompuServe at 71301,1306.


Although Microsoft calls the current incarnation of Visual Basic the "Visual Basic 3.0 Professional Edition," it wouldn't be stretching it to refer to it instead as "the Visual Basic Database Edition" because of the database orientation of its tools and functionality. In this article, I'll examine the Visual Basic 3.0 environment and related tools, presenting in the process a multimedia database application that uses a local Access database (MDB). I'll also discuss how you can revise this database so that you can use it with ODBC and a local dBase (DBF) file. Of course, you'll need an MDB, DBF, and ODBC data source with comparable table definitions.

Visual Programming

You produce Visual Basic (VB) applications by combining components in a systematic manner. The visual programming model promotes a division of labor between specialists who write custom controls and those who build applications. In most cases, the process of building an application involves selecting components and writing code that binds the pieces together. With VB, you create screens by defining forms containing Windows controls such as scroll bars, list boxes, text boxes, labels, and other user-interface objects. For each object, you set properties and generate code (methods) that executes in response to events.

VB 3.0 includes an integrated database engine (the Access Engine), a database manager that works with multiple DBMS formats, support for OLE 2.0, messaging (MAPI), database connectivity (ODBC), multimedia (MCI), graphics (Graphics Server), and report generation (Crystal Reports). VB does not support pure object-oriented programming, DOS text mode, or an Xbase dialect. The large number of plug-in custom controls from third-party developers, however, covers a range of functionality including telecommunications, fax, spreadsheets, calendars, imaging, graphics, data management, and GUI objects; see the text box entitled, "Add-on Data-base Tools for Visual Basic."

Custom Controls

Custom controls are essentially plug-and-play components packaged in DLLs. Microsoft supports a specialized DLL--the VBX (Visual Basic eXtension)--with its VB and Visual C++ (VC++) compilers. The Professional Edition of these compilers includes 19 custom controls and a Control Development Kit (CDK). Developers who write custom controls using the CDK may produce three levels of controls based on compatibility with VB 1.0, 2.0, and 3.0. VC++ programmers can use VBXs compatible with VB 1.0.

Also noteworthy is that Microsoft Foundation Classes 2.0 (included with VC++) contain classes that implement a VBX-emulation layer for VB 1.0 controls. Controls used with C++ do not have the full functionality of VB 2.0/3.0 controls in several areas, including drag-and-drop behavior, run-time controls, container controls, and control arrays.

The Database Connection

One of the benefits of VB 3.0 is the separation of the front end from the back end of a database application. In A Guide to Developing Client/Server SQL Applications (Morgan Kaufmann, 1992) Khoshafian et al. describe 12 rules for client software that include a client-autonomy rule whereby client software shall behave the same whether processing data at the client or server. VB 3.0 provides that type of transparency: A user can run an application using virtually the same user interface whether the application operates in native mode on an MDB, attaches to local tables in other database formats, or connects to an ODBC server. VB provides a good vehicle for prototyping and debugging using the Access Engine for projects that run on an SQL server in their final form.

JET, the Access 1.1 Engine, reads and writes databases in a variety of formats including Access, dBASE III and IV, FoxPro 2.x, Btrieve, Paradox 3.x, and a number of SQL (ODBC) databases. The Access Engine also provides query optimization, transaction processing, optimistic and pessimistic locking, distributed multitable joins, and so on. However, although VB programmers can manipulate Access's data, VB doesn't support unique Access features such as forms and macros or the ability to run Access applications. Further, VB programs cannot implement Access-style security from within VB.

To develop a database application with VB, you can either use the Object Layer or data controls, both of which are supported by the Access Engine. The Object (or Programmatic) Layer is VB's option for programmers who feel comfortable writing code. Data-aware controls (or more commonly, data controls) are an easy-to-use noncode solution that constitutes VB's Visual Layer. On the other hand, some developers bypass the JET engine because they've chosen other means for building database applications. VB provides alternatives such as third-party database libraries and the ability to write directly to the API of database DLLs such as ODBC.

Bound controls--data-aware controls that simplify access to a database--are a major new enhancement for database developers. VB associates a bound control with a specific database column or field. You create the association by binding the control to the data control--a scroll bar associated with a dynaset query (a dynamic query result set), SQL statement, or QueryDef. Bound controls work with the Access Engine while the data control is associated with Dynaset, Snapshot, or tables. The Professional Edition has eight bound controls, and the Toolbox shows icons for Check box, Image, Label, Picture box, Text box,

3-D check box, 3-D panel, and Masked edits. Combos and list boxes are not bound.

Finally, it's possible to bypass the engine using the SQL pass-through option, a tool for obtaining better performance and executing stored procedures at the server. However, you must manage your own connections if you use ExecSQL. VB and Access write to level 1 of the ODBC API so not all of the new features work with every ODBC driver. If you work with core-level ODBC drivers, you must continue to write directly to the ODBC Call Level Interface (CLI) and forego the ODBC abstraction and new controls (often preferable for performance reasons). Access and VB provide an option to bypass the Access Engine's SQL parse of the SQL and pass it through to the ODBC data source. To use the pass-through option and bypass the parse, set the data control's Option property to SQL_

PASSTHROUGH. VB 3.0 supports transaction processing with three statements (BeginTrans, CommitTrans, and Rollback) that are preferred to the database methods from prior releases.

VB's data-access capabilities are not the only features useful for building database applications. Others include extensions for communications, object linking, report writing, graphics, and mail. VB includes Pinnacle's graphics library, Graphics Server SDK, and the Crystal Reports database report writer. Crystal Reports for Visual Basic includes a VBX that provides approximately 15 of the 60 calls in the full API for the Crystal Reports print engine. The VBX is available at design time but hidden at run time. Crystal Reports for Visual Basic doesn't include the report compiler of Crystal's Pro Edition, which provides the ability to compile reports into a stand-alone executable. Notably, Borland also bundles Crystal Reports with its database products.

Another feature, OLE automation, permits objects to publish a set of commands and functions available to other apps (CreateObject) and subscribe to those features in other applications (GetObject). OLE automation is a standard feature in Microsoft applications; see the accompanying text box entitled, "Visual Basic for Applications." You can use the OLE tool to add OLE 2.0 automation to your applications, but VB doesn't support the full set of OLE 2.0 features.

The VB 3.0 CDK lets C++ programmers create new VB controls. The principal change to the CDK for 3.0 was the addition of functions and messages for data-aware controls.

VB isn't the best platform for collaborative projects because version-control tools that support code sharing for multiprogrammer projects do not compare with other languages. Microsoft has announced an upcoming version-control system, but not a release date. Consequently, the most common current approach is to save forms in ASCII.

A Multimedia Database

Video and database technologies are a natural marriage for some applications. One such example is an industrial database, where video is used as a tool to familiarize workers with physical layouts to minimize exposure to hazards or radiation. The program presented in this article is a typical database that includes identifiers and evaluation data. The example is for a tennis academy, although you can easily modify it to fit different applications (a personnel database, for example). Once the application has been created, I'll examine the revisions needed so you can use it with ODBC and a local dBase (DBF) file. The files you'll need for this project are available electronically; see "Availability," page 3. The example also uses the MCI custom control and Microsoft's Video for Windows to play video clips. To use this feature, you must install the files from the \RUNTIME directory of the Drivers diskette of Video for Windows. Alternatively, you can download VFWRUN.ZIP from the WINEXT or MSAPPS forum libraries on CompuServe.

The first step in creating the participant form (PERSON.FRM) is to use a data control, which resembles VCR controls. This 3.0 control permits a user to step through query data using four buttons: MoveFirst, MovePrevious, MoveNext, and MoveLast. To associate a form with a table in the database, select the Properties window, double-click on the DatabaseName property and select an Access filename from the DataBaseName dialog box. You must follow a similar procedure to select the query to run against the database by specifying a table name, an SQL statement, or a stored SQL query for the RecordSource property.

The next step in the process is to add a number of bound controls linking the field on the form to a column in the table. Associate all data-aware controls with the data control and set all of the DataSource properties to the Data1 control. To do this, place the control on the form and complete its DataField property. VB will display a list of tables in the database. Following the design of the form, the next step is to add data-validation logic. Click on the data control, select the Validate event and enter the code for your field validation. The person form (PERSON.FRM) of our example project validates the information in three bound text controls. The row or record must have a surname, a gender of M or F, and a category or rating between 10 and 70.

To test the ODBC links, I used a previously installed testbed of ODBC software and drivers. The next step was to install and test using more recent drivers from Q+E Software's ODBC Pack and Microsoft's Desktop Database Driver Pack. To use the participant form with an ODBC database, you'll need to blank the DatabaseName property. Then, you need to complete the information in the Connect property. For example, if the name of the ODBC data source is PERSONID, enter ODBC;DSN=PERSONID in the Connect property field and select the appropriate table name from the list associated with the RecordSource property.

If you set the Connect property to ODBC and don't enter a DSN string, VB will attempt an SQLDriverConnect, which will prompt you with a list of ODBC data sources. If you want to create a result set (dynaset) from an SQL query, enter the SQL statement in the RecordSource property. For example, it might be common to view participants in rating order by entering SELECT * FROM IDENT ORDER BY CATEGORY for the RecordSource property. To use an attached dBase table, you need to set the Connect property to dBase III or dBase IV, specify the directory name for the DatabaseName property and specify the table name as the RecordSource property.

Conclusion

Visual Basic is a versatile tool for client and client/server applications. The application presented here requires only minor changes to run with a variety of database formats; the data controls provide quick and easy access to data even though they are not an optimal solution for high-performance applications.

As an environment, VB often provides more than one solution, so you must evaluate the trade-offs between using VB at a high level of abstraction or getting down and dirty while writing to APIs. VB invites trade-offs because there are some projects where performance is critical, and others where ease-of-development is paramount. If you opt to write at the API level, you must learn hundreds of functions (including 50 for Crystal Reports and another 51 for ODBC) instead of pointing and clicking at the VBXs' counterparts. When evaluating whether to jump in with VBXs or write directly to the APIs, consider the sheer volume of information: The ODBC 1.0 reference manual, for example, is some 629 pages, the Graphics Server SDK almost 400 pages, Video for Windows about 250 pages, and the OLE 2.0 manual nearly 700 pages. Throw in the Win and the Win32 APIs and you won't want to see a bookstore anytime soon.

Visual Basic for Applications

Visual Basic for Applications, formerly called "Object Basic," is the embedded language of Microsoft's applications. Access Basic, WordBasic, and Visual Basic are older cousins of the latest VB edition that includes enhancements in areas like macro recording, transportability, and object support. VBA is the new programming tool for Excel 5.0 on the PC and the Macintosh, so the macro recorder now generates Visual Basic.

The concept of programming with objects is familiar to developers using VB dialects. Access Basic and Visual Basic include objects such as Form, Dynaset, Report, and Database. Excel now has Cells, ActiveCells, Ranges, and collections like Sheets, Worksheets, Charts, and Workbooks. Each edition of VB introduces more features familiar to programmers using OOP languages such as Smalltalk, Actor, and C++. The implementation of VB available with Excel includes an Object Browser, a familiar tool to OOP developers. Although the OOP-language browsers usually depict a class hierarchy within an application context, the Object Browser in VBA shows objects from other applications. An Excel user might browse and use objects from Word for Windows, FoxPro, PowerPoint, Mail, Access, Project, Publisher, or other applications that support OLE 2.0. VB programs reference objects in other applications by statements such as that shown in Figure 1(a). It is possible to use objects from EXEs or DLLs and to activate other applications and send keystrokes; see Figure 1(b).

Programmers familiar with the various implementations of Lisp and REXX may be surprised to learn the extent to which VB has become a full featured programming language for multiple computing platforms. In addition to VBA development, Microsoft is porting the VB Programming System to the Macintosh and Windows NT. NT 3.1 and OLE 2.0 are the forerunners to Cairo, Microsoft's object-oriented operating system.

Microsoft is clearly positioning VB as a flexible tool for development with objects (OLE, Windows, and NT today; Mac and Cairo in the future). In the not-too-distant past there was a lot of interest in microprogrammable computers, those adaptable to specific application needs by using a writable control store and microcoded machine instructions. Lisp and Pascal machines were computers whose microcode gave the hardware a Lisp or Pascal personality. The industry today is moving to RISC, not extensible architectures, but you wonder what the planners at Redmond would do with an object-Basic machine.

--K.N.


Figure 1: (a) Referencing objects in other applications from Visual Basic;

(b) using an object from an EXE or DLL.

(a)
     Set MSWord = CreateObject(class:= "Word.Basic")
     Set Picasso = GetObject("guernica", "MSDraw.Metafile").
     Set Linked Drawing = Sheet1.OLEObjects.Add (filename:="guernica", _link := True)
     LinkedDrawing.Update

(b)
     AppActivate title := "Terminal"
     SendKeys String := "{TAB},{F1},{enter}"

Add-on Database Tools for Visual Basic

The Visual Basic database add-on community includes many tool vendors competing to fill specific niches. One factor that gives some of these tools an edge is their usability with other languages. Developers working in multiple languages, or groups that include programmers with different language skills, can benefit from the ability to add libraries or custom controls that work with more than a single programming language.

Microsoft bundles Crystal Reports from Crystal Services (Vancouver, British Columbia) and Graph from Pinnacle Publishing (Kent, Washington) with Visual Basic Professional Edition. However, both Visual C++ and Borland C++ 4.0 developers can add similar functionality. The Graphics Server SDK from Pinnacle Publishing produces almost 20 graph types using installable components for C/C++, SQLWindows, Turbo Pascal, Powerbuilder, Actor, and Superbase. Graphics Server's architecture is similar to some Windows SQL engines. A single copy of the server sits in memory and services graphic requests in the form of DDE conversations or calls to one of the approximately 170 functions in its DLL (GSWDLL.DLL). Graphics Server manages the windows, and your application works with views in a manner similar to that of non-Windows graphics software. The coordinates in your program are relative to a view, not a window. GS SDK's geometric functions include lines, circles, ellipses, filled areas, and polygons. The text functions include titles, text, and numeric labels and legends.

Q+E MultiLink/VB from Q+E Software (Raleigh, North Carolina) is a "middleware" tool that links VB applications to PC and SQL databases--Oracle, Sybase, Ingres, SQL Server, dBase, Clipper, Paradox, and Btrieve. The tool, which supports ODBC, includes 20 database drivers and 60 new properties. Applications using these database drivers may be distributed royalty free. MultiLink/VB also provides a number of development tools including a database manager, custom controls, and the Q+E Builder. New properties include edit masking, mapped list, combo boxes, and string searches in combo and list boxes.

VBAssist from Sheridan Software (Mellville, New York) adds more than 25 tools to the VB environment, including a Data Assistant to bind custom controls to databases via drag-and-drop; a Form Wizard that generates database forms which can contain a bound label, text fields, and navigation buttons; and DB Assistant, a tool that allows the creation, testing, and maintenance of an Access database from within VB. Data Widgets, also from Sheridan, is a set of bound controls for creating front ends to database applications.

Integra VDB from Coromandel (Forrest Hills, New York) includes an SQL engine and VBX controls for VC++ and VB. It includes Architecture controls and Visual controls. The Architecture controls are invisible abstractions of database objects. The Visual controls include a grid, text box, frame, radio button, combo box, list box, and a database-operations control. The Architecture VBXs include application, data-source, query, form-definition, and form-builder controls. Integra VDB also has a call-level interface of approximately 50 functions, including a trigger function which supports master-detail lookups that fire when the user activates bound visual controls.

TrueGrid from Apex Software (Pittsburgh, Pennsylvania) allows VB programmers to create database browse tables that are editable and fully configurable. To create a browse table, you drop TrueGrid onto a VB form and set the form's DataSource property. TrueGrid supports the same database formats as VB, but also allows you to create browse tables for custom databases. The tool also includes an expression string syntax for calculating fields, and a design-time layout editor for interactive design.

Finally, because the Access Engine does not support Clipper index files (NTXs), A.S. Inc. (Minot, North Dakota) has created vxBase 3.0--a shareware function library for VB or C/C++ programmers. vxBase includes a number of functions, expressions, and operators whose syntax is similar to Xbase. A VB programmer can manage Clipper and dBase III/III+ data, memo, and index files with familiar functions such as vxAppendBlank, vxPack, and vxZap. vxBase includes multiple DBF, NTX, xBase, Multiuser, Record Navigation, Memo, Logical, Date, Numeric, Field, Char, Record I/O, File, Browse, Memory, and Windows Interface functions. Programmers opting for the commercial product can order a developer's kit that includes a royalty-free run-time DLL.

--editors


Copyright © 1994, Dr. Dobb's Journal


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.