Microsoft Office 2000 Developer

Creating a Data Source Using the Data Environment Designer

See Also

To create a data source, you identify the database(s) required and create a connection for each database. The Data Environment designer makes this easy, because you can create one Connection object for each database and then add Command objects as needed. Each Command object corresponds to a recordset. After configuring the Data Environment designer with the required Connection and Command objects, the result is known as a DataEnvironment object.

Although the Date Environment designer has a more user-friendly environment for specifying connections to databases than some of the other data access options, it only works from a stand-alone project.

Note   An extra step is involved in creating a data source with the DataEnvironment object. You must also package the resulting DataEnvironment object as a public object with public functions and properties. This is covered in greater detail in Creating a DataEnvironment Object for Use in a DLL File.

To create a Data Environment connection

1. Insert a Data Environment designer into a new VBA project.

   See Inserting ActiveX Designers into a VBA Project for more information.

2. Right-click the Connection1 icon, then click Properties.
   
   
3. Select an appropriate provider for the database you wish to use. For example, if you are using an Access database, select Microsoft Jet 4.0 OLE DB Provider.
   
   
4. Click the Next button. The Connection tab will be dynamically configured according to the provider you have selected.
   
   
5. On the Connection tab, fill in the fields as required. For example, to use the Northwind database, click the browse button to find and select it.
   
   
6. Click the Advanced tab.

   The Advanced tab allows you to set other parameters, such as Access user permissions.

7. Click the All tab.

   The All tab allows you to review the initialization parameters for the database and change the settings if needed.

8. When all desired settings are complete, click OK to create the data source.

Creating Command Objects

Once a connection has been established with a data provider, use the Data Environment designer to create Command objects. Each Command object represents a recordset from the database. If you are familiar with SQL (or Transact-SQL), you can create SQL statements that retrieve data from more than one table. If you are using Access, you can select a single table.

To add a Command object representing a database table

1. In the VBA Project Explorer, right-click the Connection icon, and click Add Command.
   
   
2. Right-click the resulting Command1 icon, and click Properties.
   
   
3. In the Command1 properties dialog box, type a name for the command.
   
   
4. Click the Database Object box, and select Table.

   The names of all tables in the database will appear in the Object Name drop-down list.

5. Select the table you want to connect to.

To add a Command object using an SQL statement

1. In the VBA Project Explorer, right-click the Connection icon, and click Add Command.
   
   
2. Right-click the resulting Command1 icon, and click Properties.
   
   
3. In the Command1 properties dialog box, type a name for the command.
   
   
4. Click the SQL Statement option.
   
   
5. Type an SQL statement into the text box. For example:

   SELECT ProductID, ProductName, UnitPrice FROM Products
   

For More Information   More documentation about the Data Environment designer and hierarchical recordsets can be found in the Visual Basic documentation in the MSDN Library. See "About the Data Environment Designer" in the Data Access Guide.