datenvr0.htm

<body link="#0000FF" vlink="#800080"> <h1><font color="#000080" size="5">Create a Connection to a Microsoft Access Database File</font></h1> <p><font size="2">When you work with data in a Microsoft Access database, you must first create a connection to a database file. The easiest way to create a connection to a Microsoft Access file is to create a data environment using the Data Environment designer.</font></p> <p><font size="2">This topic shows how to create a connection to the Phone List sample database (Employees.mdb). It assumes that the database file is located on the Student Disk downloaded from the publisher site by using the back cover of the text.</font></p> <p><font size="2"><b>To create a connection to a Microsoft Jet/Microsoft Access database file</b> </font></p> <ol> <li><font size="2">Add a Data Environment designer to your project.</font></li> <li><font size="2">Set connection properties for the designer. </font></li> </ol> <h2><font size="4">Add a Data Environment Designer to Your Project</font></h2> <p><font size="2">A Data Environment designer provides an easy way to create connections to many types of databases. To add a designer to your project, click Add Data Environment on the Project menu. Visual Basic loads a data environment and adds a Connection object to the data environment. Click the DataEnvironment1 icon in the Data Environment Window, change its name property in the property window to datEmployeesEnvironment and press Enter key.</font></p> <p><font size="2">If the Data Environment designer is not available on the Project menu, add it to your Visual Basic environment. Click Components on the Project menu, click the Designers tab, and then click the check box next to its name in the list of designers.</font></p> <p><font size="2"><b>Note</b> The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu on the Project menu. </font></p> <p><a href="graphics/datenvr1.gif" target="Win1"><font size="2"><img src="graphics/datenvr1.gif" alt="Click to see How to add Data Enviromnent" align="right" hspace="0" width="67" height="57"></font></a> </p> <h2><font size="4">Set Connection Properties</font></h2> <p><font size="2">You establish the connection to your data source by setting properties in the Data Link Properties dialog box. To display this dialog box, right-click the Connection object in your data environment and then choose Properties from the shortcut menu. Select an OLE DB provider on the Provider tab of the dialog box. Then click Next and enter connection information on the Connection tab of the dialog box. The layout of the Connection tab varies, depending on the OLE DB provider you select on the Provider tab.</font></p> <p><a href="graphics/datenvr2.gif" target="Win1"><font size="2"><img src="graphics/datenvr2.gif" alt="Click to see How to set Connection Properties" align="right" hspace="0" width="67" height="57"></font></a></p> <p><font size="2">For example, to connect to a Microsoft Jet/Microsoft Access database such as the Employees sample database, select the Microsoft Jet 3.51 OLE DB provider on the Provider tab. On the Connection tab, enter the path to the database file; for example:</font></p> <p><a href="graphics/datenvr3.gif" target="Win1"><font size="2"><img src="graphics/datenvr3.gif" alt="Click to see How to set Connection Path and Test link" align="right" hspace="0" width="67" height="57"></font></a> </p> <pre><font color="#008000" size="5"><code><strong>A:\Data\Employees.mdb</strong></code></font></pre> <p><font size="2">You can test the connection by clicking Test Connection Button.</font></p> <h1><font color="#000080" size="5">Create a Data Environment Command Object</font></h1> <p><font size="2">Once you've created a connection to your database, you can use the Data Environment designer to create Command objects that give you access to data. For example, you can create a simple Command object that gives you access to the data in a table, or a more complex Command object based on a query.</font></p> <p><font size="2">You can then use a data environment Command object as a data source in your application. First, you'll create a simple Command object based on the Phone table in the Employees sample database.</font></p> <p><font size="2"><b>To create a simple data environment Command object</b> </font></p> <ol> <li><font size="2">Open a Data Environment designer.</font></li> <li><font size="2">Create the Command object. </font></li> </ol> <h2><font size="4">Open a Data Environment Designer</font></h2> <p><font size="2">You can open an existing Data Environment designer or create a new one. To open an existing one, click Designers in the Project window, and then double-click the name of the designer. To create a new one, follow the instructions in the "Create a Connection to a Microsoft Access Database File" topic.</font></p> <h2><font size="4">Create the Command Object</font></h2> <p><font size="2">Create a data environment Command object by clicking the Add Command button on the Data Environment designer toolbar, or by right-clicking the connection in the data environment window and choosing Add Command from the menu. You can then specify the Command object's name, the connection it uses, and the source of its data in the Command Properties dialog box. To display this dialog box, right-click the Command object in your data environment and then choose Properties from the shortcut menu.</font></p> <p><a href="graphics/datenvr4.gif" target="Win1"><font size="2"><img src="graphics/datenvr4.gif" alt="Click to see How to Create a Command Object" align="right" hspace="0" width="67" height="57"></font></a> </p> <p><font size="2">For example, to create a Command object based on the Phone table in the Employees sample database, set the following properties:</font> </p> <table border="1" cellpadding="5" cellspacing="1" width="623"> <tr> <td width="39%"><font size="2"><b>Property</b></font></td> <td width="61%"><font size="2"><b>Setting</b></font></td> </tr> <tr> <td width="39%"><font size="2">Command Name</font></td> <td width="61%"><font size="2">EmployeesTable</font></td> </tr> <tr> <td width="39%"><font size="2">Connection</font></td> <td width="61%"><font size="2">Connection1</font></td> </tr> <tr> <td width="39%"><font size="2">Database Object</font></td> <td width="61%"><font size="2">Table</font></td> </tr> <tr> <td width="39%"><font size="2">Object Name</font></td> <td width="61%"><font size="2">Phone</font></td> </tr> </table> <p><a href="graphics/datenvr5.gif" target="Win1"><font size="2"><img src="graphics/datenvr5.gif" alt="Click to see Command Object Properties" align="right" hspace="0" width="67" height="57"></font></a> </p> <p><font size="2">When you click OK, the Data Environment designer displays the Command object and its underlying fields in a hierarchical view in the Data Environment designer window. Click the + to the left of the EmployeesTable icon in the Data Environment designer window to see the fields in the Employees database.</font></p> <p><font size="2">The Data Environment designer also creates a Recordset object to represent the records returned by the Command object. The Recordset object uses the same name as the Command object but adds an "rs" prefix. For example, when the Data Environment designer creates the EmployeesTable command, it also creates a Recordset object called "rsEmployeesTable."</font></p> <p><font size="2">You can refer to the Command object in code as a method of the data environment, and refer to its recordset as a property of the data environment. For example, if you have created the EmployeesTable command in a data environment called "datEmployeesEnvironment," you can refer to the command in code as follows:</font></p> <pre><code>datEmployeesEnvironment.EmployeesTable</code></pre> <p><font size="2">You can refer to the Command object's underlying recordset as follows:</font></p> <pre><code>datEmployeesEnvironment.rsEmployeesTable</code></pre> <p><font size="2">After you create the Command object, the Auto List Members feature and Object Browser display the command method and recordset property along with the other properties and methods of the data environment.</font></p> <h1><font color="#000080" size="5">Create a Simple Data-Bound Form</font></h1> <p><font size="2">Once you've created a Command object in a Data Environment designer to serve as a data source, you can easily create a data-bound form by dragging the Command object onto a blank form. You can then add a mechanism for navigating through the records that are displayed.</font></p> <p><font size="2">In this topic, you'll create a form that displays records from the Phone table in the Employees sample database. Then you'll create command buttons for moving to the next or previous record.</font></p> <p><font size="2"><b>To create a simple data-bound form</b> </font></p> <ol> <li><font size="2">Drag a Command object from the Data Environment designer to a blank form.</font></li> <li><font size="2">Create Select Menu Options that lets you navigate through records. </font></li> </ol> <h2><font size="4">Drag Command Object from Data Environment Designer to a Blank Form</font></h2> <p><font size="2">Much of the tedium of creating a data-bound form can be avoided in Visual Basic by dragging a Command object from the Data Environment designer to the form. Visual Basic automatically creates text box controls to display data from the Command object's recordset and sets data properties that bind the controls to fields in the recordset.</font></p> <p><font size="2">For example, to create a form that displays data from the Phone table in the Employees sample database follow the steps in the "Create a Data Environment Command Object" topic to create an EmployeesTable command. Then simply drag the Command object (EmployeesTable) from the Data Environment designer onto a blank form. To view both the form and data environment simultaneously so you can perform the drag operation, select Tile Horizontally, Tile Vertically, or Cascade from the Window menu.</font></p> <p><a href="graphics/datenvr6.gif" target="Win1"><font size="2"><img src="graphics/datenvr6.gif" alt="Click to see How to Drag Command Object onto Form" align="right" hspace="0" width="67" height="57"></font></a> </p> <h2><font size="4">Create Menu Options to Navigate through Records</font></h2> <p><font size="2">By basing a data-bound form on a data environment Command object's recordset, you can easily create Next and Previous options on a Menu that let you navigate through records. Each menu option requires the following lines of code.</font></p> <p><font size="2">For example, to create a Next Menu option for the form that displays Employees records, add a Select Menu which includes the Next, Previous, First, and Last options to the form. Change Next Menu's Caption property to <u>N</u>ext and its Name propery to mnuNext. Then add the following code to menus mnuNext_Click event procedure:</font></p> <pre><font color="#000080"><code>datEmployeesEnvironment.rsEmployeesTable.MoveNext IF datEmployeesEnvironment.rsEmployeesTable.EOF then datEmployeesEnvironment.rsEmployeesTable.MoveFirst END IF </code></font></pre> <p><font size="2">The code uses the MoveNext method of the EmployeesTable command's underlying recordset, rsEmployeesTable. It refers to the recordset as a property of the command's data environment designer, datEmployeesEnvironment.</font></p> <p><font size="2">Similarly, you can create a Previous Menu Option by adding a Previous Menu Option of the Select Menu to the form and change its Caption property to <u>P</u>revious and its Name property to mnuPrev. Then add the following code to the menus mnuPrev_Click event procedure:</font></p> <pre><font color="#000080"><code>datEmployeesEnvironment.rsEmployeesTable.MovePrevious IF datEmployeesEnvironment.rsEmployeesTable.BOF then datEmployeesEnvironment.rsEmployeesTable.MoveLast END IF </code></font></pre> <p><font size="2">When you run the form, Visual Basic displays the records in the Employees table and lets you move forward and backward through the recordset by using the Select Menu.</font></p> <p><font size="2"><br> </font><font color="#FF0000" size="4"><strong>Note: Use the same coding methods for the </strong><strong><u>First</u></strong><strong> and </strong><strong><u>Last</u></strong><strong> record options on the Select Menu</strong></font><font color="#FF0000" size="2"><strong>.</strong></font></p> <form> <p align="right"><input type="button" value=" <<Back " onclick="top.location.href='i110hin4.htm'"> </p> </form> </body>