LONG: Overview of Data Access in Visual Basic Version 3.0

- Professional Edition of Microsoft Visual Basic 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:

1. Flat File 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.

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:

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

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

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

1. Information is represented as values in tables.

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

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

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

2. 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.

3. 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.

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

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.

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

2. 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.

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:

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

2. 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.

3. 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.

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

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

2. Add four list boxes to the form.

3. 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
   
   

4. 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.

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

6. 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
   
   

7. 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.

8. 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.