Import Large Access File into Excel

[dgr]

Hi,
I'm using Office 2007. I've got an Access table which contains approximately 3 million records. I would like Excel to import this Access table into multiple workbooks of 1 million records each. I'm looking for a VBA macro for to either export from Access or a VBA macro to import to Excel. The end result I want to achieve is to have 3 Excel files with 1 million records each.

Could you help me with the code?

Thanks.

 

[Jon von der Heyden]

Wow that's a lot of data! What do you intend on doing with the data once dumped in Excel?

In fact you don't need VBA at all. You could use Data > Get External Data > From Access; which can allow you to maintain a connection to the DB and even manage the query definition (i.e. the import rules).

 

[dgr]

Wow that's a lot of data! What do you intend on doing with the data once dumped in Excel?

In fact you don't need VBA at all. You could use Data > Get External Data > From Access; which can allow you to maintain a connection to the DB and even manage the query definition (i.e. the import rules).

I've already tried this & it didn't work because the Access table has got 3 million records & Excel 2007 can only import 1 million+ records. Thanks.

 

[Jon von der Heyden]

Can you answer this q?

What do you intend on doing with the data once dumped in Excel?

 

[dgr]

Can you answer this q?

At the moment, the Access table is so big it takes up to 10 minutes just to open it. Therefore I'm toying with the idea of either:
Option 1: splitting the table (I don't know how to do this) so that it is easily manageable or
Option 2: exporting the table to a few Excel files so that it is easily manageable

Frankly speaking I prefer Option 1 but I think I asked the question in the Excel forum instead of the Access forum.

My Access table has got the following fields called Name, Phone, NRIC and Address1. There is no unique ID field.

If you could show me a VBA code to split the table, I would be happy.

Thanks.

 

[Jon von der Heyden]

I was more thinking along the lines of how you want to aggregate / analyse the 3m records. I really don't think Excel is the right tool to drop this sort of volume of records to. I tend to rather build interfaces that allow me to query the DB and either extract portions of the data or aggregated results.

I may sometimes resort to ADO. The code can be involved so I have written a tutorial which you can read here. It will take some reading but it does expose a number of options. You can drop all 3m records to a single sheet using ADO, or you can use parameters to query only portions of the data on an as-need basis. There are also various links at the bottom of the article that you can read to further your knowledge of ADO.

By the way - I highly recommend that you include a unique ID for each record. Besides good practice, you can create select statements e.g.:

select * from tbl where tbl.id >=1 and tbl.id <1000000;

and then

select * from tbl where tbl.id >1000000 and tbl.id <2000000;