I have a Access database of 1 million records that I need to export to Excel/Text format in smaller files/chucks of about 25K each. What is the best/fastest way to do this using Access? (note: I also have the same 1 million file in Excel too)
HiCustomerThank you for the question. In order to do this, you can use MS Access "export" function. The easiest way to do this would be to select your range (25,000 at a time), and click export. There will be an arrow next to the export button that allows you to export the selected range. You will have to do this 40 times in order to get all 1,000,000 records, however it will not take too long. You could also write a VBA script to loop through the range in the table 25,000 records at a time until the end of the table is reached, however it will take longer to write the script than it would to do this manually. The manual method would consist of the following:Back up your database by saving the MDB file to another folderOpen tableSelect first 25,000 rowsClick File -> ExportChoose XLS (Excel 97 - 2003) from the dropdown where it says "Save as Type"Enter your file nameClick the checkbox that says "Save Formatted"Click the small arrow next to "Export All", and click "Save Selection", which will save only the records you have selected.Select next 25,000 rows in table, and repeat until you are finished.Hope this helps!Please accept my answer if you feel my assistance was helpful.
Thanks for the reply. Unfortunately, there is nothing like this built in to MS Access, mainly because it is a fairly low priority use-case. MS Access provides an interface to the Microsoft API's, so you could use MS Access API code (VBA) to perform this operation if you are so inclined. Just like MS Word or Excel, you can select a range of records from an MS Excel table. Using VBA programming appears to be the only way to do this. Hope this helps,
Hmmm. You said "range of records" using excel. So I can do it using Excel instead?
Yes, you can do the same in Excel however will likely still need to do VBA scripting in order to get these out of the single file. I searched for some tools to split excel files, however did not see anything useful.
Years of professional experience in C++, Visual Basic, .NET, Java, DB2, Oracle, SQL Server, PL/SQL