Why MS Access is missing records on ODBC import / linked table (Query Timeout issue)


Many times our customers using MS Access (i.e. Microsoft Access) ODBC data import like this one complain about less number of rows being imported and no error is reported but around 60 seconds mark it stops importing data and shows partial result in imported data or linked table.



Possible Cause

This happens because the query runs longer than 60 seconds which is default QueryTimeout setting for MS Access ODBC engine so MS Access stops fetching more rows after that default Query Timeout.

Possible Solution

The solution is simple. Change default query timeout to longer than 60 seconds like below.

  1. Check ODBC QueryTimeout setting in Registry for Jet (MS Access). By default it’s 60 seconds so it stops fetching rows at this mark.
  2. Change QueryTimeout settings to one or more following keys. Some installations might have only or two keys but not all.



Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ODBC

Now try to import data again to see if this helps.