After you've gotten your application working, you can begin optimizing. There are several ways to optimize your application, many of which are described in the following paragraphs. However, the optimization you choose will depend on your computer configuration and the application you're using.
If your application updates data frequently or performs calculations on sections of large data sets, optimizing the queries that perform these actions will significantly improve the speed of your application. Small adjustments in the body of the query or in the SQL statement may be all you need to improve your application. For information about optimizing queries, see "Queries" in Help.
This method cannot be used with ODBC data sources because they cannot, by design, be opened directly as tables.
When using remote data sources, Jet provides two types of Recordset objects: Dynaset objects and Snapshot objects. If you don't need to update data in your result set, and if the result set contains fewer than 500 records, you can optimize your application by returning a Snapshot object. Snapshot objects are read-only optionally forward-only scrolling result sets; a Snapshot object is generated faster than a Dynaset object because it represents a picture of your data set.
If you directly open a table that contains an index on the field you want to search, using the Seek method will always be the fastest way to locate a record. However, the Seek method can be used only on directly opened tables.
The Find method isn't as fast as the Seek method because of the overhead associated with dynasets or snapshots — specifically,copying matching records from a table and maintaining the indexes.
Using stored queries greatly increases processing speed — especially low-memory computers — because Jet's query compiler isn't needed. If you call a SQL text string, the query compiler is called; Jet then binds references, builds a query tree, chooses an optimization method, and then runs the query.
If the values of a query aren't known before compile time, or if the application depends on user-supplied values for the query, it may not be possible to use stored static queries. In this case, using a parameter query is still faster than using a SQL text string because a parameter query is also a stored compiled query. For information about building a parameter query, see "parameter queries" in Help.
The BeginTrans method tells Jet to buffer all changes to the Recordset object. The changes will be written to the data set when the EndTrans method is applied. Without transaction processing, Jet writes changes directly to the Recordset object each time a change occurs, which may dramatically increase disk access. However, if the activity within the transaction is too large, changes to the Recordset object will be stored both in memory and in a swapfile, which increases disk access. The optimal number of records to store in a transaction depends on the computer configuration; you may need to experiment with the record numbers.
Using a bookmark instead of the Find method dramatically increases performance because a bookmark is essentially a stored primary key that Jet uses to return to the previous record location. In contrast, the Find method searches the entire data set for the previous record.
The CopyFromRecordset method optimizes your application by copying large chunks of records as a unit instead of copying individual records. Variables for indexing and looping aren't needed in the result set or on the worksheet.
Whereas the CopyFromRecordset method copies and pastes an entire result set into a Microsoft Excel range, the GetRows method returns only a specified number of records from the result set (starting with the first record).
The following example pastes the first six records from the result set created by the OpenRecordset method into cells A1:B6 on Sheet1.
Dim db As Database, rs As Recordset
Dim data As Variant
Set db = OpenDatabase("c:\access\sampapps\Nwind.mdb")
Set rs = db.OpenRecordset("Select Customer.[City] from Customer;")
Do While Not rs.EOF
data = rs.GetRows(6)
Loop
Sheets("Sheet1").Activate
For Each i In ActiveSheet.Range("A1:B6") _
i.Value = data(i.Column - 1, i.Row - 1)
Next
rs.Close
db.Close
End Sub