Database Access with Visual Basic (1997).pdf

Database Access with Visual Basic

(Publisher: Macmillan Computer Publishing)

Author(s): Jeffrey Mcmanus

ISBN: 1562765671

Publication Date: 10/01/97

Introduction

It’s probably safe to say that the majority of Visual Basic developers will use VB to access databases sooner or later. So why aren’t there more

books on databases and Visual Basic? Because database access is the equivalent of plumbing. Like plumbing, there are dozens of segments you

must put together before the whole thing works, and when it doesn’t work, the part that’s causing the problem isn’t immediately obvious. Put

bluntly, it’s a decidedly unsexy topic.

This book puts the plumbing in order. It won’t help you hike up your pants when you reach under the sink, but it will give you exposure to nearly all

aspects of database access you’re likely to encounter in Visual Basic today. Through it all, the objective is to give you the information you need in

a concise manner, using examples and step-by-step procedures rather than brief, acronym-laden blurbs.

One common misconception about VB is that it’s only good for building database front-ends. But as this book shows, Visual Basic 5.0 is not your

father’s VB. If you’re one of the thousands of developers migrating to VB 5.0 from version 3.0, you’ll notice an even bigger difference. In fact, the

majority of material covered in this book — ActiveX components, Remote Data Objects, ActiveX Data Objects, and SQL Server 6.5 — weren’t

available in version 3.0. It’s clear that Visual Basic has come of age as a software development system, and the success of VB 5.0 bears that out.

If you use this book and find it helpful, I’d be interested to hear from you. If you find an element of this book less than helpful, I’d like to know that,

too. And if you’ve used the book to create something cool and just want to crow about it, feel free to drop me a line as well. My email address is

jeffreyp@sirius.com, and the Web page for this book is at http://www.redblazer.com/vbdb/ . This Web site will also contain updates to and

corrections for this book.

This book was written in the Summer, Fall and Winter of 1997-98 in San Francisco, California, Pittsburgh, Pennsylvania, Brooklyn, New York,

Stamford and Mystic, Connecticut, Chaska, Minnesota, Princeton, New Jersey, Jacksonville, Florida, Berlin, Germany, Halifax, Canada, and

Boulder, Colorado.

Introduction

How to use the Companion CD

Acknowledgement

Chapter 1—Database Basics

What Is a Database?

What Is a Database Engine?

Tables and Fields

What Is a Recordset?

Data Types

Creating a Database Schema

Creating a Database Using Visual Basic

Creating a Database Using Microsoft Access

Relationships

Creating a Database Using More Exotic Techniques

Normalization

Using the Visual Basic Data Control

Connecting to a Database and Working with Records

Creating a Basic User Interface

Manipulating Records with the Data Control

Other Important Properties of the Data Control

Summary

Questions and Answers

Chapter 2—Queries

What Is a Query?

Where SQL Is Useful

Testing Queries with the DBGrid Control

Retrieving Records Using the SELECT Clause

1 of 330

Database Access with Visual Basic

Designating a Record Source Using the FROM Clause

Specifying Criteria Using the WHERE Clause

Operators in WHERE Clauses

Sorting Results Using ORDER BY

Sorting in Descending Order

Displaying the Top or Bottom of a Range Using TOP

Creating Top Percentage Queries

Joining Related Tables in a Query

Expressing a Join in SQL

Using Outer Joins to Return More Data

Displaying Zeros Instead of Nulls in a Joined Query

Aliasing Field Names Using AS

Queries that Group and Summarize Data

The SUM Function

Summary of Aggregate Functions

Creating Action Queries

Update Queries

Delete Queries

Append Queries

Make-table Queries

Union Queries

Crosstab Queries

Subqueries

Common Errors Generated by Queries

Using Queries Stored in the Database

Creating Stored Queries Using Visual Data Manager

Using the Visual Data Manager Query Builder

Creating Joins in Visual Data Manager

Creating Stored Queries Using Microsoft Access

Creating Joins in Microsoft Access

Creating Other Types of Queries in Access

Creating Stored Queries at Runtime

Using Data Definition Language

Creating Database Elements Using CREATE

Adding Constraints to Tables

Creating Indexes with CREATE INDEX

Deleting Tables and Indexes Using DROP

Modifying A Table’s Definition Using ALTER

