Access Cookbook - Ken Getz [321]
Dim cmdAltRock As OleDbCommand = _
New OleDbCommand("qryAlternativeAlbums", cnx)
' Odd as it may seem, you need to set the CommandType
' to CommandType.StoredProcedure.
cmdAltRock.CommandType = CommandType.StoredProcedure
' Run the query and place the rows in an OleDbDataReader.
Dim drAltRock As OleDbDataReader
drAltRock = cmdAltRock.ExecuteReader
Add the following code to bind the drAltRock OleDbDataReader to the dgrAltRock DataGrid control on the page:
' Bind the OleDbDataReader to the DataGrid
dgrAltRock.DataSource = drAltRock
dgrAltRock.DataBind( )
Save the page and preview it in your browser by right-clicking on the file name (AltRock.aspx) in the Solution Explorer window and selecting View in Browser from the popup menu. The resulting page should look similar to the one shown in Figure 17-5.
Figure 17-5. The data behind this DataGrid was retrieved from the 17-03.MDB database using the .NET OleDb provider
Discussion
Probably the trickiest part of retrieving data from an Access database using ADO.NET is in creating the connection string. The basic syntax of the connection string is as follows:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=path_to_database"
If you are using a workgroup-secured database, you will need to add User Id and Password items to the connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=path_to_database;" & _
"User Id=user_name;Password=password;"
If the database is password-protected, you will need to use the following connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=path_to_database;" & _
"Jet OLEDB:Database Password=database_password;"
TIP
If the database is stored in an Access 97 database, you should specify the Jet 3.51 provider (Microsoft.Jet.OLEDB.3.51) instead of the Jet 4.0 provider.
The steps for constructing a Windows Forms-based application that accesses an Access database are fairly similar.
This example binds the DataGrid to an OleDbDataReader object. You can also bind a DataGrid to a DataSet object. It's more efficient to use an OleDbDataReader; however, its usage is more limited. For example, if you wished to enable paging for the DataGrid, you would have to use a DataSet instead.
See Also
The following link provides a walkthrough for working with Access data from ADO.NET: Walkthrough: Editing an Access Database with ADO.NET (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/adon_wtaccessdb.asp).
Another helpful article on ADO.NET is Unlocking Microsoft Access Data with ADO.NET (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office12062001.asp).
The following article discusses how to create a pageable DataGrid using a DataSet: (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/aspnet-pageablesortable.asp).
If you're having trouble creating ADO.NET connection strings, check out Able Consulting's Connection Strings page (http://www.able-consulting.com/ADO_Conn.htm).
17.4. Call a Web Service from Access
Problem
A web service is a specially constructed component that you can access over standard web protocols. To call a web service, however, you must pass it messages encoded using Simple Object Access Protocol (SOAP). Access does not directly support the SOAP protocol. Is there any way to call a web service from Access?
Solution
Microsoft has released several toolkits that can be used by Microsoft Office programmers to call web services. This solution assumes you are using Access 2003 with Microsoft Office 2003 Web Services Toolkit. See the discussion section of this topic on calling web services from earlier versions of Access.
The RunnerCalculator web service contains a number of methods that provide pacing calculations for long distance running. This web service can be found at www.deeptraining.com/webservices. One of the RunnerCalculator methods, GetPaceDouble, can be used to calculate the pace in minutes per mile for a given