Problem
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.
- Check ODBC QueryTimeout setting in Registry for Jet (MS Access). By default it’s 60 seconds so it stops fetching rows at this mark.
- 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\Jet\4.0\Engines\ODBC
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\ODBC
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.