MS Access and Error 3035 "System resources exceeded."

Question

I see there are a lot of questions on this issue, but I thought I'd add to it again. I'm trying to run Pass Through queries to put the load on the server instead of wimpy Access. 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. Otherwise, everything is faster or fast enough when going through the pass through functions.

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. However, if I use a pass through query with insert, I can copy it over in about 47 seconds. My problem seems to come when I try to pass too long of a string of text.

Here is my function that run the pass through query

Public Sub RunPassThruQdf(sqlCode As String, Optional isTestDB As Boolean = False)

    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("vbaSQL")
    If isTestDB Then
        qdf.Connect = "ODBC;DSN=DataWarehouse_Test;Description=DataWarehouse_Test;UID=**username**;PWD=**password**;APP=Microsoft Office;DATABASE=DataWarehouse_test"
        'The above line is where the error happens when I debug
    Else
        qdf.Connect = "ODBC;DSN=DataWarehouse;Description=DataWarehouse;UID=**username**;PWD=**password**;APP=Microsoft Office;DATABASE=DataWarehouse"
    End If
    qdf.ReturnsRecords = False

    qdf.sql = sqlCode
    Do Until InStr(qdf.sql, "  ") = 0
        qdf.sql = Replace(qdf.sql, "  ", " ")
    Loop
    qdf.Execute
    qdf.Close

End Sub

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. And that last loop removes double spaces until everything is separated by a single space to reduce the string size as much as possible.

And this is the function that copies the local table to the server table

Public Sub LoadUnidentifiedFromLocal()
    
    Dim rst As New RecordsetClass: rst.OpenR "tblUnidentifiedParts_Local"
    Dim dtm As Date: dtm = Now
    
    Dim baseSQL As String: baseSQL = "INSERT INTO [DataWarehouse_test].[dbo].[mT_SalesAttributeDB_UnidentifiedParts] (ID, PartNumber, FamilyNumber, AutoNote, ManualNote, Created, Updated) VALUES "
    Dim sql As String
    
    RunPassThruQdf "SET IDENTITY_INSERT [DataWarehouse_test].[dbo].[mT_SalesAttributeDB_UnidentifiedParts] ON", True
    
    Dim i As Integer: i = 1
    Do Until rst.EOF
        If sql = vbNullString Then sql = baseSQL
        If i Mod 500 = 0 Then
            RunPassThruQdf sql, True
            'Debug.Print i & ": " & Format(Now - dtm, "hh:mm:ss") & "   (" & Format(Len(sql), "#,##0") & ")"
            
            'DoEvents
            sql = baseSQL
        End If
        
        Dim addSQL As String: addSQL = "(" & rst.Fields("ID") & ", '" & rst.Fields("PartNumber") & "', '" & rst.Fields("FamilyNumber") & "', '" & rst.Fields("AutoNote") & "', '" & rst.Fields("ManualNote") & "', '" & rst.Fields("Created") & "', '" & rst.Fields("Updated") & "')"
        If sql = baseSQL Then
            sql = sql & addSQL
        Else
            sql = sql & ", " & addSQL
        End If
        
        rst.MoveNext
        i = i + 1
    Loop
    
    If sql <> vbNullString Then
        RunPassThruQdf sql, True
        Debug.Print i & " " & Format(Now - dtm, "hh:mm:ss")
    End If
    
    RunPassThruQdf "SET IDENTITY_INSERT [DataWarehouse_test].[dbo].[mT_SalesAttributeDB_UnidentifiedParts] OFF", True
    
    rst.CloseR

End Sub

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. You'll probably also notice I have a special rst class I made. But just know that it mimics the normal one, I just have some special functions in there so it was my version of inheritance since VBA doesn't support that.

The first time I got it I googled and found that someone was able to fix their error by change the max lock to 1 million. And that worked for me too, until I exceeded it. DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000

I know it's not a problem with my computer, it's 6 core Xeon W-10855M with 64GB of ram. But I will see that error even after restart for a while and then it will just stop and work again until I try to send it too much and then I'm stuck seeing it for a bit. What is weird is even restarting my computer will not fix the issue. And I've tried compress and repair and that won't fix it either. So I'm not clear what changes so that it stops reporting that.

But my first question is if increasing the max locks per file helped, is there a way to clear what locks are currently there? My second question would be how big of a string can I send with a pass through, I think I saw somewhere in the neighborhood of 65k before I got the message (That was when I had it set at i mod 500.