Programmatic Issues | Programming Microsoft Access 2000 (Microsoft Programming Series)

One way to extend the capabilities of your Access Projects is with SQL. This is particularly so for views and stored procedures. Several of the preceding samples have shown what you can do with SQL. However, you can also put your VBA and ADO knowledge to use. The remaining samples in this chapter show how to apply your expertise in these areas to working with Access Projects or even to working with SQL Server and MSDE databases without the convenient user interface of Access Projects.

Working with Forms

While the Access Project user interface delivers extraordinary functionality with remote data sources, you can automate and simplify processes by developing custom programmatic solutions. We'll look at three areas pertaining to Access Projects: opening a form, finding a record, and viewing changes made by other users.

Opening a form

When you open a form with the Access Project interface, the form populates a local copy of the remote data in the client workstation. This local copy is a snapshot, at a point in time, of the remote data for the form. When you open the form programmatically, you must create the local copy of the remote data. One advantage to programmatically opening a form is that you can dynamically assign values to the local cache of the remote data. Your application can do this because the recordset that you assign to the form with VBA overrides the Record Source setting on the form's property sheet.

The procedure below constructs a recordset for a form before opening it. It starts by setting a reference to a new recordset instance: It assigns adUseClient to the recordset's CursorLocation property to establish the location of the form's data. (Recall that the form gets the data from the local cache on the workstation, not from the remote server.) Next, it opens the recordset with a SQL statement that extracts data from the remote source into the local copy. The sample works with the form from Figure 12-14. A commented line shows a SQL statement that can override the form's default record source. After making the local copy of the remote data, the procedure opens the form and assigns the local copy to the form's Recordset property. This new property possesses the functionality of the RecordsetClone property; in addition, changes to the recordset appear on the form automatically-the RecordsetClone property provides a read-only copy of a form's recordset. Like the RecordsetClone property, a form's Recordset property is available only programmatically.

 Sub openForm() Dim rst1 As ADODB.Recordset 'Establish a local recordset and populate it with values; 'can override property sheet settings.     Set rst1 = New ADODB.Recordset     rst1.CursorLocation = adUseClient     rst1.Open "Select * From employees", _         CurrentProject.Connection, adKeySet, _         adLockPessimistic 'Optionally run with where clause to demo override effect. '    "Select * From employees where employeeid>3" 'Open the form.     DoCmd.openForm "frmemployees2" 'Assign recordset to the open form; can override a 'setting on the property sheet.     Set Forms("frmemployees2").Recordset = rst1 End Sub

NOTE
The recordset created for a recordset instance is available only if the form remains open. Closing and reopening the form manually causes the form to revert to the Record Source setting on its property sheet.

Finding a record

One common task when you work with data in a form is finding a specific record. The following two procedures accomplish this task. The locateEmployee procedure prompts for an employee ID value and passes that value along to the second procedure, findByID. The second procedure taps the FindRecord method of the DoCmd object to search for and position the form at a new record.

 Sub locateEmployee() 'Ask for employee ID and pass it on to findByID.     employeeNumber = InputBox("Type the ID for the employee you want", _         "Programming Microsoft Access 2000")     findById CLng(employeeNumber) End Sub Sub findById(eid As Long) On Error GoTo findByIdTrap 'Set focus to employee ID field and launch find.     Forms("frmemployees2").EmployeeID.SetFocus     DoCmd.FindRecord eid findByIdExit: 'Report mismatch before exiting.     If Forms("frmemployees2").EmployeeID <> eid Then         MsgBox "No employee with ID " & eid & ".", _             vbExclamation, "Programming Microsoft Access 2000"     End If     Exit Sub      findByIdTrap:     If Err.Number = 2450 Then 'Open form if it is closed and start find again.         openForm         Resume     Else         Debug.Print Err.Number, Err.Description     End If End Sub

The second procedure detects two potential problems. First, if there is no match, the procedure issues a message to that effect. Second, neither procedure is an event procedure, so they can be invoked from outside the form. If the form is closed, the procedure detects the problem and opens it so that it can try again to find the target record.

Viewing changes made by other users

Access is inherently a multi-user development environment, and Access Projects frequently serve multi-user purposes. Therefore, you need a way to refresh the local cache so that you can view updates, additions, and deletions made by others. The following procedure does this by repopulating the local recordset for the form from the remote data source. Like the preceding samples, this one is based on the form in Figure 12-14.

 Sub requeryRemoteRestoreID() Dim int1 As Integer 'Turn off screen updates and save employee ID.     DoCmd.Echo False     int1 = Forms("frmemployees2").EmployeeID 'Requery local recordset from the server.     openForm      'Reposition to employee ID before requery and 'restore screen updating.     Forms("frmemployees2").EmployeeID.SetFocus     DoCmd.FindRecord int1     DoCmd.Echo True      End Sub

