Roger Jennings' Database Developer's Guide with Visual Basic 6

Roger Jennings is a principal of OakLeaf Systems—an Oakland, California consulting firm that specializes in the design and implementation of client/server database systems for Fortune 500 companies. He brings over 25 years of computer programming experience to his best-selling Sams titles, including Database Developer¿s Guide with Visual Basic 3, Database Developer¿s Guide with Visual Basic 4, Access 1.1 Developer¿s Guide, Access 2 Developer¿s Guide, and the Roger Jennings¿ Database Workshop books, for which he is the series editor. In addition to writing books, Roger is a Contributing Editor and the writer of “Database Design” columns and feature articles on Visual Basic, Access, and client/server computing for Fawcette Technical Publications¿ Visual Basic Programmer¿s Journal.

I. ACTIVEX DATA OBJECTS AND BOUND CONTROLS.

1. Staking Visual Basic's Claim to Enterprise Development.


Summarizing Visual Basic 6.0's New Data-Related Features. Migrating to OLE DB and ADO. Exploring the Data Environment Designer and the Data View Window. Adopting ADO-Compliant Controls. Binding Hierarchical FlexGrids to Recordsets. Printing Reports with the Data Report Designer. Automating Design with the Data Form Wizard. Binding Data Sources and Consumers. Using the Visual Data Tools. Working with Microsoft SQL Server.

2. Understanding OLE DB and Universal Data Access.


Interfacing with Data. Creating ADODB.Recordsets. Exploring Top- Level ADO Properties, Methods, and Events. Examining Provider-Specific ADODB.Properties Collections.

3. Migrating from DAO and RDO to ADO.


Comparing ADO, DAO, and RDO Object Models. Moving from DAO.Recordset to ADODB.Recordset Objects. Manipulating ADODB.Recordset Objects with VBA Code. Taking Advantage of New ADODB.Recordset Features. Adopting ADODB.Recordset Find Methods. Migrating from rdoResultsets and ODBCDirect Recordsets to ADODB.Recordsets. Understanding Hierarchical Recordsets.

4. Using the ADO Data Control with Bound Controls.


Creating Simple Text Box Data Display and Editing Forms. Exploring ADODC's Data-Related Properties and Events. Introducing Batch Optimistic Updates. Simple-Binding Data Consumer Controls. Complex-Binding Data Consumer Controls.

II. Database and Query Design Concepts.

5. Optimizing the Design of Relational Databases.


Classifying Database Systems. Modeling Data. Rules for Relational Database Design. Indexing Tables for Performance and Domain Integrity. Warehousing and Analyzing OLTP Data.

6. Learning Structured Query Language.


Reviewing the Foundations of SQL. Differences Between SQL and Procedural Computer Languages. Writing ANSI SQL Statements. Comparing the Jet SQL Dialect with ANSI SQL-92.

7. Running Crosstab and Action Queries.


Using Crosstab Queries to Present Summary Data. Using TRANSFORM and PIVOT to Create Crosstab Queries. Executing Crosstab Queries as DataEnvironment Commands. Defining Action Queries. Using the SQL Action Queries.

8. Connecting to Desktop Data Sources with ODBC.


Understanding the Structure of the ODBC API. Exploring ODBC Initialization Files and Registry Entries. Creating and Using a DSN for dBASE Tables.

III. AN INTRODUCTION TO DATABASE FRONT-END DESIGN.

9. Designing A Decision-Support Front End.


Organizing the Data Behind a Decision-Support Front End. Specifying the Data Sources. Developing Query Strategies. Designing the User Interface. Creating and Using Button Bars and Toolbars. Creating Graphs from Rolled-Up Data. Displaying Detail Data with the MSFlexGrid Control.

10. Taking Full Advantage of Data-Bound ActiveX Controls.


Working with DataGrids and Hierarchical FlexGrids. Creating and Displaying Hierarchical Recordsets. Saving and Displaying Images with PictureBox and Image Controls.

11. Graphing Summary Data Created from Crosstab Queries.


Organizing a Chart-Based Decision-Support Application. Deciding on a Navigation Method for Complex Decision-Support Applications. Setting Graph Properties in Design Mode. Implementing the First Two Chart Levels. Adding a Detail Grid to the MDI Child Form. Adding the Code to Populate an MSHFlexGrid Control. Writing the Code to Navigate Between Graphs and Charts. Printing and Saving Graphs and Charts.

12. Printing with the Report Designer and VBA Code.


Designing and Printing a Simple DataReport. Grouping Detail Records. Using Command Aggregates for Subtotals and Grand Totals. Crossfooting Reports with Row Totals. Substituting RptFunction Controls for Aggregate Values. Exporting DataReports to HTML and Text Files. Printing with VBA Code.

IV. ADVANCED PROGRAMMING FOR DATA ACCESS AND AUTOMATION.

13. Drilling Down into Data from Graphs and Charts.


