LONG: Overview of Data Access in Visual Basic Version 3.0

ID: Q108379

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, version 3.0

SUMMARY

This article provides an overview of data access in Visual Basic version 3.0. It contains following sections:

Data Access In Visual Basic Version 3.0 Versus 2.0

Relational Database Theory

Visual Basic Can Use SQL

Visual Basic Uses the Microsoft Access Database Engine

Data Access Object Hierarchy

MORE INFORMATION

DATA ACCESS IN VISUAL BASIC VERSION 3.0 VERSUS 2.0

Historically, database management systems (DBMS) represent some of the most mission-critical and most complex programming challenges in the field of computer science.

Visual Basic version 3.0 ships with full-featured, multi-faceted data access capability. It has a full range of connectivity, flexibility, and support for Open Database Connectivity (ODBC) on the Microsoft Windows platform.

Database features were introduced to Visual Basic in version 2.0. The upgrade to Visual Basic version 3.0 offers much easier and much more powerful database management.

Microsoft added new data methods, objects, and properties in Visual Basic version 3.0. Version 2.0 was not able to create an updatable Dynaset on an entire table, and could not navigate the Dynaset with methods other than MoveNext. Visual Basic version 3.0 can do both. In version 3.0, Dynasets created using SQL statements are updatable, and there are three new move methods (MovePrevious, MoveLast and MoveFirst).

Programmers are much more likely to face network architecture issues in Visual Basic version 3.0 than with version 2.0. This is because of the fuller implementation of ODBC and the inclusion of the Microsoft Access engine and its ISAM database connectivity.

Some programmers use Visual Basic version 3.0 as the center of mission- critical database management systems connected to proprietary databases. For example, a Visual Basic version 3.0 application could be written as a front end for an enterprise-wide information system containing data in formats for SQL Server, Oracle, dBASE and FoxPro. The Visual Basic application could integrate data from all these sources and from multiple servers. The performance of a system such as this depends significantly on the network behavior and back-end systems outside of Visual Basic.

RELATIONAL DATABASE THEORY

Database Models

The field of computer science has evolved four models for databases, in the following order of progressing theory and technology:

Flat File Database

Hierarchical Database

Network Database

Relational Database

The relational model is a major step forward for database programmers. With the relational model, none of the physical and logical pointers between records are exposed to the programmer. The relational database handles all low-level structure. A relational database management system (RDBMS) makes database programming much easier and more flexible than earlier database systems.

Relational Database Model

Visual Basic uses a relational database model. The relational database model offers the following benefits:

Organizes data in a collection of tables making the design easy to understand.

Provides a relationally complete language for data definition, retrieval, and update. It is non-procedural and criteria-based.

Provides data integrity rules that define consistent states of the database to improve data reliability.

A relational database management system (RDBMS) is software that allows you to represent your data according to the relational model. Both the programmer and the user think in terms of groups of tables comprising the database, with tables composed of rows and columns. The data in those rows and columns relate to each other according to a consistent theory and practice.

Relational databases support a standard language called Structured Query Language (SQL). SQL has evolved into a comprehensive language for controlling and interacting with a database management system (DBMS). SQL is now a standard approved by the American National Standards Institute (ANSI).

SQL provides three important functions:

Data Definition -- to define the tables that hold the data.

Data Manipulation -- to insert, update, or delete information stored in tables.

Data Control -- to prevent access to private data in the database.

Dr. Codd, considered the father of relational database theory, has defined twelve conditions that a database must obey to be considered fully relational, and he defined three criteria for a minimally relational DBMS:

Information is represented as values in tables.

Internal data structures and pointers are not visible to the user.

The DBMS language supports at least the following syntax: SELECTION, PROJECTION, and JOIN.

These three criteria are necessary and sufficient for a minimally relational definition because of the following:

The relational operations only work on tables, therefore all the data must be in tables.

If internal data structures and pointers were visible to the user, the data would not appear to be in a table. It would appear to be in some DBMS dependent structure.

