<div>
instead of using TransferSpreadsheet, use automation and CopyFromRecordset. &nbsp;Here is code you can modify: 
<pre><code class="code-100 nolinks" id="code-20-41455576-1">Sub CopyFromRecordset_example( _
 pPath As String, pQname As String)
 
 On Error GoTo Proc_Err
 
 ' originally posted by NateO
 ' modified by crystal
 
 ' NEEDS REFERENCE 
 ' Microsoft ActiveX Data Objects Library

 'Declare your ADO Recordset
 Dim rs As ADODB.Recordset
 
 'Excel Objects
' Dim xlApp As Excel.Application 'early binding for dveloping
' Dim xlWb As Excel.Workbook
 Dim xlApp As Object 'late binding for distribution
 Dim xlWb As Object
 
 'Field Names - Stack into Array
 Dim fldArr() As String
 
 'Need some loop counters
 Dim j As Long _
 , i As Long
 
 Dim sFilename As String
 sFilename = pPath &amp; pQname
 
 i = 1
 
 'OLE - Create xl Objects
 Set xlApp = New Excel.Application
 
 'Add a new Workbook, with one Worksheet, to our Excel Application
 Set xlWb = xlApp.Workbooks.Add(1)
 
 'this is commented out because to show you can loop if you want
 'For i = LBound(sqlArr) To UBound(sqlArr)
 
 'New ADO Recordset
 Set rs = New ADODB.Recordset
 
 'Open the Recordset, Passing the Sql from our Array
 rs.Open CurrentDb.QueryDefs(pQname).SQL, CodeProject.Connection, _
 adOpenStatic, adLockReadOnly
 
 With rs
 'Stack a String Array with the Field Names
 ReDim fldArr(0 To .Fields.Count - 1)
 
 For j = LBound(fldArr) To UBound(fldArr)
 Let fldArr(j) = .Fields(j).Name
 Next j
 
 'Time to Pass some Data to Excel!
 With xlWb.Worksheets

 'Add a Worksheet if we're at 2nd Recordset or Greater -- when looping
 'If i &gt; 1 Then .Add After:=.Item(i - 1)

 'Refer to the Worksheet by Item Number
 'in the Collection of Worksheets (1-based)

 With .Item(1)

 'Pass our dynamic Field String Array to A1,
 'stretched to the Right for number of Elements
 Let .Range(&quot;a1&quot;).Resize(, UBound(fldArr) + 1).Value = fldArr
 
 'Copy our Current Recordset to A2
 .Range(&quot;a2&quot;).CopyFromRecordset rs
 
 'Rename Individual Worksheet
 .Name = &quot;WorksheetName&quot;
 
 'however many columns of data you have, if desired
 .Columns(&quot;A:G&quot;).EntireColumn.AutoFit
 
 End With
 End With
 End With
 
 'Moving on, no need to close or terminate our RS, yet,
 ' we're going to recycle in the Loop
 
 'end of optional loop
 'Next
 
 'Make Excel visible - (Otherwise Save and Close)
 With xlApp
 .Goto xlWb.Worksheets(1).Range(&quot;A1&quot;)
 'commented but can be activated if desired
 'xlSht.Cells(1, 1).Activate
 '.Visible = True
 End With
 
Save_Workbook:
 
 'delete file if it already exists
 If Dir(sFilename) &lt;&gt; &quot;&quot; Then
 On Error Resume Next
 Kill sFilename
 DoEvents
 On Error GoTo Proc_Err
 End If
 
 'commented because there was not code to disable in this case
 'if you are writing to a template with code, you may want
 'to disable events in the beginning
 'xlApp.EnableEvents True
 
 xlApp.ActiveWorkbook.SaveAs sFilename
 xlApp.ActiveWorkbook.Close False

Proc_Exit:
 On Error Resume Next
 
 'Terminate our Excel Object Variables
 Set xlWb = Nothing
 If TypeName(xlApp) &lt;&gt; &quot;Nothing&quot; Then
 xlApp.Quit
 Set xlApp = Nothing
 End If
 
 'Now close and terminate the ADO Recordset, we're all done!!
 rs.Close: Set rs = Nothing

 Exit Sub
 
Proc_Err:
 MsgBox Err.Description, , &quot;ERROR &quot; &amp; Err.Number &amp; &quot; CopyFromRecordset_example&quot;
 Resume Proc_Exit
 'press Ctrl-Break to stop code at Msgbox
 'set this to be the next statement then F8 to step through code and debug
 Resume

End Sub
</code></pre>
</div>

<div>
<blockquote>
the data size has grown over the last couple of years.
</blockquote>
Grown in Rows or Columns? 
Access still has a column limit of 255. 
 
Also note that you are specifying the old Excel *97* format. 
Try specifying the actual Excel format you are saving your files in. 
 
How much memory do you have &quot;available&quot;? 
 
Finally, try increasing the MaxLocksPerFile setting in the registry. 
I think the default in Win 7 is: 9500 
So try increasing it to 10000, ... 
Then increase it in increments of 1000, ...until the issue goes away. 
 
JeffCoachman
</div>

SwitchboardItemsExcelError.JPG

mlcktmguy

<div>
<div class="content wysiwyg-content">
I have an Access 2003 app that has been running in production for a couple years. &nbsp;In that period, the statement I am getting this error on has run many, many times. &nbsp;However, the last couple of months the users are getting the 3035 error. 
 
There have been no application changes but the data size has grown over the last couple of years. 
 
The statement that is throwing the error is: 

<pre><code class="code-1 nolinks" id="code-8293541">'
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, &quot;qryRpt_InstallPlan_Letters_Export_2&quot;, wkExportNameAndLoc, True
</code></pre>
 
There are a couple process that run prior to this statement pulling together the data that is needed on the export but they always run successfully. 
 
Any ideas what can be done to eliminate this issue?
</div>
</div>

I have an Access 2003 app that has been running in production for a couple years.  In that period, the statement I am getting this error on has run many, many times.  However, the last couple of months the users are getting the 3035 error.

There have been no application changes but the data size has grown over the last couple of years.

The statement that is throwing the error is:


There are a couple process that run prior to this statement pulling together the data that is needed on the export but they always run successfully.

Any ideas what can be done to eliminate this issue?

TransferSpreadsheet generates 3035, System Resources Exceed

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.