How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Tech_Ben Your Own Question

Tech_Ben, Computer Software Engineer
Category: Programming
Satisfied Customers: 1773
Experience:  10 years professional experience in Java, C++, Visual Basic, .NET, DB2, Oracle, SQL Server
Type Your Programming Question Here...
Tech_Ben is online now
A new question is answered every 9 seconds

I have a Access database of 1 million records that I need to

This answer was rated:

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)

Thank 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 folder
Open table
Select first 25,000 rows
Click File -> Export
Choose XLS (Excel 97 - 2003) from the dropdown where it says "Save as Type"
Enter your file name
Click 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.
Customer: replied 7 years ago.
Opps. I meant a solution that was not manual :)
For example, is there a way to run a wizard or table in access that only shows 1 to 25,000. Then, highlight all, copy or export.
Then repeat - 25,0001 to 50,000 etc
Sure seems there should be a way to only "show" a specific range?
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,
Customer: replied 7 years ago.
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.
Tech_Ben and other Programming Specialists are ready to help you

Related Programming Questions