Without SELECTION, the DBMS could not perform operations on subsets of the table. It would be forced to operate on the entire table. In effect, it would be just a file handler.

Without PROJECTION, the DBMS could only perform operations on an entire row. Therefore, it would be just a unit record handler.

Without JOIN, data could not be correlated across tables. It would not be a related database, just a collection of unrelated tables.

The following additional terms are associated with relational database theory:

primary key
foreign key
null values
duplicate values
updatable values
derivative data
constraints
referential integrity

For more information on relational database theory, refer to any of the books listed in the BIBLIO.MDB database in Visual Basic version 3.0.

VISUAL BASIC CAN USE SQL

The Microsoft Access engine included with Visual Basic version 3.0 uses a dialect of Structured Query Language (SQL). This dialect is based on the ANSI 1986 standard and differs from that of Microsoft's SQL Server in certain syntax. For that syntactical reference, please refer to Appendix B of "Microsoft Visual Basic 3.0: Professional Features Book 2: Data Access Guide."

The SQL parsing capability of the Microsoft Access engine adds considerable power and flexibility to Visual Basic. SQL gives database programmers and users more leverage and a standardized approach to querying databases.

VISUAL BASIC USES THE MICROSOFT ACCESS DATABASE ENGINE

Visual Basic version 3.0 uses the database engine from Microsoft Access version 1.1. This engine provides data access to many database formats, including Microsoft Access, FoxPro, dBASE, Paradox, Btrieve, SQL Server, Oracle, and other formats that support the ODBC specification.

The Microsoft Access database engine in Visual Basic version 3.0 provides the following:

Provides a query engine

Supports multi-user applications

Allows for transaction processing

Offers choice of optimistic or pessimistic locking

Supports rich data types such as sound, video, OLE objects, and pictures

Parses SQL

Performs distributed joins, such as joining a FoxPro table with an Oracle table

Performs updatable queries and query optimization

Supports international collating orders.

In Visual Basic, you can harness the database engine in two different ways:

By writing code using the data definition language (DDL) and data manipulation language (DML). This involves dimensioning and using database object variables.

By using the data control and bound controls. Bound controls include the text box, label, check box, image control, and picture control in the Standard Edition of Visual Basic, plus the masked edit, 3DPanel, and 3DCheckBox in the Professional Edition. You can enable data access without code by setting design-time properties or by setting properties in run-time code.

Programmers can handle database objects easily in Visual Basic code. The object layer provides a uniform system catalog, independent of whether the database is a Microsoft Access database or an external database such as an ODBC or ISAM database. You can gain access to the hierarchical structure of the system catalog by using the TableDef objects in the TableDefs collection of each database.

Component Model of Data Access in Visual Basic

The architecture of the database components is the same for Microsoft Access version 1.1 and Visual Basic version 3.0.

You can access three types of databases from Visual Basic:

Microsoft Access databases, which are native to Visual Basic's database engine. Visual Basic can use Microsoft Access databases directly.

Indexed sequential access method (ISAM) databases, such as dBASE, Paradox, and Btrieve databases. Visual Basic reaches these databases through user-installable drivers that link Visual Basic to the specific databases.

Open Database Connectivity (ODBC) accessible databases. These include client-server database management systems (DBMS), such as Microsoft SQL Server and ORACLE. Visual Basic reaches these databases through the appropriate ODBC drivers.

Various gateways are also available to connect to databases on mainframe computers. This is usually implemented through an ODBC driver.

DATA ACCESS OBJECT HIERARCHY

At the top of the database object hierarchy is the Database object, not to be confused with the Database property of the data control. One of the properties of the Database object is the TableDefs collection, which is also an object. The TableDefs collection represents all the individual TableDef objects associated with the Table objects. Please read further about objects in the NOTE sections in the sample program below.

Step-by-Step Example Shows How to Use Database Objects

Start a new project in Visual Basic. Form1 is created by default.