Summary

Questions and Answers

Chapter 3—Data Access Objects

Using the DAO 3.5 Object Model

Programming with Objects

Using DAO to Work with Data

Connecting to a Database Using the Database Object

Using the Recordset Object

Manipulating Fields Using the Field Object

Using Navigational Methods with the Recordset Object

Searching for Data in Recordsets and Tables

Accessing Session Information with the Workspace Object

Handling Errors Using the Errors Collection and the Error Object

Creating Objects That Manipulate the Structure of a Database

Creating a Database

Manipulating Tables Using the TableDef Object

Creating Relationships Between Tables Using the Relation Object

Creating Indexes Using the Index Object

Manipulating Stored Queries Using the QueryDef Object

Compacting and Repairing a Jet Database

Working with Database Documents and Containers

Creating and Using Custom Properties of Database Objects

2 of 330

Database Access with Visual Basic

Summary

Questions and Answers

Chapter 4—Reporting and Exporting Data

Taking Advantage of the Great Forward-Scrolling Cursor Caper

Reporting Using Crystal Reports

Creating a Report Using Crystal Reports

Running the Report in Your Application with the Crystal Reports ActiveX Control

Reporting Using Microsoft Access

Running Microsoft Access Reports from Visual Basic

Running Access Reports Using VideoSoft VSREPORTS

Using the Visual Basic Printer Object

Setting Up the Printer’s Coordinate System

Designing the Report

Outputting a Recordset to the Printer Object

Reporting Using VideoSoft VSVIEW

Printing Tables with vsPrinter

Exporting to Text Files

Using the Open Statement to Create a File

Using FreeFile to Determine the Next Available File

Using Print # to Loop Through a Recordset and Output

Using the Close Statement to Close the File

Exporting to HTML Web Pages

Exporting to Microsoft Office Applications

Exporting to Microsoft Word

Summary

Questions and Answers

Chapter 5—Client/Server

The Drama of the Gifted Server Programmer

Setting Up and Running Microsoft SQL Server

Determining Installation Requirements for SQL Server

Installing SQL Server

Starting and Stopping SQL Server Using SQL Service Manager

Getting Started with SQL Server: The Basics

Creating a Database Using SQL Enterprise Manager

Creating Tables in a SQL Server Database

Using ISQL/w to Access a Database

Using Database Views to Control Access to Data

Using Stored Procedures

Displaying the Text of an Existing View or Stored Procedure

Creating Triggers

Managing Users and Security in SQL Enterprise Manager

Applying Security Attributes in ISQL/w

Removing Objects from the Database

Publishing Results of SQL Server to the World Wide Web

Migrating from Microsoft Access to SQL Server

Using Microsoft Access Upsizing Tools

Exporting Data from Access to SQL Server Using Linked Tables

Configuring and Using Open Database Connectivity (ODBC)

Creating an ODBC Data Source

Testing a Database Connection with odbcping

Accessing a Client-Server Data Source with the Visual Basic Data Control and ODBCDirect

Accessing Data Using the Remote Data Control

Using the Remote Data Control in Your Project

Bugs in the Remote Data Control Fixed in the Visual Studio Service Packs

Using Remote Data Objects to Access Client/Server Data

Setting Database Engine Properties with the rdoEngine Object

Accessing the Environment with the rdoEnvironment Object

Establishing a Connection with the rdoConnection Object

Responding to Events in RDO

Creating a Connection with UserConnection Designers

3 of 330

Database Access with Visual Basic

Accessing Queries with a UserConnection Designer

Utilizing Data with the rdoResultset Object

Running Queries with the rdoQuery Object

Accessing Tables with the rdoTable Object

Handling Errors with the rdoErrors Collection and the rdoError Object

Bugs in Remote Data Objects Fixed in Visual Studio Service Packs

Writing Your Own Jet Server

Summary

Questions and Answers

Chapter 6—Classes

Working with Classes and Objects

Building Custom Classes

Creating Collections and Collection Classes

Creating Class Hierarchies with VB Class Builder Utility

Using Forms as Classes

Using Classes and Objects with Database Access

Using Single Record-Handling Classes

Creating Classes that Handle Recordsets

Using Array-Handling Classes

Using Factory Methods

Creating Classes that Export Data

