Face it: .NET is coming (if it's not already here for you). The faster you learn it, the faster you can benefit from it. This month, Rick Dobson tackles the topic of how to make VB.NET play with Access databases.
As you've probably noticed, Microsoft is betting very heavily on the .NET Framework. Does this matter to you? As an Access developer, you typically program Jet database files with VBA in process from Access. Sometimes, however, you also create applications using Visual Basic out of process from some other application. With either approach, you have a choice of using DAO or ADO. What .NET allows you to do is implement many typical Access applications using VB.NET and ADO.NET. ADO.NET is a major upgrade of data access technology. Access developers who take the time to learn VB.NET and ADO.NET are equipping themselves for the next generation of Microsoft technology.
As an Access developer, you can legitimately ask why the .NET Framework should consume any of your time. The answer: Because the .NET Framework solves important problems for you. In particular, the .NET Framework provides great scalability for database solutions and excellent Web interoperability. "Scalability" means that your Access solution can handle more than a small number of users. "Interoperability" with the Web means opening new, growing markets to Access developers. Both of these benefits are important for the vibrancy and the growth of Access developers.
I'm going to give you an introduction on how to use VB.NET to retrieve data from Jet databases and show the results on a Windows form. You'll see how to show data in a datasheet style as well as how to navigate between rows for a bound form with text boxes. The good news is that the VB.NET IDE offers numerous graphical aids for pointing a project at a Jet database, creating a disconnected pool of Access data in a VB.NET project, and binding controls on a form to the disconnected pool of data. The bad news is that you have to learn about the new ADO.NET classes and Windows form controls that implement data binding. Happily, a beginner's grasp of these classes and controls is sufficient to implement a wide range of typical data retrieval tasks. This article shows the graphical tools, describes the ADO.NET classes, and demonstrates how to use them with VB.NET on Windows forms. You'll find much of this looks similar to the environment that you use when working in Access.
Why and how to use .NET with Access
It's common for Access applications to experience degraded performance as the number of concurrent users increases. The point at which performance degradation becomes noticeable varies for different types of applications. However, one of my seminar registrants recently confided that he was forced to switch to using unbound data development techniques with as few as four concurrent users. His workaround involved storing unbound Access data in a hidden list box on a form. If you've had to resort to similar approaches or if you have applications with degraded performance as the number of users has grown, you'll appreciate the built-in disconnected data access features of ADO.NET.
Access database files aren't commonly used for Web-based solutions even though Access is a very widely available database. The native Access solution–Data Access Pages, which have been available since Access 2000–is based on ActiveX controls, which makes the pages incompatible with some browsers. Active Server Pages, another technique for processing Jet data through a browser connection, is code-intensive and can often result in spaghetti code with interspersed VBScript and HTML.
ASP.NET, on the other hand, offers a code-behind-page development model that resembles the Access code-behind-form development model. The code-behind-page model isolates VB.NET code from the HTML on a page. In addition, you can program ASP.NET solutions with VB.NET. ASP requires you to program solutions with a scripting language, such as VBScript, which isn't exactly the same as either VBA or VB. The .NET Framework also allows you to expose a Jet database over the Web via XML Web Services. While you may have heard that Visual Basic .NET is very different from Visual Basic, most of the differences are in the object models–.NET uses a different set of objects than Access does. The actual language constructs haven't changed, except to add new features (which you're free to ignore).
So how do you create .NET solutions for Access database files? There are three main answers to this question:
• First, you can use a Windows form. This type of form is for creating solutions that run on a Windows desktop.
• Second, you can use a Web form. ASP.NET uses this type of form for Web-based solutions in the same way Windows forms let you create desktop solutions.
• Third, you can expose data from an Access database file as an XML Web Service. Coverage of XML Web Services is beyond the scope of this article, but the approach is a very powerful means for exposing Access over the Web. You can use either a Windows form application or a Web form application as a client for an XML Web Service.
A Windows form in the .NET Framework works similarly to a traditional Access form. In addition, you can manipulate a Windows form in ways that generally parallel an Access form. For these reasons, this article will highlight techniques for using Windows forms.
Windows forms and ADO.NET classes
You can obtain VB.NET with ADO.NET from any Microsoft edition of Visual Studio .NET or from Visual Basic .NET Standard Edition, which is substantially less expensive than all Visual Studio .NET editions. With the release of .NET 2.0, Microsoft is making available Express versions of the .NET products to allow experimenters and hobbyists to get started with .NET. Visual Basic .NET Standard Edition is also available with Visual Studio Tools for the Microsoft Office System, which includes a royalty-free license for the Access 2003 runtime and other goodies of interest to Access developers.
After opening Visual Studio .NET, you can start a project by clicking the New Project button. This opens the New Project dialog box, which has two panes–Project Types and Templates. Select Visual Basic Projects from the Project Types pane, and highlight Windows Application in the Templates pane. These selections allow you to click OK to open a new Windows form application with a default name. You can override the default project name with any other name you prefer. This article uses the name SA0804 for its project.
After starting a Windows form application, you'll see a blank form with a caption of Form1. Windows forms use the Text property to set the form's caption, and you should replace Form1 with a more meaningful name. The Name property for a Windows form sets the programmatic handle that you use to reference the form within VB.NET. You'll find that the Text and Name properties appear in numerous other .NET Windows controls as well.
As an Access developer, you know how convenient it is to switch from working on your application's forms and code to working on your application's tables and queries. Server Explorer, one of the many windows available from the VB.NET IDE, allows you to connect to your Access database from within Visual Studio .NET. You can open Server Explorer from the View menu. Right-click Data Connections in Server Explorer and choose Add Connections to open a Data Link Properties dialog box. From this familiar dialog box, you can choose a Jet data provider on the Connections tab and then navigate to the Access database file that you wish to use. This process adds an item in the Data Connections folder of Server Explorer that you can use to access objects in an Access database file.
A Data Connection item pointing at an Access database can have up to three folders labeled Tables, Views, and Stored Procedures. The Views and Stored Procedures folders contain Access queries. You can drag an object name from the Tables, Views, or Stored Procedures folder to create OleDbConnection and OleDbDataAdapter ADO.NET components in a components tray below a Windows form. The first time you drag a query to a Windows form to generate a Data Connection item, the VB.NET IDE asks if you want to save the password with the assembly for the Visual Basic project. This OleDbConnection object points at a whole Access database, while the OleDbDataAdapter points to a particular data source within the database file. Figure 1 shows the IDE immediately after dragging the Shippers table from the Northwind database to Form1 in the SA0804 project.
An OleDbDataAdapter object can serve as a two-way pump between an Access database and a VB.NET project. In order for the OleDbDataAdapter to pump data from a data source to a project, it requires an ADO.NET DataSet in the project. A DataSet can act as a container for data pumped from a database to a VB.NET project. You can create a "Typed DataSet" by right-clicking an OleDbDataAdapter and then selecting Generate Dataset. This selection opens the Generate Dataset dialog box that you can use to name a new DataSet or select an existing DataSet. By default, the dialog box names new DataSet objects DataSetx, where x is an integer such as 1. Accepting the default settings for the first DataSet in a project creates DataSet1. The VB.NET IDE adds an item to the components below the form named DataSet11 that points at DataSet1. This sounds confusing but it does make sense–if you drag a TextBox to a form, you get a component called TextBox1; so, when you create a component from DataSet1, it's called DataSet11. In the same way that you use TextBox1 from your code, you use DataSet11 in VB.NET code when referencing DataSet1.
Showing Access data on a DataGrid control
The Toolbox, which you can open from the View menu, corresponds to the Access Toolbox. The Toolbox contains controls and other components that you can use to populate a project. Select the Windows tab in the Toolbox to expose controls for populating a Windows form. One of the most powerful Windows form controls for showing data is a DataGrid control. With this control, your VB.NET solutions can display data similarly to the way a datasheet in Access or a tabular layout in an Access report displays data.
You can add a DataGrid to a Windows form by dragging it from the Toolbox to a form. After dragging the control to the form, you can resize the control or move it by clicking and dragging with your mouse, as you would in Access. The Properties window, another VB.NET IDE window, makes it simple to connect the DataGrid with a DataSet. As with other IDE windows, you can show the Properties windows from the View menu if it's not already available.
When you generate a new DataSet for an OleDbDataAdapter or point an OleDbDataAdapter at an existing DataSet, the DataSet receives a DataTable with the same name as the data source for the OleDbDataAdapter. The name of the DataTable in my example is Shippers because I dragged the Shippers table to Form1 to create OleDbDataAdapter1. You can connect a DataGrid control to a DataTable in a DataSet by specifying the DataSet and DataTable in the Properties window as the data source for the DataGrid control.
Figure 2 shows the Properties window selection for binding DataGrid1 in Form1 to the Shippers DataTable available through DataSet11. As you can see, the DataGrid shows the column names from the Shippers table in the Northwind database. DataSet11 points at DataSet1 and a DataSet object in ADO.NET contains a schema that describes the data design, including the tables in the DataSet. In this example, the DataSet's schema includes the Shippers table from the Northwind database.
The following line of code from the Load event procedure populates the Shippers DataTable by invoking the Fill method for OleDbDataAdapter1 and passing it the DataSet to put the table in and the name to give the table created in the DataSet:
After you place this single line of code in the Load event procedure for Form1, DataGrid1 can display the contents of the Shippers table whenever it opens. Figure 3 shows the DataGrid control with data from the Shippers DataTable. The selection in the Properties window for the DataSource property of DataGrid1 caused the filled Shippers DataTable to appear in the DataGrid.
Dynamically assigning a DataSource property
Instead of assigning a DataGrid's DataSource property at design time, you can allow users to assign the data source for a DataGrid at runtime. Figure 4 shows two versions of a Windows form with two Button controls and a DataGrid control. The top version of Form2 shows the form after a click to the Button control labeled Shippers. The second version of Form2 displays selected columns from the Employees table. In order to display the table column values without the need for users to adjust column widths manually, I changed the default widths for both the form and the DataGrid, as well as the PreferredColumnWidth property for the DataGrid.
Form2 in Figure 4 runs in the same project as Form1 in Figure 3. With an Access project, you'd use the New button on the Database window toolbar to add a new form to your project. You add a new Windows form to a Visual Basic .NET project by using the Project | Add Windows Form menu choice. You pick which form will open when your application runs by making that form the Startup object. This is done from the project's Property Pages dialog box–just right-click on the project name in Server Explorer and select Properties. You can also programmatically open one form from the code in another, for example, by invoking the Show method on the form that you want to open.
After creating the new form, I copied the OleDbConnection1, OleDbDataAdapter1, and DataSet11 components tray items from Form1 to Form2. In addition, I dragged the Employees table from the Data Connection item for the Northwind database to Form2. Releasing the table over the form created OleDbDataAdapter2 pointing at the Employees table. By clicking the Build button (...) in the Properties window for the CommandText property of the SelectCommand object in OleDbDataAdapter2, I was able to reduce the number of columns returned by the query to FirstName, LastName, and HomePhone. Figure 5 shows the Query Builder dialog box that I used–not very different from the Query Design window in Access. While Query Builder doesn't work exactly like the Query Designer in Access, anyone familiar with Query Designer is likely to be instantly handy with Query Builder.
After you edit an OleDbDataAdapter to your preference, right-click it and choose Generate Dataset to add the data retrieved by the OleDbDataAdapter to a DataSet. For this article's sample application, I selected the existing SA0804.DataSet1 as the container for the Employees DataTable.
The following code is from Form2. The form's Load event procedure and the two Click event procedures for the Button controls were automatically generated by double-clicking each object following the Visual Basic model rather than the Access model. Once you're in the code-behind module, you can add new routines as you would in Access by selecting objects from the dropdown box on the left and the event from the dropdown box on the right.
The Load event procedure has two main parts. First, it populates the Shippers and Employees DataTable objects by invoking the Fill methods for OleDbDataAdapter1 and OleDbDataAdapter2. Second, the code specifies selected formatting for Form2's DataGrid and two Button controls. While it's sometimes easier to specify properties initially through the Properties window, it's generally simpler to modify property settings after you express them in code. The Click event procedures for Button1 and Button2 assign either the Shippers or Employees DataTable objects from DataSet11 to the DataSource property of DataGrid1. With the assignments in the two Click event procedures, users can manually change the data source for the DataGrid control at runtime.
Private Sub Form2_Load( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
DataGrid1.Left = _
(Me.ClientSize.Width - DataGrid1.Width)/2
Button1.Left = 20
Button2.Left = Me.ClientSize.Width _
- (Button2.Width + 20)
Private Sub Button1_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
DataGrid1.DataSource = DataSet11.Shippers
Private Sub Button2_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
DataGrid1.DataSource = DataSet11.Employees
Navigating with TextBox controls
Using TextBox controls to display column values from a DataTable requires considering different issues than those for displaying data values in a DataGrid control. This is because the DataGrid control can concurrently bind to all the column values in all the rows of a DataTable. In contrast, the Text property for a single TextBox control must bind to a single column of a DataTable, and the TextBox control can display the column value from a single row. A Windows form must have one TextBox control for each column of values that it wants to show from a DataTable. In addition, a Windows form must provide some means of navigating between rows in a DataTable so that the TextBox controls can show column values from multiple rows.
After adding TextBox controls, such as the three text boxes in Form3 of the SA0804 project, you can bind the controls to columns in a DataTable through the Properties window. After selecting a TextBox control, expand the DataBindings property in the Properties window. Click the down arrow next to the Text property within the DataBindings property. This exposes a list of the typed DataSets, such as DataSet1 in the SA0804 project. You can also see DataTable objects within the DataSet as well as column names within a DataTable. By highlighting a column, you can bind the Text property for the selected TextBox to the column. Figure 6 shows the Text property for TextBox1 in Form3 bound to the ShipperID column in the Shippers DataTable. The Text property for TextBox2 and TextBox3 bind to the CompanyName and Phone columns, respectively, within the Shippers DataTable.
The Button controls in Form3 facilitate navigation between the rows of the Shippers DataTable in DataSet11. The Click event procedures for these controls manipulate the Position property of Form3's BindingContext class instance. This object applies to all the data-bound controls within Form3 that use a specific table. Therefore, by modifying the Position property value for the form's BindingContext, you change which row from the Shippers DataTable is shown in all three TextBox controls on the form.
Of course, you must first fill the DataSet containing the Shippers DataTable. The following line of code, which is identical to code behind Form1, retrieves the data:
Each of the four Button controls on Form3 performs a different navigation task. From Button1 through Button4, these tasks are: move to first row, move to previous row, move to next row, and move to last row. The code to implement each task takes just a single statement. These statements appear in order for Button1 through Button4. You specify which BindingContext object you're using by specifying the DataSet object and DataTable name. The index values for the rows of a DataTable are zero-based, so the index for rows extends from 0 for the first row through the count-or-rows-less-one for the last row. The BindingContext class instance is smart enough that you don't require any trapping logic for stepping out of the range of row index values for a DataTable.
Position = 0
Position = _
Position - 1
Position = _
Position + 1
Position = _
Count - 1
Figure 7 shows Form3 in operation. The form initially opens to the first row in the Shippers DataTable, but the figure depicts the form after a click to the Next button (>). This click advances the row index to point at the second row. As a consequence, the TextBox controls on the form show values from the second row, such as a ShipperID value of 2.
Notice that the runtime view of Form3 in Figure 7 displays its four Button controls differently than the four Button controls in the form's Design view that appears in Figure 6. The Load event procedure reformats the Button controls to set new control widths to 25 pixels and repositions the controls from a vertical to a horizontal alignment. In addition, new Text property values, such as << and <, replace the default ones of Button1, Button2, and so on. The following excerpt from the form's Load event procedure shows the code for achieving the transformation of the Button controls from their Design view appearance to their runtime appearance. Additional formatting (not shown here) applies to the Label controls on Form3.
Button1.Width = 25
Button2.Width = 25
Button3.Width = 25
Button4.Width = 25
Button2.Top = Button1.Top
Button2.Left = Button1.Left + Button1.Width
Button3.Top = Button1.Top
Button3.Left = Button2.Left + Button2.Width
Button4.Top = Button1.Top
Button4.Left = Button3.Left + Button3.Width
The odds are that if you're an intermediate to advanced Access developer, you'll eventually be creating solutions with VB.NET. An interesting question is when will "eventually" become "now"? With the content from this article, you can get started trying the .NET Framework with your Jet databases. This article drilled down on how to implement three basic data retrieval tasks for Access databases with VB.NET. You saw how to create a Windows form for a Jet database and how to populate a Windows form with data from a Jet database. You also saw the basic ADO.NET classes for managing Access data in a VB.NET solution. Finally, you saw the VB.NET techniques for managing data as well as formatting Windows forms and their controls.
Your download file is called 411DOBSON.ZIP in the file SA2004-11down.zip
This is found on this page