'Item not found in this collection' while exporting table data to Excel (1 Viewer)


Local time
Today, 23:01
Nov 22, 2021

Am trying to use below code in my file. I am trying to split the data for every 50,000 records.
I am getting bug at 'Set qdef = CurrentDb.QueryDefs("Chunk" & i)' this line.
It says, Item not found in this collection.

numChunks is showing as 32
maxnum is correct
File is present, path is correct.

When macro enters the loop for first time, before throwing bug, it has actually modified the excel file.
One worksheet is created in the file with name as 'Chunk', 50,001 records are pasted here.
However not able to proceed.. Am not able to understand this.
Can anyone please help me in this.

Copied from below URL

Sub ExportChunks()
    Dim rs As Recordset
    Dim ssql As String
    Dim maxnum As Long
    Dim numChunks As Integer
    Dim qdef As QueryDef
    ssql = "SELECT COUNT(Id) FROM NewTable"
    Set rs = CurrentDb.OpenRecordset(ssql)
    maxnum = rs.Fields(0).Value  'total number of records
    'add 0.5 so you always round up:
    numChunks = Round((maxnum / 50000) + 0.5, 0)
    'On Error Resume Next 'don't break if Chunk_1 not yet in QueryDefs
    ssql = "SELECT TOP 50000 * FROM NewTable"
    CurrentDb.QueryDefs.Delete "Chunk"
    Set qdef = New QueryDef
    qdef.SQL = ssql
    qdef.Name = "Chunk"
    CurrentDb.QueryDefs.Append qdef
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Chunk", "D:\MMM\Personal Documents\MyAccess\Chunk.xlsx"

    For i = 2 To numChunks
        ssql = "SELECT TOP 50000 * FROM NewTable WHERE ID NOT IN (SELECT TOP " & (i - 1) * 50000 & " ID FROM NewTable)"
        Set qdef = CurrentDb.QueryDefs("Chunk")
        qdef.SQL = ssql
        Set qdef = CurrentDb.QueryDefs("Chunk" & i)
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdef.Name, "D:\MMM\Personal Documents\MyAccess\" & qdef.Name & ".xlsx"
    Next i
End Sub


Local time
Today, 18:31
Jul 26, 2013
That seems very inefficient to me - to select the top 50000 records to populate an IN clause in the sub query.

If your table has a unique ID Field make your initial select with that as an order ASC on it, then select the top 50000, store the max ID from that ordered list and simply select the next 50000 with an ID above that, rinse and repeat.
The id field doesn't need to be sequential just unique. The sorting will take care of the rest.

Users who are viewing this thread

Top Bottom