Using Top Values to export a range of records


Well-known Member
I have a very large table of 3 million records that I need to export out of ACCDB in chunks of 500k records at a time.

They are sorted in a particular order so their Primary IDs are not sequential.

I can easily export out the first 500k by setting the Top Values property to 500,000. Is there a way to then set the range to 500,001 to 1,000,000?




MrExcel MVP, Moderator
Not tested (i rarely use top in Access) but should work like this

select top (500000) * from Table where SomeID < 500000 order by SomeID
select top (500000) * from Table where SomeID >= 500000 and SomeID < 1000000 order by SomeID
select top (500000) * from Table where SomeID >= 1000000 and SomeID < 15000000 order by SomeID

If the IDs are not really sequential then you can't be sure of getting exactly 500,000 per chunk.
If you need to get exactly 500,000 per chunk then you have to store the ID's of what is exported so you can start the next batch from the same place.

Probably not ideal. Best to test your algorithm on a small table with say 10 chunks of 10, with only 100 records (more or less).

Another possibility - export the relevant IDs into a temporary-ish table, partition them into the right number of groups, and use that table to control the exports. This would provide a better audit and probably a better way of ensuring reliability (which can also be helped by recording what is exporting and validating the counts).

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...