Course Outline: ADO Programming using Microsoft Visual Basic V6

ADO (ActiveX Data Objects) is data connectivity software that is used extensively by Visual Basic, VBA, ASP and Office programmers to connect to, and interact with data. This highly practical course takes programmers through from the basics of ADO Architecture to working with cursors, Data Environments, Data Reports, hierarchical and disconnected recordsets, and transactions. Although most of the course is oriented towards the Visual Basic 6 Programmer, worked examples and labs cover use of ADO in ASP pages, Excel and other environments. The course emphasises and explains best practices wherever possible.

Course Duration: 4 Days

Upon completion of the course the delegate should be able to:

• Describe the main features, objects and architecture of the ADO Object Model.
• Create, configure and use ADO Connections (including asynchronously). Use them to execute commands and return data.
• Create, configure and use ADO Commands and Parameters, including invoking parameterised stored procedures in SQL Server.
• Create, configure and use ADO recordsets, including disconnected recordsets with conflict resolution, and hierarchical recordsets.
• Describe and choose between different cursor types, cursor locations and lock types.
• Create ADO recordsets from non-relational data sources.
• Create data forms using either bound or unbound controls, and perform data binding through code.
• Navigate ADO Recordsets using Move, Find and Seek methods. Restrict a view of data using the Filter property.
• Use Data Environments for rapid application development.
• Use Data Reports for simple reporting.
• Use ADO code from Visual Basic 6, Excel and ASP pages.
• Describe and use transactions to ensure data integrity.

Intended Audience

This course is designed for Visual Basic programmers who are comfortable with the use of standard controls on forms, data validation behind forms, object creation and manipulation and regular VBA looping and decision-making constructs, who have had little or no experience of ADO, as well as more experienced programmers who wish to learn some of the more advanced ADO capabilities. It is also suitable for Office or other VBA programmers wishing to get to grips with ADO, and VBScript programmers wishing to gain a better understanding of ADO used from ASP pages.

Prerequisites

It is assumed that anyone attending this course has created simple applications with Visual Basic (4, 5 or 6), and understands how to perform error handling, looping and decision-making constructs, write subroutines and functions, write event handlers, and can write simple SQL SELECT or UPDATE instructions. Prior knowledge of ADO is not assumed, although rudimentary knowledge of ADO, DAO or RDO would prove useful. Similarly, although prior use of EXCEL or ASP is not required, it will prove helpful. Attendance of the course ‘VB6 Programming’ will have covered all of the requisite skills.

Course Format

The course is normally spread over a four-day period and comprises of instructor-led presentations and 23 practical hands-on exercises. The practical sessions are structured to give the delegates the opportunity to put into practice the technologies covered during the instructor-led sessions. The delegates will be able to take away with them copies of all the code they have written, along with solutions and demonstration code. Examples and labs use ACCESS and SQL Server 2000 data sources, as well as a non-relational source, and are mainly VB6 (with some EXCEL and ASP content).

If desired, the content could be compressed by reducing the number of practical exercises, or taught more intensively within a three-day period.

Course Content

Connections, Commands and Recordsets. ADO Providers. The Client Cursor Engine. Comparisons with RDO and DAO.

ADO Recordsets
Key properties and methods. Opening a recordset. Default recordsets. Types and locations of cursors. Locking mechanisms and strategies. Navigating recordsets. Modifying data using a recordset. The ADO Data Control and binding controls to data. Using unbound controls.

ADO Connections
Key properties and methods. Choosing a Provider. Opening and Closing Connections. Using ODBC DSN’s, ConnectionStrings and UDF Files. Connection pooling. Modifying data using a connection. Asynchronous operation. Using connection events.

ADO Commands
Key properties and methods. The Parameters collection. Configuring and executing commands. Using stored procedures and parameter queries. Modifying data using a command.

Data Environments
Relationship to ADO architecture. Using IntelliDrop and bound controls. Grouping and Aggregation of data. Hierarchical recordsets and Shape commands. Invoking stored procedures.

Data Reports
Creating simple and complex reports. Report controls. Report formats. Report events.

Advanced ADO Recordsets
Using Sort, Find, Seek and Filter methods and properties. Disconnected recordsets, batch updates and conflict resolution. Use in middle tier components. Creating recordsets from arbitrary data sources. Multiple recordsets. Persisting data to disk as XML files.

Data Modification
Review of modification techniques and best practices. Use of transactions to protect data integrity. Review of error handling during data modification. Data modification best practice.