Read large sets quickly from linked tables

Code that runs through a linked table from start to end – an export, a validation loop, a report in VBA – is considerably slower through the driver if it opens a dynaset. With a snapshot, it reads the same rows in a fraction of the time.

Access (ACE) reads a linked table in two ways, through this driver just as through the SQL Server ODBC driver:

• Snapshot (dbOpenSnapshot; reports, combo boxes): one statement; the driver fetches the rows page by page.
• Dynaset (dbOpenDynaset; forms, datasheets): first only the keys, then each displayed row individually with … WHERE "ID" = ?. Through the driver, this becomes one request to the service per row.

A form fetches only the rows it shows; that isn't noticeable. In a loop over all rows, each row costs a request of its own through the driver – with the fixed price of a request over HTTP. A snapshot avoids it.

1. Open recordsets that you only read as snapshots:

   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("SELECT Nummer, Bezeichnung FROM Artikel WHERE Gruppe = 'S'", dbOpenSnapshot)
   Do Until rs.EOF
       ' … process …
       rs.MoveNext
   Loop
   rs.Close

2. Limit columns and rows in the statement: the condition and the column list go to the service, which returns only what it finds.
3. Read with MoveNext, not with GetRows; see the remarks.

Result: A snapshot reads all rows with a few requests; a dynaset needs one for every row it visits. Over a network, its round-trip time is added per request; for a dynaset, that means per row.

• GetRows can hang on an error while reading further – with any ODBC driver; see Access hangs at GetRows.
• Joins of two linked tables are sent by Access as one statement; without an address for own statements, they fail, see "ODBC--call failed" for totals or joins.
• Compression saves the most over slow lines; the driver decompresses on its own when the service compresses.

• Supported SQL – what goes to the service as a condition
• How the driver translates statements