microsoft access system resource exceeded windows 10

Where I have that mod 500 is me trying to figure out how many lines I can copy over until I get that System resources exceeded error.

(ID, PartNumber, FamilyNumber, AutoNote, ManualNote, Created, Updated) VALUES ' And that last loop removes double spaces until everything is separated by a single space to reduce the string size as much as possible.Īnd this is the function that copies the local table to the server table Public Sub LoadUnidentifiedFromLocal()ĭim rst As New RecordsetClass: rst.OpenR 'tblUnidentifiedParts_Local'ĭim baseSQL As String: baseSQL = 'INSERT INTO. You'll notice that it connects with a special login (redacted) because I can't make changes to the server myself, but that user account can. Qdf.Connect = 'ODBC DSN=DataWarehouse Description=DataWarehouse UID=**username** PWD=**password** APP=Microsoft Office DATABASE=DataWarehouse' 'The above line is where the error happens when I debug Qdf.Connect = 'ODBC DSN=DataWarehouse_Test Description=DataWarehouse_Test UID=**username** PWD=**password** APP=Microsoft Office DATABASE=DataWarehouse_test' Here is my function that run the pass through query Public Sub RunPassThruQdf(sqlCode As String, Optional isTestDB As Boolean = False) My problem seems to come when I try to pass too long of a string of text. However, if I use a pass through query with insert, I can copy it over in about 47 seconds. The table in question currently holds about 25k lines and if I just write a query to have access copy the local table to SQL server it takes about 1 hour. Otherwise, everything is faster or fast enough when going through the pass through functions. Almost every table I have is stored in a SQL server, but I have a large table I have to loop through and it's much faster if I copy it to a local table and then loop through it. I'm trying to run Pass Through queries to put the load on the server instead of wimpy Access.

I see there are a lot of questions on this issue, but I thought I'd add to it again.

Microsoft access system resource exceeded windows 10