How to: Show large tables in server mode

Show a linked table with a million records in a grid that opens in about a second and scrolls, filters, sorts, and groups without loading the records.

Requires WKL Forms Object Library 1.23 or later.

With DataMode = wgdServer, the grid reads only the records it shows; the record count, filters, groups, totals, value lists, and search all become statements against the record source. Writes still go through Access.

The grid is only fast when the source allows it. Prerequisites:

• A linked ODBC table as the RecordSource: SQL Server through ODBC Driver 18 (other drivers aren't supported), or a data service through the Wkl OData driver. The grid then reads over its own connection, bypassing the Access database engine.
• A saved login in the link: either Windows authentication or the password saved with the link (Save password when you link the table). Without one, the grid can't open its own connection.
• KeyField set to the table's primary key, so the link has a unique index in Access.
• Indexes on the server for the columns users sort, filter, and group by; server mode leaves that work to the server.
• With the Wkl OData driver: the data service must allow pass-through statements (SQL statements sent by the caller) for both the data source and the caller. The data service administrator turns on pass-through for the data source and, for the caller, either adds a rule with the sql resource (a service key, or a Windows account with Persons = Service) or grants a rule that isn't read-only (a separate SQL login, or Persons = Impersonate). These steps are part of configuring the data service, not the grid. Without pass-through, the grid falls back to reading through Access (see Remarks).

With Me!Grid1.Object
    .RecordSource = "tblSales"          ' linked table, 1,000,000 records
    .KeyField = "ID"
    .DataMode = wgdServer
End With

Or turn on Server mode on the designer's Record source page. The setting is saved with the form.

• Falls back instead of failing. Local tables, queries, SQL statements, a set Filter, links without a saved login, and Wkl OData links without pass-through are read through Access, with the keys fetched in chunks of 10,000. Everything still works; distant positions are slower, and totals the source rejects stay empty (troubleshooting).
• Differences from memory mode: Filter must be an Access SQL condition (no JPath); clicking a column header queries the source again; groups are sorted by value; text conditions compare the stored values; an edited record keeps its position until the next Requery; and Matches only in the search bar doesn't filter.
• What the grid sends. Over its own connection (the link's connection string), the grid sends SQL in the ODBC dialect to the object the link points to (the link's SourceTableName, either a table or a view), never to the tables behind a view: COUNT, the page with OFFSET ... FETCH or a ranking, GROUP BY for groups and totals, and DISTINCT for value lists. Linking a view therefore keeps every row filter in the view, including one on the signed-in user; linking the table instead bypasses such a filter. Link views rather than tables when the server restricts rows per user. Whether the Wkl OData driver translates a statement or passes it on to the data service is up to the driver; see its documentation. Writes always go through Access and its link.
• Always in memory: a grid bound with Recordset, or one whose MasterFilterMode filters in memory, loads every record regardless of the setting.
• Export: the export reads every exported record, so filter large sources first. A workbook holds at most 1,048,574 rows.
• How fast: a table with one million records on SQL Server opens in about a second, through both ODBC Driver 18 and the Wkl OData driver. Actual times depend on the server and its indexes.

• API reference: DataMode
• Totals stay empty in server mode