Export to CSV (1 Viewer)

[tt1611]

Good day all
I am trying to export a given table TEMP to a CSV. The docmd.transfertext works great but the problem I have is that I am trying to open a recordset to count the total records in the table TEMP. Export up to 20k records to one csv after which create a new file and export another 20k records. Each csv only holds a total 20k rows

My code so far is below

Do Until rst2.RecordCount <= 20000
DoCmd.TransferText acExportDelim, , "temp", "[URL="file://\\someserver\9100\AccessDBs\StoreReporting\Sales\fw"]\\someserver\9100\AccessDBs\StoreReporting\Sales\fw[/URL]_" & a & "sales_metrics.csv"
Loop

The above only works for one files and the loop exits after (naturally). Deos anyone has an idea on creating multiple files in the above path each with 20k records from the record set and then go on to create a new file with the same logic.

Thanks in advance