One way to do this could be to run multiple queries which each produce a partial set of the data from the table. After executing the query you can export it to Excel.
In order to do this, you should design your query so that each executions return maximum of 1048576 rows. How to do this depends on the data in your table.
In general the idea could be something like
FirstKeyValue = 0
LimitKeyValue = 1048576
NameCounter = 1
while FirstKeyValue <= MaxKeyValueInTable
DoCmd.OpenQuery "QueryWhichFetchesTheKeyRange", acViewNormal, acEdit
DoCmd.OutputTo acOutputQuery, , acFormatXLS, "FilePathAndName" & NameCounter & ".xls", True
LimitKeyValue = LimitKeyValue + 1048576
FirstKeyValue = FirstKeyValue + 1048576
NameCounter = NameCounter + 1
wend
After the spreadsheets are created, you can continue to merge the files either on Access or Excel side. Probably could be easier to do this on Excel side using a single master file which contains a macro which would collect all the partial sheets to a single file.
References:
-
DoCmd.OpenQuery method (Access) | Microsoft Docs[
^]
-
DoCmd.OutputTo method (Access) | Microsoft Docs[
^]
Submit an article or tip