Let me preface this by saying yes, I do need all of the records. It's part of a FOIL request. Not necessarily in one worksheet or file.
I'm having trouble figuring this out. I am currently able to export about 500k records at a time without timing out or exceeding access file size(I think this is due to working with state systems legacy data) or worrying about excel row limit. I can preserve column headers but lose leading zeros in one field.
This is done using the export wizard to text file. I change the destination file name ending from .txt to .csv and that gives me the option to keep headers. On the preview of the .csv file in the wizard and when opened in notepad it shows the field with leading zeros correctly with double quotes around it, for example "00123456" but then when opened in excel it shows as 123456. If I update the row from General format to Text the contents remain the same.
I have tried VBA method of DoCmd.TransferSpreadsheet but when I try to run it I am prompted with a Macros box. And honestly am less familiar with VBA than in am SQL. Overall I consider myself a novice.