{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconConnectToISAMDatabaseC"}
Many data access applications created with earlier versions of Visual Basic store and manage data using the Microsoft Jet database engine, the engine used by Microsoft Access. These applications use Microsoft Data Access Objects (DAO) to access and manipulate data.
Now you can use Microsoft ActiveX Data Objects (ADO) to easily
manipulate data in a variety of database formats, including Microsoft Jet
format. You may still want to use DAO to work with your local Microsoft Jet
databases, but for new applications you'll probably want to use
This series of topics shows you how to use
· Create a connection to a Microsoft Access database filevbconCreateConnectionToMDBFile
· Create a data environment Command objectvbconCreateQueryWithDesigner
· Create a simple data-bound formvbconBindControlsToQueryFields
· Create a data grid form based on a queryvbconMakeFormFromQuery
· Create a master/detail formvbconCreateDetailReportOnForm
· Create a data-bound reportvbconMakeReportUsingDesigner
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconCreateConnectionToMDBFileC"}
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.
This topic shows how to create a connection to the Northwind Traders sample database (Nwind.mdb). It assumes that the database file is located in the Samples folder on your computer.
To create a connection to a Microsoft Jet/Microsoft Access database file
1. Add a Data Environment designer to your project.
2. Set connection properties for the designer.
Note This topic is part of a series that walks you
through creating a simple database application that interacts with data
in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft
Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
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.
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.
Note 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.
For More Information See Using the Data Environment DesignervbconUsingDataEnvironmentDesigner.
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 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.
For example, to connect to a Microsoft Jet/Microsoft Access database such as the Northwind Traders sample database, select the Microsoft Jet OLE DB provider on the Provider tab. On the Connection tab, enter the path to the database file; for example:
C:\Samples\Nwind.mdb
If the database file requires you to supply a user name and password, you can also enter those on the Connection tab. You set additional connection properties by clicking the Advanced or All tabs, or test the connection by clicking Test Connection.
For More Information For more information on connections and connection properties, see Connection ObjectsvbconConnectionObjects in "About the Data Environment Designer." And for more information on Data Links, see Data Link API Overview!ALink("DataLinkAPIOverview") in "Database and Messaging Services" in the Platform SDK.
This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb.
|
To |
See |
|
Go to the next step |
Create a Data Environment Command ObjectvbconCreateQueryWithDesigner |
|
Start from the beginning |
Interacting with Data in a Microsoft Jet/Microsoft Access DatabasevbconConnectToISAMDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconCreateQueryWithDesignerC"}
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.
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 Customers table in the Northwind Traders sample database.
Note This topic is part of a series that walks you
through creating a simple database application that interacts with data
in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft
Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
To create a simple data environment Command object
1. Open a Data Environment designer.
2. Create the Command object.
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.
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 menu.
For example, to create a Command object based on the Customers table in the Northwind Traders sample database, set the following properties:
|
Property |
Setting |
|
Command Name |
CustomersTable |
|
Connection |
Connection1 |
|
Database Object |
Table |
|
Object Name |
Customers |
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.
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 CustomersTable command, it also creates a Recordset object called "rsCustomersTable."
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 CustomersTable command in a data environment called "MyDataEnvironment," you can refer to the command in code as follows:
MyDataEnvironment.CustomersTable
You can refer to the Command object's underlying recordset as follows:
MyDataEnvironment.rsCustomersTable
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.
For More Information See How Commands are Exposed for Programmatic Access!ALink("vbconHowDECommandsExposed").
This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb.
|
To |
See |
|
Go to the next step |
Create a Simple Data-Bound FormvbconBindControlsToQueryFields |
|
Start from the beginning |
Interacting with Data in a Microsoft Jet/Microsoft Access DatabasevbconConnectToISAMDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconBindControlsToQueryFieldsC"}
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.
In this topic, you'll create a form that displays records from the Customers table in the Northwind Traders sample database. Then you'll create command buttons for moving to the next or previous record.
Note This topic is part of a series that walks you
through creating a simple database application that interacts with data
in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft
Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
To create a simple data-bound form
1. Drag a Command object from the Data Environment designer to a blank form.
2. Create command buttons that let you navigate through records.
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.
For example, to create a form that displays data from the Customers table in the Northwind Traders sample database, follow the steps in the "Create a Data Environment Command Object" topic to create a CustomersTable command. Then simply drag the Command object 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.
By basing a data-bound form on a data environment Command object's recordset, you can easily create Next and Previous buttons that let you navigate through records. Each command button requires a single line of code.
For example, to create a Next button for the form that displays customer records, add a command button to the form and change its Caption and Name properties to Next. Then add the following line to the command button's Next_Click event procedure:
MyDataEnvironment.rsCustomersTable.MoveNext
The code uses the MoveNext method of the CustomersTable command's underlying recordset, rsCustomersTable. It refers to the recordset as a property of the command's data environment designer, MyDataEnvironment.
Similarly, you can create a Previous button by adding a command button to the form and change its Caption and Name properties to Previous. Then add the following code to the command button's Previous_Click event procedure:
MyDataEnvironment.rsCustomersTable.MovePrevious
When you run the form, Visual Basic displays the records in the Customers table and lets you move forward and backward through the recordset.
This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb.
|
To |
See |
|
Go to the next step |
Create a Data Grid Form Based on a QueryvbconMakeFormFromQuery |
|
Start from the beginning |
Interacting with Data in a Microsoft Jet/Microsoft Access DatabasevbconConnectToISAMDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconMakeFormFromQuery_C"}
You can use the same drag-and-drop technique you used to create a simple data-bound form to create a data grid form based on a query. Simply create a Command object based on an SQL query, then drag the Command object onto a blank form. You can use the Query designer to create the SQL query.
In this topic, you'll create a form that displays orders for French customers in the Northwind Traders sample database in a data grid.
To create a data-bound form based on a query
1. Create a data environment Command object based on a query.
2. Drag the Command object from the data environment designer to a blank form.
Note This topic is part of a series that walks you
through creating a simple database application that interacts with data
in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft
Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
You can easily create a data environment Command object based on a query by using the Query designer. First, follow the instructions in the previous steps in this scenario to open an existing Data Environment designer or create a new one.
Create the Command object by clicking the Add Command button on the Data Environment toolbar, or by right-clicking the connection in the Data Environment designer and selecting 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 menu.
For example, to create a Command objects based on a query of orders by French customers in the Northwind Traders sample database, set the following properties:
|
Property |
Setting |
|
Command Name |
FrenchCustomersOrders |
|
Connection |
Connection1 |
Rather than select a specific database object as the basis for the command, as you did in the "Create a Data Environment Command Object" step, select SQL Statement as the source of data for the command. Then click the SQL Builder button to open the Query designer, where you specify the tables, fields, and criteria for the query.
For example, create a query that returns information about orders by French customers by dragging the Customers and Orders tables from the Data View window (available from the View menu) to the upper pane of the Query designer. (The designer automatically displays a line between the tables showing their related field, CustomerID.) Then click the check box next to the fields that you want to include in the query:
|
Table |
Field |
|
Orders |
OrderID |
|
Orders |
CustomerID |
|
Orders |
OrderDate |
|
Orders |
ShippedDate |
|
Customers |
Country |
Finally, specify the criteria for the query by entering "
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate, Customers.Country
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID AND
(Customers.Country = '
To view the results of the query, right-click the Query designer and select Run. The Query designer displays the resulting recordset.
When you save the query, the Data Environment designer updates the Command object to use the SQL statement as its data source. To verify the statement, you can open the Command properties dialog box and see the statement is in the SQL Statement box.
For More Information See Designing Queries!ALink("dvovrQueryDesigner").
In the "Create a Simple Data-Bound Form" topic, you created a data-bound form that displayed data in text boxes by dragging a Command object from the data environment designer to a blank form. You can also display the data in a data grid by dragging a data environment Command object.
For example, to create a data grid that displays the orders of French customers, select the FrenchCustomersOrders Command object you created in the previous step. Then while pressing the right mouse button, drag the Command object onto a blank form. Visual Basic displays a popup menu that lets you select whether to create a data grid or bound control. When you select Data Grid, Visual Basic automatically creates a data grid that displays records from the command's recordset.
When you run the form, Visual Basic displays the records returned by the FrenchCustomersOrders Command object in a data grid.
This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb.
|
To |
See |
|
Go to the next step |
Create a Master/Detail FormvbconCreateDetailReportOnForm |
|
Start from the beginning |
Interacting with Data in a Microsoft Jet/Microsoft Access DatabasevbconConnectToISAMDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconCreateDetailReportOnFormC"}
In database applications, it's often useful to simultaneously view a record with a group of related records. For example, you may want to view a customer record and the current orders for the customer. A common way to accomplish this is to create a master/detail form.
To create a Master/Detail form
1. Create a parent/child data environment Command object.
2. Drag the Command object from the Data Environment designer to a blank form.
Note This topic is part of a series that walks you
through creating a simple database application that interacts with data
in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft
Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
You've seen in the "Create a Data Environment Command Object" and "Create a Data Grid Form Based on a Query" topics how to create a data environment Command object based on a single table or query. You can also use a hierarchical parent/child Command object that lets you simultaneously view a record from one table or query with a group of related records in a second table or query.
For example, you can create a parent Command object that returns address information for all French customers in the Northwind Traders sample database. You can then add a related child Command object to the parent Command object that displays order information for each French customer.
Create the parent Command object by following the instructions in the previous steps in this scenario to open an existing Data Environment designer or create a new one. Then click the Add Command button on the Data Environment toolbar and set the following properties in the Command Properties dialog box:
|
Property |
Setting |
|
Command Name |
FrenchCustomers |
|
Connection |
Connection1 |
Select SQL Statement as the source of data for the Command object.
Click the SQL Builder button to open the Query designer, drag the Customers
table from the Data View window to the upper pane of the Query designer, and
then click the check box next to the CustomerID, CompanyName, Address, City,
PostalCode, and Country fields. Specify the criteria for the query by entering
"
SELECT CustomerID, CompanyName, Address, City, PostalCode, Country
FROM Customers
WHERE (Country = '
Add the child command to the parent command by right-clicking the FrenchCustomers command in the Data Environment designer, and then select Add Child Command from the popup menu. Set the following properties in the Command Properties dialog box:
|
Property |
Setting |
|
Command Name |
OrderDates |
|
Connection |
Connection1 |
Select SQL Statement as the source of data for the Command object. Click the SQL Builder button to open the Query designer, drag the Orders table from the Data View window to the upper pane of the Query designer, and then click the check box next to the OrderID, CustomerID, OrderDate, and ShippedDate fields. The Query designer builds the following SQL statement:
SELECT OrderID, CustomerID, OrderDate, ShippedDate
FROM Orders
Finally, define the relationship between the parent and child Command objects. In the Data Environment designer, select the OrderDates Command object and click the Properties button on the toolbar. Click the Relation tab. Define a relationship between the CustomerID fields in the parent and child Command objects by selecting CustomerID in the Parent Fields and Child Fields/Parameters lists. Then click the Add button. The Data Environment designer adds the relation to the Relation Definition box.
When you've finished, the data environment displays a hierarchical view of the parent Command object and its fields along with the child Command object and its fields.
For More Information See How Commands are Exposed for Programmatic Access!ALink("vbconHowDECommandsExposed").
As with the data-bound forms based on a single table or query, you can create a master/detail form based on a hierarchical parent/child Command object by simply dragging the Command object from the Data Environment designer to a blank form.
For example, create a master/detail form that displays address information and related order information for French customers by selecting the FrenchCustomers Command object you created in the previous step. Then while pressing the right mouse button, drag the Command object onto a blank form. Visual Basic displays a popup menu that lets you select whether to create a data grid, hierarchical flex grid, or bound controls. When you select hierarchical flex grid, Visual Basic automatically creates a master/detail form based on the parent/child Command object.
When you run the form, Visual Basic displays the address records returned by the FrenchCustomers Command object and related order records returned by the OrderDates Command object.
This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb.
|
To |
See |
|
Go to the next step |
Create a Data-Bound ReportvbconMakeReportUsingDesigner |
|
Start from the beginning |
Interacting with Data in a Microsoft Jet/Microsoft Access DatabasevbconConnectToISAMDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconMakeReportUsingDesignerC"}
You can create a data-bound report using the same drag-and-drop technique you used to create data-bound forms. Simply create a Command object based on a table or query, then drag it onto a blank report in the Data Report designer.
In this topic, you'll create a report that displays orders for French customers in the Northwind Traders sample database.
To create a data-bound report
1. Create a data environment Command object.
2. Open a Data Report designer.
3. Drag the Command object from the Data Environment designer to the Data Report designer.
4. Set data properties for the report.
Note This topic is part of a series that walks you
through creating a simple database application that interacts with data
in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft
Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
You can follow the steps in the "Create a Data Environment Command Object" topic to create a command based on a table or in the "Create a Data Grid Form Based on a Query" to create a command based on a query. In this topic, you'll base the report on the FrenchCustomersOrders command you created in the "Create a Data Grid Form Based on a Query" topic.
The Data Report designer lets you easily create reports from within the Visual Basic environment. You can use the Data Report designer to create a multiple-section data report, add controls to the report, and bind the report to data. You can also drag Command objects from the Data Environment designer to the Data Report designer.
To add a data report to your project, click Add Data Report on the Project menu. If the Data Report 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.
Note 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.
For More Information See Writing Reports with the Microsoft Data Report DesignervbconUsingMicrosoftDataReportDesigner.
Dragging a data environment Command object onto a Data Report designer is similar to dragging it onto a form. When you drop the Command object onto the report's Detail section, the Data Report designer automatically creates controls to display data from the Command object's recordset and sets data properties that bind the controls to fields in the recordset.
For example, to create a report that displays the orders of French customers, select the FrenchCustomersOrders Command object you created in the "Create a Data Grid Form Based on a Query" topic. Then drag the Command object onto the detail section of a blank report.
When you drag a Command objectfrom the Data Environment, the Data Report designer automatically sets data properties for each control that it creates. However, before the report can display data, you must also set the report's DataSource and DataMember properties.
For example, to display the orders of French Customers, set the properties as follows:
|
Property |
Setting |
|
DataMember |
FrenchCustomersOrders |
|
DataSource |
MyDataEnvironment |
When you run the report, the Data Report designer creates a report based on the records returned by the FrenchCustomersOrders Command object.
This topic concludes a series that walks you through creating a simple database application that interacts with data in Nwind.mdb. To start from the beginning, see Interacting with Data in a Microsoft Jet/Microsoft Access DatabasevbconConnectToISAMDatabase.
# vbconConnectToISAMDatabase
T 183934
$ Interacting with Data in a Microsoft Jet/Microsoft Access Database
A vbconBindControlsToQueryFieldsC;vbconConnectToISAMDatabase;vbconCreateConnectionToMDBFileC;vbconCreateDetailReportOnFormC;vbconCreateQueryWithDesignerC;vbconDataControlC;vbconMakeFormFromQuery_C;vbconMakeReportUsingDesignerC;
> LangRef
K Access
(Microsoft);data access, JET and
# vbconCreateConnectionToMDBFile
T 183935
$ Create a Connection to a Microsoft Access Database File
A vbconCreateConnectionToMDBFile;vbconCreateQueryWithDesignerC;
> LangRef
K data access, connecting to JET using Data Environment designer;
# vbconCreateQueryWithDesigner
T 183936
$ Create a Data Environment Command Object
A vbconBindControlsToQueryFieldsC;vbconCreateDetailReportOnFormC;vbconCreateQueryWithDesigner;
> LangRef
K Data Environment Command object, creating;
# vbconBindControlsToQueryFields
T 183937
$ Create a Simple Data-Bound Form
A vbconBindControlsToQueryFields;vbconCreateDetailReportOnFormC;
> LangRef
K Data Environment designer, simple data bound form;
# vbconMakeFormFromQuery
T 230384
$ Create a Data Grid Form Based on a Query
A vbconCreateDetailReportOnFormC;vbconMakeFormFromQuery;vbconMakeReportUsingDesignerC;
> LangRef
K Data Environment designer, query;Data Environment designer, simple data bound form;
# vbconCreateDetailReportOnForm
T 183939
$ Create a Master Detail Report on a Form
A vbconCreateDetailReportOnForm;
> LangRef
K Data Environment designer, master/detail form;Data Environment designer, simple data bound form;
# vbconMakeReportUsingDesigner
T 183951
$ Create a Data-Bound Report
A vbconMakeReportUsingDesigner;
> LangRef
K Data Environment designer, simple data bound form;