I have several MS Access97 database tables that are larger than 120 MB. When I use ADO to open the entire table it seems to try to load the table into memory, this crashes the machine it is running on. Is there anyway to limit the amount of memory used by ADO?
If you don't need all the data back at the same time, you might want to try using the PageSize/PageCount properties of a static recordset. If you open your recordset as adOpenStatic, you can set the PageSize property to be the number of records you want back at a time.
The PageCount property will then be populated with the number of pages (full or partial) that are in the recordset. You can then use the AbsolutePage property to jump immediately to a particular page. This method is much more efficient when it comes to large recordsets and large databases.