Add four list boxes to the form.

Add the following code to the Form Load event:


   Sub Form_Load ()
      form1.Show
      Dim MyDb As Database
      Dim MySingleTableDef As TableDef
      Dim AllTableDefs As TableDefs
      Set MyDb = OpenDatabase("BIBLIO.MDB", True, False)
      Set AllTableDefs = MyDb.TableDefs
      For i = 0 To AllTableDefs.Count - 1
         ' Only Count property is applicable to top-level Tabledefs object
         list1.AddItem AllTableDefs(i).Name ' Get each table name in MyDb
         list2.AddItem AllTableDefs(i).DateCreated
         list3.AddItem AllTableDefs(i).Updatable
         list4.AddItem AllTableDefs(i).Attributes
         ' Value property is only valid if part of a recordset:
         ' list5.AddItem AllTableDefs(i).Value
      Next i
   End Sub

Start the program or press the F5 key. Examine the contents of the list boxes. Close the form to end the program.

NOTE: Using the values of the Name property of the TableDefs object (the top-level collection), you can examine the properties of the TableDef object of the individual tables as shown below. You can walk through the Fields collection of the TableDef object of the individual tables using the Count property. The Count property is the only property of the collection objects. The collection objects are Fields, TableDefs, and Indexes.

Add four more list boxes to the form, numbered 5 through 8.

Append the following code to the existing code in the form load procedure:


   ' Get information on the first table listed on list box 1:
   Set MySingleTableDef = MyDb(list1.List(0))
   For i = 0 To MySingleTableDef.Fields.Count - 1
      list5.AddItem MySingleTableDef.Fields(i).Name
      ' or you can use: list5.AddItem MySingleTableDef(i).Name
      ' because Fields are the default collection.
      list6.AddItem MySingleTableDef.Fields(i).Size
      list7.AddItem MySingleTableDef.Fields(i).Type
      If i <= MySingleTableDef.Indexes.Count - 1 Then
         list8.AddItem MySingleTableDef.Indexes(i).Name
      End If

      ' The Value property is only valid if part of a recordset:
      ' MySingleTableDef.Fields(i).Value
      ' The other 5 properties are valid for a field of a TableDef object:
      ' MySingleTableDef.Fields(i).OrdinalPosition
      ' MySingleTableDef.Fields(i).CollatingOrder
      ' MySingleTableDef.Fields(i).Attributes
      ' MySingleTableDef.Fields(i).SourceField
      ' MySingleTableDef.Fields(i).SourceTable
   Next i

Start the program or press the F5 key. Examine the contents of the list boxes. Close the form to end the program.

NOTE: The Field and Index objects are contained in the Field and Index collections of the Table and TableDefs objects. The following code shows this.

Append the following code to the existing code in the form load procedure:


   msgbox "Next, show indexes for the " & MySingleTableDef.Name & " Table"
   list5.Clear
   list6.Clear
   list7.Clear
   list8.Clear
   For i = 0 To MySingleTableDef.Indexes.Count - 1
      list5.AddItem MySingleTableDef.Indexes(i).Name
      list6.AddItem MySingleTableDef.Indexes(i).Primary
      list7.AddItem MySingleTableDef.Indexes(i).Unique
      list8.AddItem MySingleTableDef.Indexes(i).Fields
      ' property of Index object: indicates simple/composite keys
      ' Determines which TableDef fields are key fields in an index.
      ' Read-only when the Index is a member of a collection.
      ' Read/write only in the Professional Edition
      ' with a new object not yet appended to an Indexes collection.
      ' An Index object has field(s) representing key values
      ' for each record. Field names are separated by semicolons.
   Next i

REFERENCES

"Microsoft Visual Basic 3.0: Professional Features Book 2: Data Access Guide."

The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. You can refer to the VISDATA source code for examples of how to use each data access function.

Additional query words: 3.00 1.10 2.00


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: June 18, 1999