DoCmd.TransferSpreadsheet acExport - loses part of dataset

[walrus]

Hello

:banghead:

I am exporting a query from access to excel (both 2003), then creating a pivot table in excel from access. This all works fine.

I use the same query with different criteria to produce different reports, then output them to excel, create the pivot table, close excel, rinse & repeat. This is working fine for all my reports except one. All the queries produce between 15 - 40k rows in excel. The results of one particular query, with about 20k rows, is getting the last 5k rows 'chopped off' when it gets to excel, so the pivot table is missing some data.

I have tried running the query manually and it returns the correct results and if I use Tools>Office Links>Analyse it exports correctly.
Stepping through my code it is working correctly to the point it has created the query in Access, but as soon as the query is exported to excel (before I start creating the pivot table) the exported dataset is missing records. In the code below, it works fine up to 'exports sheet to excel

        qdf.SQL = strSQL
 
        'checks if the query is empty - if it is, skips it
            If DCount("tblCombiSS.UAID", "qryMultiSelect", "") = 0 Then GoTo exitY2
 
'exports sheet to excel
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, "qryMultiSelect", strFullPath, True
        Set appExcel = New Excel.Application
        Set wbkOutput = appExcel.Workbooks.Open(strFullPath)
'change to see excel build the data
        appExcel.Visible = True
'create Pivot Table
        Call CreatePivotTable(strCriteria, appExcel, strFullPath, strFileName, strRepCode)

but if I stop it at 'create Pivot Table to look at the exported sheet I can see it is missing the data.

I do Dim properly & set the queryDef to 'nothing' between reports and as I said, it works fine most of the time! Any ideas please?

 

[walrus]

Fixed it! It was the old access-only-reads-the-first-8-rows-of-the-query-before-setting-the-field-type-for-the-excel-sheet chestnut. I added a sort order to one of my query fields so the field type was recognised as alphanumeric (it was just picking up the numeric) and Bobs a man you auntie knows very well.