Deploying Classes as ActiveX Servers

Using an ActiveX Server in a Standard EXE Project

Registering ActiveX Components on Users’s Computers

Converting Standard EXE Projects to ActiveX Projects

Using ActiveX Components Remotely

Creating Multithreaded Components Using Visual Basic 5.0 Service Pack 2 or Greater

Summary

Questions and Answers

Chapter 7—Remote Database Access

About Clients, Servers, and Code Components

Encapsulating Business Rules with a Three-Tier Client/Server Architecture

Setting Up a Hardware Architecture for DCOM

Creating Your First DCOM Application

Using Registration Utilities to Work with ActiveX Components

Using ActiveX Components to Facilitate Database Access

Using GetRows to Return Data in an Array

Creating a Class to Decode Variant Arrays

Transferring Data with Database Replication

Designing a Database with Replication in Mind

Doing Replication in Microsoft Access

Doing Replication in Data Access Objects

Using Partial Replication

Performing Database Replication Over the Internet

Summary

Questions and Answers

Chapter 8—Multiuser Issues

Locking Data in Microsoft Jet

Locking the Entire Database Using Data Access Objects

Using Recordset-Level Locking

Using Page-Level Locking

Using Microsoft Jet Database Security

Accessing a Secured Jet Database in Code

Assigning a Password to a Database

Identifying the Current User in Code

Creating a Workgroup Information File

Creating and Deleting Users

Creating and Deleting Groups

Adding Users to Groups

Assigning and Removing Ownership of Database Objects

4 of 330

Database Access with Visual Basic

Assigning Permissions to Users and Groups

Encrypting a Microsoft Jet Database

Checklist for Implementing Jet Database Security

Summary

Questions and Answers

Chapter 9—Internet Database Applications and ADOs

Building Visual Basic Applications with ActiveX Data Objects

Understanding the OLE DB/ADO Architecture

Installing and Creating a Reference to ADO in Your Visual Basic Application

Using the ADO Connection Object to Connect to a Data Source

Using the ADO Recordset Object to Manipulate Data

Executing a Query Using the ADO Command Object

Running Parameterized Queries Using the ADO Parameter Object

Accessing Recordset Data Using the ADO Field Object

Handling Errors Using the ADO Errors Collection

Inspecting Provider-Specific Attributes Using the ADO Properties Collection

Building Web Applications with ActiveX Data Objects

Setting Up and Configuring Microsoft Internet Information Server for Active Server Pages

Writing Scripts with Active Server Pages (ASP)

Running an ADO Query in ASP

Inserting Records Using ASP and HTML Forms

Summary

Questions and Answers

Chapter 10—User-Interface Controls

Using Intrinsic Data-Aware Controls

Entering Data with the TextBox Control

Accessing Boolean Values with the CheckBox Control

Using the ListBox Control to Display Data

Using the Standard ComboBox Control

Displaying Read-Only Data with the Label Control

Displaying Bitmap Images Using the PictureBox Control

Displaying Binary Objects with the OLE Container Control

Using Data-Aware ActiveX Controls

Controlling Text Input with the MaskedEdit Control

Displaying Formatted Data with the RichTextBox Control

Displaying Data in Lists with the DBCombo and DBList Controls

Displaying Data in Rows and Columns with the DBGrid and MSFlexGrid Controls

Third-Party Data-Aware Controls

Creating Database-Aware ActiveX Controls

Summary

Questions and Answers

Chapter 11—Using the DBGrid and Apex True DBGrid Controls

Overview of Database Grid Controls

Issues Relating to DBGrid Resolved in Visual Basic 5.0 Service Packs

Getting Started with the DBGrid Control

Selecting Columns to Display at Design Time

Manipulating Columns at Design Time

Making the Data Editable

Inserting Splits at Design Time

Manipulating Split Objects in Code

Manipulating Grid Columns in Code

Navigating with the Bookmark Property

Selecting Records Using the SelBookmarks Collection

Using the DBGrid Control in Unbound Mode

Developing Database-Aware Applications Using True DBGrid Pro 5.0

Migrating from Previous Versions of True DBGrid

Storing and Applying Formatting with the Style Object

Changing the Display of a Column with the ValueItem Object

Providing Help for Users with CellTips

Summary

5 of 330

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: