Visual Basic is moving forward with Microsoft's overall view of data access. (See also my article on MDAC and DB APIs.)

VB supports database for most proprietary DB APIs, including the following:

• ODBC, the most common DB API and native for MS SQL Server 7.
• DB Library, the proprietary DB API for versions of MS SQL Server prior to 7.

VB also supports all of the database object APIs made by Microsoft. In historical order, here are three sets of database access objects:

• DAO (Data Access Objects) COM objects that access databases via Microsoft JET (Joint Engine Technology) which is used especially for Microsoft Access.
• RDO (Remote Data Objects) COM objects that access database with ODBC (Open DataBase Connectivity). ODBC drivers by Microsoft can connect to the majority of SQL (Structured Query Language) databases.
• ADO (ActiveX Data Objects) COM objects that access databases with OLE DB (Object Linking and Embedding DataBase). OLE DB uses "providers" which are sort of like drivers that can connect to JET, ODBC, and other sources like:
• Relational databases
• Non-relational data
• Text files
• E-mail
• HTML pages
• Sources over networks and the Web.

VB also supports another database object model called ODBCDirect, which has an object model similar to DAO but can be used on ODBC databases.

Proprietary DB APIs are usually very powerful and fast, but are also more difficult to program and non-transferable. In contrast, database access objects can be applied to multiple products and are easier to program than CLI.

When using the database access objects, be sure to qualify the type of object as needed. EG:

Dim rs As Recordset
'This is for DAO
Dim rs As ADODB.Recordset
'This is for ADO

The database access objects can be utilized either:

• Programmatically with the option to bind controls.
• Programmatically while binding controls to a "data control". A data control serves to connect and move through databases. Here are the variations of data control:
• Data control for DAO
• MSRDC control for RDO

The Data Environment Designer creates ADO (ActiveX Data Objects) objects that can access data. You can add code to work with ADO objects and their properties, methods, and events. The Data Environment Designer can make the three basic ADO objects: Connections, Commands, and Recordsets. Establish your connection. Use the connection to make commands. Use the commands to make recordsets. The Data View Window in the View pull down menu can be used to see the structure of your database objects.

The name of each Data Environment Designer is usually prefixed with de. The name of each Connection is usually prefixed with cnn. The name of each Command is usually prefixed with com. The name of each Record set is made by prefixing the Command name with rs and qualifying the resultant name with the Data Environment name, eg deDataEnvironment.rscomCommand

Controls on a form can be bound either manually or by drag-and-dropping. To manually bind a control, typically set these properties of the control: DataSource (i.e. to the Data Environment name), DataMember (i.e. to the Command name), and DataField (i.e. to the field in the Command). To bind with drag-and-drop, drag the desired command or field from the Data Environment Designer. Right drag to show a grid sheet or spreadsheet like display. To navigate on the form make command buttons that invoke the MovePrevious and MoveNext methods on the Recordset.

Legacy forms can be made ADO compliant by adding an ADO control to the form, pointing the ADO control the correct source, and then pointing all the other controls to the new ADO control.

Data can be formatted at design time by setting the DataFormat property of the control. Data can be formatted at run time by first adding a reference to the Microsoft Data Formatting Object Library to the project. Declare a StdDataFormat object using the WithEvents keyword, to make the Format and UnFormat events available.

It is also possible to just use the Add-In menu to add the Data Form Wizard and use that to set up data access.

A Data Report can be made from Commands in a Data Environment Designer. If Data Report is not under the Project menu, then it can be added by choosing the Project menu, then Components, then Designers, and then Data Report. Then simply drag the Command onto the report and set the properties DataSource (to point to the appropriate Data Environment), DataMember (to point to the appropriate Command), and DataField (to point to the appropriate field in the Command) of the Data Report object. The name of each Data Report is usually prefixed with dr or rpt.

Page Modified: (Hand noted: ) (Auto noted: )