Export to Excel but table too large - what to do?

[losamfr17]

Hi,

I have a table on Access that has 400k+ rows. I want to export it to Excel, but Access tells me that the table is to big to export, and I have to break it down into smaller tables. How do you do that or is there another way?

Please advise.

Thank you.

 

[iliace]

Not sure if there is an intrinsic Access limitation for this, but are you using Office 2007 or later? That should accommodate just over 1M rows, beginning with Excel 2007. Also, do you have a 32-bit or 64-bit version of Office?

You can also do this from Excel via macro by creating a Recordset. Here is a simple example. Let's say you have a database on you C:\ drive, called MyDatabase.mdb. In this database, you have a table called MyTable, which is your 400K+ table. This code will copy the data to range A2 of Sheet1 (leaving row 1 for column headers):

Public Sub GetAccessData()
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb"
  
  Set rs = New ADODB.Recordset
  rs.Open "MyTable", cn, adOpenDynamic, adLockReadOnly
  
  Sheet1.Range("A2").CopyFromRecordset rs

  DoEvents

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing
End Sub

 

[losamfr17]

Iliace,

I think I'm running on the 2010 office - 32 bit.
When I run the macro, I get the following error message: "User-defined type not defined." What does this mean?

Thank you.

 

[iliace]

Iliace,

I think I'm running on the 2010 office - 32 bit.
When I run the macro, I get the following error message: "User-defined type not defined." What does this mean?

Thank you.

You would need to add a Reference to this library: Microsoft ActiveX Data Objects 2.8 Library. Sorry, neglected to mention.

 

[losamfr17]

You would need to add a Reference to this library: Microsoft ActiveX Data Objects 2.8 Library. Sorry, neglected to mention.

You mean I need to download and install it, right?
https://www.microsoft.com/en-us/download/details.aspx?id=5793

Thank you!

 

[xenou]

It should be on your machine already.

 

[xenou]

This works for an accdb file, without needing to set references.

Public Sub GetAccessData()

  Dim cn As Object ' ADODB.Connection
  Dim rs As Object 'ADODB.Recordset
  
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myTemp\Database1.accdb"
  
  Set rs = CreateObject("ADODB.Recordset")
  rs.Open "Table1", cn
  
  Sheet1.Range("A2").CopyFromRecordset rs

  DoEvents

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing

 End Sub

my code is for accdb files - using the new Ace provider, the original one posted is for mdb files, which can use Ace or the Jet provider you see there. They are the same thing, but Ace is newer and updated for the accdb file format as well as still supporting the older mdb file format.

This is what is meant by setting references:
ADO and DAO Library References

 

[shanesuzanne]

Love the ADO responses you have been given; I've used similar code myself and it works great. But, as another option (dependent on your intended use of the data once its in Excel), have you considered PowerPivot? I literally have had millions of rows from Access into Excel via PowerPivot.

 

[SpillerBD]

Love the ADO responses you have been given; I've used similar code myself and it works great. But, as another option (dependent on your intended use of the data once its in Excel), have you considered PowerPivot? I literally have had millions of rows from Access into Excel via PowerPivot.

Really the PowerQuery part which is more widely available in 2016 as Get&Transform.

But just a simple complete Table: Data\From Access Database...
depending on use you may want to send it to the DataModel instead of to a Table.