| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
As you might have inferred from the previous chapter, it is very easy to implement client/server ineffectively. This can result in worse performance rather than better performance. The developer's task is to intelligently apply appropriate techniques that deploy client/server systems effectively.
The following sections discuss strategies to help you develop smart client/server applications.
Sometimes it is best to create a dynaset, and at other times it is more efficient to create a snapshot. It is very important that you understand under what circumstances each choice is the most appropriate.
In essence, a dynaset is a collection of bookmarks that enables each record on the server to be identified uniquely. Each bookmark corresponds to one record on the server and is generally equivalent to the primary key of the record. Because the bookmark is a direct pointer back to the original data, a dynaset is an updatable set of records. When you create a dynaset, you create a set of bookmarks of all rows that meet the query criteria. If you open a recordset using code, only the first bookmark is returned to the user's PC's memory. The remaining columns from the record are brought into memory only if they are directly referenced using code. This means that large fields, such as OLE and Memo, are not retrieved from the server unless they are explicitly accessed using code. Access uses the primary key to fetch the remainder of the columns. As the code moves from record to record in the dynaset, additional bookmarks and columns are retrieved from the server.
All the bookmarks are not retrieved unless a MoveLast method is issued or each record in the recordset is visited using code.
Although this keyset method of data retrieval is relatively efficient, dynasets carry significant overhead associated with their editability. This is why snapshots are often more efficient.
A snapshot is a set of records returned from a query.
When you open a snapshot type of recordset, all columns from the first row are retrieved into memory. As you move to each row, all columns within the row are retrieved. If a MoveLast method is issued, all rows and all columns meeting the query criteria are immediately retrieved into the client machine's memory. Because a snapshot is not editable and maintains no link back to the server, it can be more efficient. This is generally true only for relatively small recordsets. The caveat lies in the fact that all rows and all columns in the result set are returned to the user's memory whether they are accessed or not. With a resultset containing over 500 records, the fact that all columns are returned to the user's memory outweighs the benefits provided by a snapshot. In these cases, you may want to create a Read Only dynaset.
If your data does not need to be updated and it is sufficient to move forward through a recordset, you may want to use a forward-scrolling snapshot. Forward-scrolling snapshots are extremely fast and efficient. You create a forward-scrolling snapshot using the dbForwardOnly option of the OpenRecordset method. This renders the recordset forward-scrolling only. This means that you cannot issue a MovePrevious or MoveFirst method. You also cannot use a MoveLast. This is because only one record is retrieved at a time. There is no concept of a set of records, so Access cannot move to the last record. This method of data retrieval provides significantly better performance than regular snapshots with large recordsets.
The fact that dynasets return a set of primary keys causes problems with forms. With a very large set of records and a large primary key, sending just the primary keys over the network wire can generate a huge volume of network traffic. When you open a form, Access retrieves just enough data to display on the form. It then continues to fetch the remainder of the primary keys satisfying the query criteria. Whenever keyboard input is sensed, the fetching process stops until idle time is available. It then continues to fetch the remainder of the primary keys. To prevent the huge volume of network traffic associated with this process, you must carefully limit the size of the dynasets that are returned. Methods of accomplishing this are covered in the section titled Optimizing Forms.
It is important to remember that executing pass-through queries and stored procedures is much more efficient than returning a recordset to be processed by Access. The difference lies in where the processing occurs. With pass-through queries and stored procedures, all the processing is completed on the server. When operations are performed using VBA code, all the records that will be affected by the process must be returned to the user's memory, modified, and then returned to the server. This generates a significant amount of network traffic and slows down processing immensely.
In dealing with ODBC databases, connections to the server are transparently handled by Jet. When you issue a command, a connection is established with the server. When you finish an operation, Jet keeps the connection open in anticipation of the next operation. The amount of time that the connection is cached is determined by the ConnectionTimeout setting in the Windows Registry. You may want to utilize the fact that a connection is cached to connect to the back-end when your application first loads, before the first form or report even opens. The connection and authentication information will be cached and used when needed.
As seen in the LinkToSQL routine in chapter 20, you can send password information stored in variables as parameters when creating a link to a server. These values could easily have come from a login form. The following code preconnects to the server. It would generally be placed in the startup form for your application:
Sub PreConnect(strDBName As String, _
strDataSetName As String, _
strUserID As String, _
strPassWord As String)
Dim db As DATABASE
Dim strConnectString As String
strConnectString = "ODBC;DATABASE=" & strDBName & _
";DSN=" & strDataSetName & _
";UID=" & strUserID & _
";PWD=" & strPassWord
Set db = OpenDatabase("", False, False, strConnectString)
db.Close 'Closes the database but maintains the connection
End Sub
The trick here is that the connection and authentication information will be maintained even when the database is closed.
Some database servers are capable of running multiple queries on one connection. Other servers, such as Microsoft SQL Server, are capable of processing only one query per connection. You should try to limit the number of connections required by your application. Here are some ways that you can reduce the number of connections that your application requires.
Dynasets containing more than 100 records require two connections, one to fetch the key values from the server, and the other to fetch the data associated with the first 100 records. Therefore, try to limit query results to under 100 records wherever possible.
If connections are at a premium, you should close connections that you are no longer using. This can be accomplished by moving to the last record in the result set or by running a Top 100 Percent query. Both of these techniques have dramatic negative effects on performance because all the records in the result set are fetched. Therefore, these techniques should be used only if reducing connections is more important that optimizing performance.
Finally, you might want to set a connection timeout. This means that if no action has been taken for a specified period of time, the connection will be closed. The default value for the connection timeout is 10 minutes. This value can be modified in the My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Jet\3.0\Engines\ODBC key of the Windows Registry by changing the ConnectionTimeout setting. The timeout occurs even if a form is open. Fortunately, Access automatically reestablishes the connection when it is needed.
One of the best things that you can do to optimize data handling—such as edits, inserts, and deletes—is to add a version field (timestamp) to each remote table. This version field is used when users update the data on the remote table to avoid overwrite conflicts. If this field does not exist, the server compares every field to see whether they have changed since the user first began editing the record. This is quite inefficient and is much slower than evaluating a timestamp.
The use of transactions is another way to improve performance significantly, because transactions enable multiple updates to be written as a single batch. As an added benefit, they protect your data by ensuring that everything has executed successfully before changes are committed to disk.
On the whole, the movement to client/server improves performance. If you are not careful when designing your queries, forms, and reports, the movement to client/server can actually degrade performance. There are several things that you can do to ensure that the movement to client/server is beneficial. These techniques are broken down into query techniques, form techniques, and report techniques.
Servers cannot perform many of the functions offered by the Access query builder. The functions that cannot be processed on the server are performed on the workstation. This often results in a large amount of data being sent over the network wire. This extra traffic can be eliminated if your queries are designed so that they can be processed solely by the server.
The following are examples of problem queries that cannot be performed on the server:
The following techniques can help you design forms that capitalize on the benefits of the client/server architecture. The idea is to design your forms so that they request the minimal amount of data from the server and that they obtain additional data only if requested by the user. This means that you request as few records and fields as possible from the server. This can be accomplished by basing forms on queries rather than directly on the tables. It can be further refined by designing your forms specifically with data retrieval in mind. For example, a form can initially be opened with no RecordSource. The form can require that users limit the criteria before any records are displayed.
You should store static tables, such as a state table, locally. This reduces network traffic and requests to the server. Furthermore, combo boxes and list boxes should not be based on server data. Whenever possible, the row source for combo boxes and list boxes should be based on local static tables. If this is not possible, you can use a text box in conjunction with a combo box. The Row Source of the combo box is initially left blank. The user must enter the first few characters into the text box. The Row Source of the combo box is then based on a Select statement using the characters entered into the text box.
Furthermore, OLE object and Memo fields are large and therefore significantly increase network traffic. It is best not to display the contents of these fields unless they are specifically requested by the user. This can be accomplished by setting the Visible property of OLE and memo fields to False, or by placing these fields on another page of the form. You can add a command button that enables the user to display the additional data when required.
The form shown in Figure 21.1 illustrates the implementation of several of these methods. The detail section of the form is initially not visible. The form has no RecordSource, and the data that underlies the combo box that appears on the form is stored in a local table. The After Update event of the combo box looks like this:
Private Sub cboBookType_AfterUpdate()
Me.RecordSource = "Select * From dbo_titles Where Type Like '" & _
cboBookType.Value & "*';"
Me.Detail.Visible = True
End Sub
Figure 21.1. Form using After Update of combo box to populate RecordSource and make detail visible.
The Visible property of the detail section of the form is initially set to False. When the user selects an entry from the combo box, the RecordSource of the form is set equal to a Select statement, which selects specific titles from the dbo_titles table database. The Detail section of the form is then made visible (see Figure 21.2).
Figure 21.2. Form using After Update of combo box to populate RecordSource with detail visible.
Finally, you may want to use unbound forms. This involves creating a form and then removing its RecordSource. Users are provided with a combo box that enables them to select one record. A recordset is built from the client/server data with the one row that the user selected. With this method of form design, everything needs to be coded. Your form needs to handle all adds, edits, and deletes. An example of such a form is shown in Figure 21.3. None of the controls on the form have their Control Source filled in. The name of each control corresponds with a field in the database server table. The Open event of the form looks like this:
Private Sub Form_Open(Cancel As Integer) Set mdb = CurrentDb Me.txtTitle.SetFocus End Sub
Figure 21.3. Unbound form displaying one row of data.
It sets a module level database variable to the current database and sets focus to the txtTitle text box. The After Update event of the text box looks like this:
Private Sub txtTitle_AfterUpdate()
Me!cboTitle.RowSource = "SELECT DISTINCTROW [dbo_titles].[title_id] " _
& "FROM [dbo_titles] " _
& "WHERE [dbo_titles].[title_id] Like '" & Me!txtTitle.Text & "*';"
End Sub
It sets the RowSource property of the combo box to a Select statement that selects all records from the titles table where the title_id field begins with the first few characters that the user typed. In this way, the combo box is not populated with all the titles from the server. The After Update event of the combo box looks like this:
Private Sub cboTitle_AfterUpdate()
Dim fSuccess As Boolean
Set mrst = mdb.OpenRecordset("Select * From dbo_Titles " _
& "Where Title_ID = '" & Me!cboTitle.Value & "';")
fSuccess = PopulateForm(Me, mrst)
If Not fSuccess Then
MsgBox "Record Not Found"
End If
End Sub
The OpenRecordset method is used to open a recordset based on the linked table called dbo_Titles. Notice that only the records with the matching Title_ID are retrieved. Because the Title_ID is the primary key, only one record is returned. The PopulateForm function is then called:
Function PopulateForm(frmAny As Form, rstAny As Recordset)
If rstAny.EOF Then
PopulateForm = False
Else
Dim fld As Field
For Each fld In rstAny.Fields
frmAny(fld.Name) = fld
Next fld
PopulateForm = True
End If
End Function
The PopulateForm function checks to ensure that the recordset that was passed has records. It then loops through each field on the form, matching field names with controls on the form. It sets the value of each control on the form to the value of the field in the recordset with the same name as the control name.
Note that these changes to the data within the form do not update the data on the database server. Furthermore, the form does not provide for inserts or deletes. You need to write code to issue Updates, Inserts, and Deletes, and you have to provide command buttons to give your users access to that functionality.
The employee table is probably going to be moved to a database server in the near future because it contains sensitive data. For this reason, let's design the Employee form with client/server in mind. The form limits the data displayed within the form. The form opens with only an option group containing the letters of the alphabet (see Figure 21.4). After the user selects a letter, the detail section of the form is displayed, and the RecordSource for the form is populated with a Select statement (see Figure 21.5).
Figure 21.4. Employee form with Option Group to select employee last name.
Figure 21.5. Full view of employee form.
The Open event of the form looks like this:
Private Sub Form_Open(Cancel As Integer) Me.Detail.Visible = False End Sub
The Visible property of the detail section of the form is set to False. The AfterUpdate event of the Option Group looks like this:
Private Sub optEmpName_AfterUpdate()
Me.RecordSource = "Select * from tblEmployees Where LastName Like '" _
& Chr$(Me![optEmpName].Value) & "*';"
Me.NavigationButtons = True
Me.Detail.Visible = True
DoCmd.DoMenuItem acFormBar, 7, 6, , acMenuVer70
End Sub
It populates the RecordSource of the form using a Select statement. It makes the navigation buttons and the detail section of the form visible. Finally, it resizes the window to the form.
In this chapter, you learned client/server strategies and optimization techniques that will improve the performance and maintainability of your applications. You were instructed on methods for selecting the best recordset type and how to use Key Set Fetching. You learned how to utilize pass-through queries, stored procedures, and preconnecting and connection timeouts. Finally, you learned how to optimize data handling, queries, and forms.