The procedure refills the local data cache by invoking the openForm procedure (which was discussed earlier). If the form is already open, the call simply repopulates the cache. Since repopulating a form's recordset automatically makes the first record current, the procedure saves the current record position before repopulating the local cache from the remote data store. After repopulating the cache, the procedure searches through the records to find the preceding current record. This restores the old record position if that record is still available (that is, not deleted). If another user has deleted the former current record, the form displays the first record in the local cache.

Working with Standalone Modules

All the emphasis on ADO throughout this book will serve you well when you develop custom solutions against MSDE and SQL Server data sources. Chapter 4 shows some samples for dealing with remote data sources. Here, we'll revisit some programmatic issues in the context of Access Projects and MSDE.

Opening a table

The following sample integrates opening a table in a remote data source with writing the results to the Immediate window as a telephone directory. It uses ADO Connection and Recordset objects to do this. The procedure starts by creating a new instance of an ADO Connection object. Then it defines a connection string and uses that string to open a connection to the NorthwindCS database on the cab2200 server. Next, it opens a forward-only, read-only recordset based on the Employees table in NorthwindCS. This type of recordset is acceptable for a report that makes a single pass through a recordset. The procedure prints the employee directory in the Immediate window with the help of a Do loop for passing through successive records.

 Sub openTableOnRemoteServer() Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset 'Open connection to NorthwindCS database 'on cab2200 server.     Set cnn1 = New ADODB.Connection     strCnn = "Provider=sqloledb;" & _         "Data Source=cab2200;" & _         "Initial Catalog=NorthwindCS;" & _         "User Id=sa;Password=;"     cnn1.Open strCnn      'Open employee table with a forward-only, 'read-only recordset; this type is OK for a single 'pass through the data.     Set rst1 = New ADODB.Recordset     rst1.CursorType = adOpenForwardOnly     rst1.LockType = adLockReadOnly     rst1.Open "employees", cnn1, , , adCmdTable 'Print an employee telephone directory.     Do Until rst1.EOF         Debug.Print rst1.Fields("FirstName") & " " & _             rst1.Fields("LastName") & " has extension " & _             rst1.Fields("Extension") & "."         rst1.MoveNext     Loop 'Close the connection and recover the resource.     cnn1.Close     Set cnn1 = Nothing End Sub

The test environment for this chapter has an MSDE server on the local machine named CABXLI in addition to the SQL Server database manager on the machine named cab2200. The syntax for referring to local servers is slightly different than the syntax for referring to remote ones. Instead of having to designate a specific server by name, you can simply specify (local). Since my local computer also has the NorthwindCS database installed, no other change in the procedure is necessary. The procedure is identical to the preceding one with the exception of the one block that appears below. Notice the new server name. In practice, you might want to have users designate their own login account name or have them use a limited one with fewer privileges than sa.

 'Open connection to NorthwindCS database 'on the local server.     Set cnn1 = New ADODB.Connection     strCnn = "Provider=sqloledb;" & _         "Data Source=(local);" & _         "Initial Catalog=NorthwindCS;" & _         "User Id=sa;Password=;"     cnn1.Open strCnn

You can process views by using the same syntax that you use for tables. Simply enclose the view's name in quotes, just like you do for a table. You must still use the adCmdTable setting for the Option parameter. The companion CD includes a sample named openViewOnRemoteServer that demonstrates this approach.

Opening a stored procedure

Stored procedures can return recordsets. Your applications have more flexibility when they provide recordsets using stored procedures instead of tables because you are not restricted to the tables in the current data source or even exact copies of the tables in the current data source. With stored procedures, you can filter records, compute new values, and aggregate field values across the records in a table.

The following procedure invokes the stored procedure named Ten Most Expensive Products and prints in the Immediate window the 10 product names and prices. Because the procedure name includes spaces, it must be in brackets. Notice also that the procedure uses an Options argument of adCmdStoredProc. This tells the ADO processor to expect a procedure name that contains SQL, not an actual SQL statement. Since the procedure always processes 10 records, the procedure for printing out the records uses a For loop that goes from 1 through 10. In other respects, the procedure for printing the return set from a stored procedure is identical to the procedure for printing an entire table.

 Sub openProcedureOnRemoteServer() Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Dim int1 As Integer 'Open connection to NorthwindCS database 'on cab2200 server.     Set cnn1 = New ADODB.Connection     strCnn = "Provider=sqloledb;" & _         "Data Source=cab2200;" & _         "Initial Catalog=NorthwindCS;" & _         "User Id=sa;Password=;"     cnn1.Open strCnn      'Open employee table.     Set rst1 = New ADODB.Recordset     rst1.CursorType = adOpenForwardOnly     rst1.LockType = adLockReadOnly     rst1.Open "[Ten Most Expensive Products]", _         cnn1, , , adCmdStoredProc 'Print prices for 10 products.     For int1 = 1 To 10         Debug.Print rst1.Fields(0) & " has a unit price " & _             "of $" & rst1.Fields(1) & "."         rst1.MoveNext     Next int1 'Close the connection and recover the resource.     cnn1.Close     Set cnn1 = Nothing End Sub

