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

SachAccess

Active member
Local time
Today, 14:13
Joined
Nov 22, 2021
Messages
389
Hi,

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

Code:
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
    CurrentDb.QueryDefs.Refresh
    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
        CurrentDb.QueryDefs.Refresh
        Set qdef = CurrentDb.QueryDefs("Chunk" & i)
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdef.Name, "D:\MMM\Personal Documents\MyAccess\" & qdef.Name & ".xlsx"
    Next i
End Sub
 

Minty

AWF VIP
Local time
Today, 08:43
Joined
Jul 26, 2013
Messages
10,353
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.
 

SachAccess

Active member
Local time
Today, 14:13
Joined
Nov 22, 2021
Messages
389
Hi @Minty thanks a lot for the help. :)
 

Users who are viewing this thread

Top Bottom