Taking Advantage of Chart Events. Displaying a Single Series from a Stacked Area Chart. Adding a Drilldown Pie Chart to the Drilldn.vbp Project. Using MouseDown and Double-Click Events to Activate Drilldown. Choosing the Drilldown Path with a Pie Chart. Populating an MSHFlexGrid with the Selected Drilldown Data.

14. Scheduling And Device Control with Jet and VBA.


Navigating the Visual Basic Program Guide. Working with WebTV's Scheduling Database. Comparing the Performance of DAO and ADO with Large Recordsets. Upgrading Projects from DAO to ADO. Replacing Compound FindFirst and FindNext Methods with Find. Traversing a Recordset to Fill a Custom Grid. Exploring the Remainder of the VB6PG Project.

15. Writing Local Automation Components and ActiveX DLLs.


Defining the Role of Automation Components. Deciding Between In- Process and Out-of-Process Components. Building a Simple Automation Component. Creating a Complex Three-Tier Automation Component.

16. Creating User Controls For Database Applications.


Emulating an Access Continuous Form with the DataRepeater Control. Automating Control Generation with Wizards. Exploring the BindingCollection Object. Debugging Data-Bound ActiveX Controls.

V. MULTIUSER AND CLIENT/SERVER DATABASE FRONT ENDS.

17. Networking Secure Multiuser Jet Databases.


Understanding Network Topology and Operations. Working with Network Hardware and Operating Protocols. Logging on to Servers and Joining Workgroups. Maintaining Database Security in a Network Environment. File Permissions Using NTFS Partitions. Fathoming the Jet Security System. Programming Jet Security Features with Visual Basic 6.0 and DAO 3.5. OLE DB, ADO 2.0, and Jet Security.

18. Working With Client/Server Databases.


Using Microsoft SQL Server. Upsizing Nwind.mdb to SQL Server 6.5 with the Access 97 Wizard. Specifying Primary Keys and Relationships in Database Diagrams. Using the Native SQL Server Provider. Connecting to Oracle8 Databases. Using Visual Data Tools with Visual Basic 6.0. Working with Client/Server Connections in VBA Code.

19. Processing Transactions and Bulk Operations.


Defining Transactions and the ACID Test. Processing Transactions with ADO 2.x and VBA Code. Distributing Transactions Across Multiple Databases. Working with Bulk Updates and Disconnected Recordsets.

20. Porting Access OLTP Applications To Visual Basic 6.0.


Designing Front Ends for Heads-Down Data Entry. Following the OLTP Project's Upgrade Path. Stepping Through the First Phase of the Migration Process. Making the Transition from DAO to ADO. Reviewing the Code of the Ado2oltp Project.

21. Using Transact-SQL Stored Procedures.


Gaining a Performance Boost with Stored Procedures. Understanding Stored Procedure Syntax. Replacing SQL Statements with Stored Procedures. Calling Stored Procedures in VBA Code. Adapting the Ado2oltp Application to Stored Procedures. Troubleshooting Stored Procedures with the T-SQL Debugger.

VI. DATABASES, INTRANETS, AND THE INTERNET.

22. Integrating Databases with Intranets and the Internet.


Cutting Through the Hype for Microsoft's Digital Nervous System. Getting a Grip on Windows Distributed interNet Applications Architecture. Examining the New Internet-Related Features of Visual Basic 6.0. Creating a Simple Data-Enabled WebClass Project. Starting a Simple DHTML Project. Opening a New DHTML Project and Adding a DHTML Page.

23. Using ActiveX Document Objects with Intranets.


Converting the ADO2OLTP.vbp Project to an ActiveX Document. Packaging ActiveX Document Objects. Dealing with the Architectural Issues of Multi-form Projects.

24. Working with Remote Data Services.


Understanding RDS Architecture. Exploring the Methods, Properties, and Events of RDS Components. The RDS.DataSpace Object. The RDSServer.DataFactory Object. Coding for Implicit and Explicit Remoting. Programming RDS with VBA in the DHTML Page Designer.

25. Developing Data-Enabled Internet Applications.


Setting Up to Run VBLive. Touring the VBLive Application. Understanding the Architecture of VBLive. Generating HTML from Structure Resources. Generating HTML Directly from Template Files. Localizing Content with a Resource File.

VII. ENTERPRISE-LEVEL DEVELOPMENT TECHNIQUES.

26. Taking Advantage of Microsoft Transaction Server 2.0.


Positioning MTS in the Middleware Market. Understanding the Role of MTS in N-Tier Client/Server Architecture. Setting Up MTS 2.0 Under Windows NT Server 4.0. Setting Up and Running the Bank Client on a Remote Computer. Installing MTS 2.0 on Workstations.

27. Creating and Deploying MTS Components.


Defining MTS Component Candidates. Designing Efficient MTS Components. Converting a Monolithic Client to Use MTS Components. Adding Security to Your Packages and Components.

28. Analyzing Multidimensional Data with OLAP and MDX.


Understanding Measures and Dimensions. Defining Measures and Dimensions at the Department Level. Categorizing OLAP Servers and Engines. Delivering Information to the Client. Exploring ADO MD Objects.

VIII. APPENDIX.

Appendix A. Glossary.
Index.