Assigning CursorLocation values

CursorLocation settings can profoundly affect performance, especially as table sources grow to even moderate size. The sample below demonstrates this with a recordset source of just over 19,000 records. It forms this recordset with a view based on the Cartesian product of the Employees and Order Details tables. The view's name is vwLargeView.

The sample opens its recordset source with either adUseClient or adUseServer as its CursorLocation setting. The adUseServer setting causes a procedure to progressively move through the records one at a time on the server. The adUseClient setting transfers the records to the local workstation so that procedures can access the records from a local workstation without returning to the server for each record.

The first procedure in the sample prompts for an instruction about which CursorLocation setting to use. The second procedure prepares a report and prints it in the Immediate window. It prints the cursor location and type as well as the start time, end time, and the duration for the task. You can adapt this general model for testing various combinations of database settings with your own data sources.

 Sub openRemoteWithCursorLocation() Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Dim start As Date, done As Date 'Open connection to NorthwindCS database 'on cab2200 server.     strCnn = "Provider=sqloledb;" & _         "Data Source=cab2200;" & _         "Initial Catalog=NorthwindCS;" & _         "User Id=sa;Password=;"     Set cnn1 = New ADODB.Connection     If MsgBox("Use local cursor?", vbYesNo, _         "Programming Microsoft Access 2000") = vbYes Then         cnn1.CursorLocation = adUseClient     Else         cnn1.CursorLocation = adUseServer     End If     cnn1.Open strCnn      'Open vwLargeView view; create the view in the remote 'database server before running the procedure. 'The sample uses the Cartesian product of the 'Employees and Order Details tables. 'Notice that the connection setting for CursorType silently 'overrides the recordset property setting.     Set rst1 = New ADODB.Recordset     rst1.CursorType = adOpenKeyset     rst1.LockType = adLockOptimistic     rst1.Open "vwLargeView", cnn1, , , adCmdTable     start = Now     Do Until rst1.EOF         temp = rst1.Fields(1)         rst1.MoveNext     Loop     done = Now     reportResults cnn1.CursorLocation, rst1.CursorType, _         start, done End Sub Sub reportResults(cloc As Integer, ctype As Integer, _     startedAt As Date, endedAt As Date)     Debug.Print "Results for: "     Select Case cloc         Case adUseServer             Debug.Print "     Cursor Location: adUseServer"         Case adUseClient             Debug.Print "     Cursor Location: adUseClient"         Case Else             Debug.Print "     Faulty Cursor Location setting"     End Select     Select Case ctype         Case adOpenForwardOnly             Debug.Print "     Cursor Type: adOpenForwardOnly"         Case adOpenKeyset             Debug.Print "     Cursor Type: adOpenKeyset"         Case adOpenDynamic             Debug.Print "     Cursor Type: adOpenDynamic"         Case adOpenStatic             Debug.Print "     Cursor Type: adStatic"     End Select     Debug.Print "Start time to nearest second: " & startedAt     Debug.Print "End time to nearest second: " & endedAt     Debug.Print "Difference in seconds: " & DateDiff("s", & _         startedAt, endedAt) End Sub

Figure 12-18 shows the results of running the procedures using an adUseClient setting and then an adUseServer setting. It took just 1 second to loop through the records with a local cursor, but it took 37 seconds to loop through the records with a server-side cursor. Results can vary depending on a wide range of factors, so it's a good idea to test for other settings and your particular databases.

click to view at full size.

Figure 12-18. Sample output showing results of processing the same data source with an adUseClient or an adUseServer setting.

This sample is instructive for a few reasons. First, the performance outcomes are drastically different. Second, you can see that ADO changes your settings if they conflict. For example, the program sets the cursor type to adOpenKeyset, but this setting conflicts with adUseClient, so the ADO interpreter silently changes the cursor type setting to adStatic. This happens in other contexts as well. Third, this sample presents a simple model that you can readily adapt for exploring database settings in your own custom applications.