Append Query Run Time Error 3035 (1 Viewer)

Hello,

I have built an import button on a form which when clicked, transfers data from an excel spreadsheet (through file dialog picker) to a temp table. It then uses an append query to append the new data to a sql server linked table.

It is working well with small volumes of data. However, beyond 40 000 rows, it comes up with this error message:

Run-Time Error '3035': System resource exceeded

How do I solve this?

Appending from a Recordset will do the trick although much slower.
After each append, make sure to issue

Dbengine.idle dbrefreshcache

So that record added will forcefully commit to the server thus releasing some resources.

Search this forum for "Resource Exceeded" posts - we have had several in the last year. You have relatively few posts so might not know this. The search function for AWF is in the thin blue ribbon near the top of the page, just under the little boxes that identify you and tell you where you are in the forum's response tree. "Search" is 3rd from the right on that ribbon.

I have similar systems where far more rows than this are imported from CSV files into temp tables and then appended to SQL server tables without issues.

I just checked and several of these have approx 140,000 rows. There are no issues. The destination table has almost 1.5 million records
Furthermore I import 30 CSV files each night of which up to 10 will be that sort of size

I changed to CSV files several years ago to fix issues with importing data
Basically there used to be a limit of 65536 rows prior to Excel 2007 but its over a million now.
See https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx

As an experiment I just saved the CSV files as an XLSX files & linked that to the database
The XLSX file is half the size but took MUCH longer to open than the linked CSV file. There was a noticeable delay with XLSX but CSV opened instantly

So one suggestion is to use CSV files instead