Query results exceed "clipboard" limit of 64K records

[Matter]

Hi All,

I'm managing a very large database with around 15 years of data in it. The primary purpose of the database is to organize and archive scientific data so it can be exported for use in higher-level modeling programs. These programs do not often have the ability to query data directly from the database, so users must export the data to excel or csv - however it seems that I have maxed out the export function in Access:

One of the export queries that is used bi-weekly is in excess of 85k records, and upon attempting to export this to excel Access reports that the clipboard can only handle the first 64k records or so.

Is there a way around this? I've been using the "export as excel" function through a marco, but perhaps there is a better, more efficient way of doing this through VB?

Any ideas are appreciated. Thanks for the tips!

 

[KenHigg]

I think you may be hitting an 'Excel' limit of 64k rows.

 

[Matter]

I just tested again, exporting to text/csv and got the same error:

"You selected more records than can be copied to the clipboard at once
Divide the records into two or more groups and then copy / paste [...] the maximum number is approximately 65,000"

This is trying to export 84,000 records - is there any way to bypass the clipboard? Or perhaps export records 1-60,000, and then append the rest onto the end of the file through VBA?

 

[DCrake]

Whats happening also is that it cannot paste 64k+records into one tab you need to get it to rollover onto the next worksheet when full. I do have code for this but it is in my other office.

 

[DCrake]

Whats happening also is that it cannot paste 64k+records into one tab you need to get it to rollover onto the next worksheet when full. I do have code for this but it is in my other office.

In Excel 2007, the worksheet size is 16,384 columns by 1,048,576 rows, but the worksheet size of earlier versions of Excel is only 256 columns by 65,536 rows. Data in cells outside of this column and row limit is lost in earlier versions of Excel.

 

[DCrake]

You may have to go down the VBA route using the CopyFromRecordset technique. Are you familiar with this concept?

 

[Matter]

In Excel 2007, the worksheet size is 16,384 columns by 1,048,576 rows, but the worksheet size of earlier versions of Excel is only 256 columns by 65,536 rows. Data in cells outside of this column and row limit is lost in earlier versions of Excel.

We're using Office 2k7 here, so no problems with Excel/CSV and the 84k records. I have previously exported as much as it would do and then copy and paste the remainder of the records to the bottom they all end up in 1 worksheet just fine. This works, however, I do not expect all of the users to know to do this. Code is appreciated! Cheers,

 

[Matter]

You may have to go down the VBA route using the CopyFromRecordset technique. Are you familiar with this concept?

I just read up on the method, however I am unclear what you have in mind - do you mean to daisy chain two of these together to copy and export everything? I don't see where to specify the starting row to select for copying... Perhaps I misunderstand the documentation. Thanks!

 

[DCrake]

Sample

Public Function ExportToPreformattedExcelworkbook()
    'Declare Objects
    Dim oExcel As Object
    Dim obook As Object
    Dim oSheet As Object

    Dim Rs As DAO.Recordset
    Set Rs = CurrentDb.OpenRecordset("[B]YourQuery/TableNameHere[/B]")

    'Test for previous copy of file
    If Dir("C:\Temp\Recon.xls") <> "" Then
        If Dir("C:\Temp\ReconTemp.xls") <> "" Then
            Kill "C:\Temp\ReconTemp.xls"
        End If
        'Create a new blank formatted workbook
        FileCopy "C:\Temp\Recon.xls", "C:\Temp\ReconTemp.xls"
        Kill "C:\Temp\Recon.xls"
   End If
   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   
   Set obook = oExcel.Workbooks.Open("C:\Temp\ReconTemp.xls")
   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = obook.Worksheets(1)
   'There are various methods of populating the workbook
   'Enter the method you prefer here
   'The most common one would be to use the CopyFromRecordset command
[COLOR="Red"]   oSheet.Range("A2").Value = CopyFromRecordset Rs  [/COLOR] 
   'Save the Workbook and Quit Excel
   'You could give the workbook a generic name or hard code it yourself
   obook.SaveAs "C:\Temp\Recon.xls"
   oExcel.Quit
   'Destroy object references from cache
   Set oExcel = Nothing
   Set obook = Nothing
   Set oSheet = Nothing
   
   

End Function

This is a similar example using the copy from recordset method. Access may fall over again due to size/memory limitations.
but give it a try and see if it works. Get back tome if it does not.

 

[Matter]

[...] but give it a try and see if it works. Get back to me if it does not.

Hi DCrake,

Sorry for not getting back sooner- I had a pile of other things to do these past few days. Anyway, I tried your code, but I'm getting compile errors on this line:

oSheet.Range("A2").Value = CopyFromRecordset(Rs)

the error is:

Compile error: Sub or Function not defined

which highlights CopyFromRecordset.

I've also tried DoCmd.OutputTo, but that runs into the same 65k records problem. You mention 2 other methods to do this; I found this:
http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1832693&page=&view=&sb=&o=&fpart=1&vc=1
But having a loop run record-by-record for 85k records doesn't sound efficient. So, any thoughts? Thanks!

m