The Jet database engine is capable of retrieving data from databases of any size. However, as the number of records processed increases, be aware of the increased amount of required TEMP storage space. Any design that opens tables directly, without benefit of a SQL query that limits scope, should be reconsidered. Jet will generate a trappable error if local disk space is exhausted while Jet is building a keyset. This is not really a limitation in Jet but of system resources, and it is characteristic of poor application design. Any application design that requires the database engine to create a physical pointer to each row of the result set data, such as when a keyset is created, has a theoretical upper limit set by the capacity of the media where the keyset is stored.
In some cursor models, only a subset of the keyset is maintained on the client machine, or the keysets are built on the server. Although Jet supports a table-type recordset that permits browsing tables without impacting client resources, this is not available when accessing remote (ODBC) data. Instead, only the dynaset-type and snapshot-type Recordset objects are supported.
Both of these build the keyset on the client system, overflowing to TEMP space on disk if necessary. The snapshot-type recordset also downloads data, which may further limit the size of the recordset that can be built. In any case, your data access strategy should involve restraining the result set scope. That is, you should limit the number of rows returned by the query.
For More Information See "Choosing a DAO Query Processor for Use with Jet" later in this chapter.